Create User in Postgres: The Right Way, the Lazy Way, and the Minimum Privilege Pattern That Survives Production

Q: What is the difference between CREATE USER and CREATE ROLE in Postgres?

CREATE USER is shorthand for CREATE ROLE WITH LOGIN. A 'user' is a role that can connect to the database. A 'role' without LOGIN is a group. For most purposes, the two are interchangeable.

Q: Should I use GRANT ALL for the application user?

No. GRANT ALL gives the application every privilege, including DROP DATABASE. The minimum privilege pattern (GRANT SELECT, INSERT, UPDATE, DELETE on the specific tables) limits the blast radius of a compromised credential.

Q: How do I handle migrations with a separate role?

Create a separate role for the migration: CREATE USER app_migrator. Grant the migration role the privileges the migration needs. The application uses app_user. The two are separate. The application's credential cannot create or drop tables.

Q: What is the default hashing algorithm for Postgres passwords?

scram-sha-256 since Postgres 14. The application sends the password over a TLS connection; Postgres hashes it and compares. The hash is what is stored.

To create a user in Postgres, run CREATE USER app_user WITH PASSWORD '...'; from a superuser session. To create the right user for an application, also grant the minimum privileges the application needs (CONNECT on the database, USAGE on the schema, SELECT/INSERT/UPDATE/DELETE on the specific tables) and set a sensible valid until date for the password. The one-line command is the easy part. The minimum privilege grant is the part that survives the application getting compromised.

This post is the right way, the lazy way, and the production checklist. The first half is the SQL. The second half is the role vs user confusion, the grant hierarchy, the password rotation, and the deploy-side secret management.

The interesting thing about “create user in postgres” is that the question almost always means “create a database user for an application.” The answer is not just the CREATE USER line — it is the full set of grants, the password storage, the role attributes, and the rotation policy. The team that runs the one-line command and grants ALL PRIVILEGES is the team that gets burned the first time the application is compromised.

The direct answer
The right way: minimum privilege
The lazy way: GRANT ALL (and why it bites)
The role vs user confusion
The password: storage, rotation, and the right algorithm
The role attributes that matter
The deploy-side secret management
The opinion this post is built on
FAQ

The direct answer

For an application user:

-- Create the user with a strong password
CREATE USER app_user WITH PASSWORD 'a-strong-random-password';

-- Grant connect to the database
GRANT CONNECT ON DATABASE app_db TO app_user;

-- Grant schema usage (Postgres 14+: also grant on the schema)
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant table-level privileges (only what the app needs)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- For new tables created in the future, set default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_user;

That is the minimum privilege pattern. The user can connect, can use the schema, can read and write the tables, can use the sequences for auto-incrementing IDs, and has no other privileges.

The right way: minimum privilege

Minimum privilege is the principle that a user should have only the privileges the user needs to do the job. For an application user, the privileges are:

CONNECT on the database. The user can connect to the database.
USAGE on the schema. The user can use the schema (reference tables, sequences, etc.).
SELECT, INSERT, UPDATE, DELETE on the specific tables. The user can read and write the application’s data.
USAGE, SELECT on the sequences. The user can use auto-incrementing IDs.

The user does NOT have:

CREATEDB. The user cannot create new databases.
CREATEROLE. The user cannot create new users.
SUPERUSER. The user is not a superuser.
GRANT OPTION. The user cannot grant privileges to other users.
Access to other schemas. The user cannot read or write tables in other schemas.
Access to other databases. The user cannot connect to other databases.

The minimum privilege pattern limits the blast radius of a compromised credential. A leaked application password can read and write the application’s data. It cannot create users, cannot drop databases, cannot read other applications’ data, cannot exfiltrate the entire cluster.

The pattern is the same as every other system. The user has the least privilege needed to do the job. The pattern is what every audit and every compliance check looks for. The pattern is the boring answer that saves the team the day the application is compromised.

The lazy way: GRANT ALL (and why it bites)

The lazy way:

GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

The lazy way works. The application can connect, can read, can write, can drop, can create, can do anything. The lazy way is what every tutorial shows. The lazy way is what every Stack Overflow answer recommends.

The lazy way bites when the application is compromised:

SQL injection. The attacker can read other tables, other schemas, other databases. The attacker can exfiltrate the entire cluster.
Credential leak. The leaked password has more privileges than the application needs. The attacker can drop the database, drop the schema, drop the tables.
Misconfigured backup. The backup user has the application’s privileges. The backup runs as the application. The application can read the entire cluster.

The minimum privilege pattern is the fix. The application has the privileges it needs. The application does not have the privileges it does not need. The compromise is bounded.

The cost of minimum privilege is the discipline. The team that sets up the application with the right grants from the start does the work once. The team that sets up the application with GRANT ALL and intends to come back later never comes back. The pattern is the same as every other security practice: do the right thing upfront, save the team the incident.

The role vs user confusion

Postgres has no real distinction between a “user” and a “role.” A CREATE USER is a CREATE ROLE WITH LOGIN. A CREATE ROLE without LOGIN is a group. The two are interchangeable for most purposes.

The legacy distinction:

CREATE USER creates a role with LOGIN (can connect to the database).
CREATE ROLE creates a role without LOGIN (cannot connect, can be granted to other roles).

The modern best practice: use CREATE ROLE for everything, and explicitly add LOGIN for users that need to connect:

-- The "user" the application uses to connect
CREATE ROLE app_user WITH LOGIN PASSWORD '...';

-- The "role" that holds the privileges (can be granted to multiple users)
CREATE ROLE app_readwrite;
GRANT CONNECT ON DATABASE app_db TO app_readwrite;
GRANT app_readwrite TO app_user;

The two-level pattern is the right answer for a database that serves multiple applications. The role holds the privileges. The user inherits the role. The team can grant the role to a new user without re-granting the privileges.

For a single-application database, the one-level pattern (CREATE USER app_user ...) is fine. The team that grows past one application refactors to the two-level pattern. The migration is a few GRANT statements.

The password: storage, rotation, and the right algorithm

The password is the credential the application uses to connect. The password should be:

Strong. At least 32 random characters. Generated by a password manager, not chosen by a human.
Stored in the secret store. Not in a config file, not in a build argument, not in an environment variable committed to a file. The secret store is the platform’s secret manager (AWS Secrets Manager, GCP Secret Manager, the platform’s secret store, Vault).
Injected at runtime. The application reads the password from the environment, not from a file. The platform sets the environment variable when it starts the container.
Rotated on a schedule. The platform rotates the password every 90 days. The application reads the new password at the next deploy.

The right algorithm: the password is the password. Postgres hashes the password with md5 or scram-sha-256 (the default since Postgres 14). The hash is what Postgres stores. The application’s password is what the application sends on connect. The two are different things.

The trap: the password is in a config file. The team commits the config file. The team’s git history has the password. The team’s CI logs have the password. The team’s deploy logs have the password. The team’s first incident is a leak from a config file the team forgot about.

The fix: the password is in the secret store. The secret store is the source of truth. The application reads the password from the environment. The environment is set by the platform at container start. The team that follows the pattern is the team that does not have a password leak in the git history.

For a deeper look at the secret-store pattern, the Flask + MySQL connection guide covers the same pattern in a different language. The contract is the same: the secret store is the source, the environment is the bridge, the application reads the bridge.

The role attributes that matter

The role attributes that matter for an application user:

LOGIN (or the CREATE USER shorthand). The role can connect to the database.
NOSUPERUSER. The role is not a superuser.
NOCREATEDB. The role cannot create new databases.
NOCREATEROLE. The role cannot create new roles.
INHERIT. The role inherits privileges from roles it is a member of (the default).
NOINHERIT. The role does not inherit. Use only if the team has a specific reason.

The default attributes for a CREATE USER are reasonable: LOGIN, NOSUPERUSER, NOCREATEDB, NOCREATEROLE, INHERIT. The team that uses CREATE USER gets the right defaults. The team that uses CREATE ROLE and adds LOGIN gets the same defaults. The team that uses ALTER ROLE ... SUPERUSER is the team that should not be deploying to production.

The trap: a CREATEDB role. The team creates a role with CREATEDB for the application because the application needs to create a temporary table or run a migration that creates a table. The team grants CREATEDB to the role. The role can now create any database in the cluster. The minimum privilege pattern is broken.

The fix: use a separate role for the migration. The application uses app_user (no CREATEDB). The migration uses app_migrator (CREATEDB, used only at deploy time). The two are separate. The application’s credential cannot create databases. The migration’s credential can, but the migration is short-lived.

The deploy-side secret management

The deploy-side secret management is the same as the local-side secret management: the secret store is the source of truth, the environment variable is the bridge, the application reads the bridge.

-- 1. Create the user with a strong password
CREATE USER app_user WITH PASSWORD '***';

-- 2. Grant minimum privileges
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_user;

The platform stores the password in the secret store. The platform sets DATABASE_URL=postgresql://app_user:***@db.internal:5432/app_db in the application’s environment. The application reads DATABASE_URL at startup. The credential is in the secret store, not in the code.

For a deeper look at the platform side, the RunxBuild platform handles the secret store, the environment injection, the connection pooling, and the SSL. The team focuses on the application.

The opinion this post is built on

Creating a user in Postgres is one line of SQL. Creating the right user for an application is a discipline. The discipline is minimum privilege, password rotation, role separation, and secret management. The discipline is the difference between “the application is breached and the attacker reads the application’s data” and “the application is breached and the attacker reads the entire cluster.”

The boring answer is the right one. CREATE USER with a strong password. GRANT the minimum privileges. Set the password in the secret store. Rotate the password on a schedule. Use a separate role for migrations. The boring pattern is the pattern that survives the incident.

The platform is the multiplier. A platform with a secret store, an environment injection, a connection pooler, and a managed Postgres is a platform where the minimum privilege pattern is a config file, not a discipline. A platform without those features forces the team to operate the database by hand, and the team will get the privileges wrong in a different way for every service.

The minimum privilege pattern is the right answer. The platform is the right home. The team that combines the two is the team that has a database the audit will not flag, that the incident will not breach, and that the team will not resent.

How this fits the rest of the stack

The platform that runs the Postgres is also the platform that prices the user. A managed database charges by instance size, by storage, by bandwidth, and sometimes by the number of connections — each of those is a separate line item the team should know before adding another role. The RunxBuild hosting calculator is the quick way to model that — pick the database tier, the storage, the connection count, and the expected query volume, and the calculator shows what the database costs at the team’s actual usage.

Useful related references:

FAQ

What is the difference between `CREATE USER` and `CREATE ROLE` in Postgres?

CREATE USER is shorthand for CREATE ROLE WITH LOGIN. A “user” is a role that can connect to the database. A “role” without LOGIN is a group that can be granted to other roles. For most purposes, the two are interchangeable. The two-level pattern (a role for privileges, a user for connection) is the right answer for a multi-application database.

Should I use `GRANT ALL` for the application user?

No. GRANT ALL gives the application every privilege, including DROP DATABASE. The minimum privilege pattern (GRANT SELECT, INSERT, UPDATE, DELETE on the specific tables, USAGE on the schema, CONNECT on the database) limits the blast radius of a compromised credential. The discipline is the work upfront; the work saves the team the incident.

How do I rotate the application user’s password?

The platform’s secret manager rotates the password on a schedule. The team updates the secret, the platform sets the new value in the application’s environment, the next deploy reads the new password, the application’s connections use the new password. The team that does not rotate on a schedule is the team that has the same password in production for years. The team that does rotate is the team that limits the lifetime of a leaked credential.

How do I handle migrations with a separate role?

Create a separate role for the migration: CREATE USER app_migrator WITH PASSWORD '...'. Grant the migration role the privileges the migration needs (CREATEDB, schema creation, table creation, etc.). The application uses app_user. The migration uses app_migrator. The two are separate. The application’s credential cannot create or drop tables. The migration’s credential can, but the migration is short-lived and runs only at deploy time.

Should the application’s password be in the config file?

No. The application’s password is in the secret store. The application reads the password from the environment at startup. The environment is set by the platform when it starts the container. The team that puts the password in the config file is the team that has a password leak in the git history. The team that uses the secret store is the team that does not.

What is the default hashing algorithm for Postgres passwords?

scram-sha-256 since Postgres 14. The application sends the password in plain text over a TLS connection; Postgres hashes it and compares. The hash is what is stored. The application does not hash the password; the application sends the password. The team that uses TLS is the team that does not send the password in plain text over the network.