Migrate to RunxBuild and earn up to $50 in hosting credit on your first deposit.

Calculate your savings
unxBuild
Log in Get Started
Back to Blog Databases

create user postgres: The Commands That Work on Every Cluster and the Defaults That Bite You

Sean

Platform Writer

Jun 18, 2026
7 min read

The headline answer to “create user postgres” is CREATE USER app_user WITH PASSWORD '...';. That is the SQL statement that has worked since PostgreSQL 7. The honest version covers six things that you actually need to know to create a user that is usable on a real cluster: the difference between CREATE USER and CREATE ROLE, the password policy you actually need, the default privileges that quietly fail, the statement timeout that keeps a runaway query from killing the database, the role attributes you almost always want, and the dozen managed-cluster quirks where CREATE USER works but the user cannot actually connect.

create user postgres: The Commands That Work on Every Cluster and the Defaults That Bite You

Table of contents

The short version for most teams: use CREATE USER (not CREATE ROLE), set a real password with VALID UNTIL, grant CONNECT on the database, grant USAGE on the schema, grant SELECT/INSERT/UPDATE/DELETE on the tables, set a statement_timeout, and audit the role 30 days later. Skip any of these and you are going to have a confusing week.

create user postgres: the commands that work on every cluster and the defaults that bite you

Table of contents

The direct answer

The minimum command:

CREATE USER app_user WITH PASSWORD 'replace_me_with_real_password';

The version that survives contact with reality:

CREATE USER app_user WITH
  PASSWORD 'replace_me_with_real_password'
  VALID UNTIL '2027-01-01'
  CONNECTION LIMIT 50;

GRANT CONNECT ON DATABASE myapp 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 USER app_user SET statement_timeout = '10s';
ALTER USER app_user SET idle_in_transaction_session_timeout = '60s';
ALTER USER app_user SET search_path = 'public';

That creates a user, grants the typical app permissions, and sets the runtime defaults that prevent a runaway query from killing the database.

The four things a real Postgres user needs

A “real” Postgres user — one an application can actually use — has four pieces:

  1. Login credentials. WITH PASSWORD for password auth, or use certificate auth for service-to-service.
  2. Connection permission. GRANT CONNECT ON DATABASE is the gatekeeper. Without it, the user cannot connect to the database at all.
  3. Schema access. GRANT USAGE ON SCHEMA public lets the user see what is in the schema. Without it, even a successful login hits “permission denied for schema.”
  4. Table permissions. GRANT SELECT/INSERT/UPDATE/DELETE on the specific tables the user needs. Without these, the user can connect but cannot read or write.

If you only grant the credentials, the user can psql -U app_user -d myapp and connect, but every query fails with “permission denied.” If you only grant the table permissions but forget CONNECT, every connection is refused.

CREATE USER vs CREATE ROLE: which one and why

CREATE USER is an alias for CREATE ROLE ... LOGIN. The two statements do the same thing; CREATE USER defaults to LOGIN, CREATE ROLE defaults to NOLOGIN. Use CREATE USER for human or service accounts that need to connect. Use CREATE ROLE for group roles that aggregate permissions and are inherited.

The full syntax:

CREATE USER app_user WITH LOGIN PASSWORD '...';
-- equivalent to
CREATE ROLE app_user WITH LOGIN PASSWORD '...';
-- and equivalent to
CREATE ROLE app_user WITH PASSWORD '...';  -- NOLOGIN is the default

The reason CREATE USER exists at all is historical — pre-8.1, CREATE USER and CREATE GROUP were separate commands. PostgreSQL has since unified everything under CREATE ROLE. The aliases are kept for compatibility and readability.

A pattern that works well for most teams: create group roles for permission sets, then create user roles that inherit from them:

-- group role for app users
CREATE ROLE app_users NOLOGIN;
GRANT CONNECT ON DATABASE myapp TO app_users;
GRANT USAGE ON SCHEMA public TO app_users;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_users;

-- individual user
CREATE USER app_user WITH LOGIN PASSWORD '...' IN ROLE app_users;

The individual user gets the group permissions. New permissions added to the group propagate to all members. Removing access is one REVOKE app_users FROM app_user.

The password policy that survives a security audit

Three settings that matter:

  1. PASSWORD '...'. The actual password. Use a strong one. Rotate on a schedule.
  2. VALID UNTIL '2027-01-01'. Expiry date. After this, the password stops working and the user must rotate.
  3. ENCRYPTED (optional). Tells PostgreSQL to store the password as an SCRAM-SHA-256 hash instead of MD5. The default in modern PostgreSQL (10+) is already encrypted, but be explicit on older versions.

The audit-friendly version:

CREATE USER app_user WITH
  LOGIN
  PASSWORD 'S3cure!Passw0rd_Generated_Not_Personal'
  VALID UNTIL '2027-01-01'
  CONNECTION LIMIT 50;

The CONNECTION LIMIT 50 is a small but useful safety net. It stops a misbehaving client from opening 10,000 connections and exhausting the cluster’s max_connections.

For managed clusters, most of the password policy is enforced at the platform level (AWS RDS IAM auth, Cloud SQL IAM, etc.) and the in-database password is optional. Read the platform docs before setting PASSWORD on a managed cluster.

The default privileges that quietly fail

The default privileges in PostgreSQL are not what most teams expect. Out of the box, the public schema is owned by the postgres role. New tables created by postgres are readable by everyone; new tables created by app_user are readable only by app_user (and the owner).

This means if you create a table as postgres, then try to SELECT from it as app_user, it works. If you create a table as app_user, then try to SELECT from it as a second app_user, it fails. The default GRANT does not propagate to other users in the same role.

The fix is ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_users;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_users;

Now, any future tables created by postgres automatically grant the listed permissions to app_users. New tables “just work” for the application role.

A common gotcha: ALTER DEFAULT PRIVILEGES is per-creator-role. If you create tables as app_user and expect other roles to read them, you need a second ALTER DEFAULT PRIVILEGES FOR ROLE app_user. The defaults are scoped to whoever is running the CREATE TABLE.

The statement timeout and why every app user needs one

statement_timeout is the single most important runtime setting for an application user. Without it, a runaway query from the app can hold a connection indefinitely and exhaust the connection pool.

The setting:

ALTER USER app_user SET statement_timeout = '10s';

After this, every query app_user runs will be cancelled after 10 seconds. The application sees canceling statement due to statement timeout and recovers gracefully (or fails the request, depending on the driver).

The pair that matters:

  • statement_timeout = '10s' — kill queries that run too long.
  • idle_in_transaction_session_timeout = '60s' — kill transactions that are open but not doing anything (the classic “developer opened a transaction in psql and forgot to commit” failure mode).

Both are application-level defaults, not connection-level. You can override per-connection if you have a long-running migration that needs more time.

The managed-cluster quirks

On managed PostgreSQL services (RDS, Cloud SQL, Azure Database, Neon, Supabase, Render), CREATE USER works, but the surrounding context does not.

AWS RDS: The postgres role is renamed to rds_superuser or hidden behind a separate endpoint. Use the rds_superuser role for admin tasks; do not try to use postgres.

Cloud SQL: Similar to RDS — the postgres role is reserved for Google’s maintenance. Use a separate admin role you create at provision time.

Neon / Supabase: These providers give you a default role with the database name. Creating a new role works, but you cannot grant superuser or replication privileges. The provider does that.

Render, Railway, RunxBuild: The managed PostgreSQL is provisioned with a default admin role. New users you create can have any of the standard attributes except superuser and replication.

The fix in all cases: read the provider’s docs for the role naming, and assume the platform reserves some privileges for itself. Plan your user-creation scripts to grant only what the provider allows.

The full safe template

A single transaction that creates a fully-configured application user:

BEGIN;

CREATE USER app_user WITH
  LOGIN
  PASSWORD '[REDACTED]'
  VALID UNTIL '2027-01-01'
  CONNECTION LIMIT 50;

GRANT CONNECT ON DATABASE myapp TO app_user;

\c myapp

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 FOR ROLE postgres IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

ALTER USER app_user SET statement_timeout = '10s';
ALTER USER app_user SET idle_in_transaction_session_timeout = '60s';
ALTER USER app_user SET search_path = 'public';

COMMIT;

Replace [REDACTED] with a real password. Run as a superuser. The \c myapp switches the connection to the application database so the schema grants apply to the right place.

For a SaaS app running on a managed PostgreSQL cluster where the user creation happens in a deploy script, this same template works — the platform creates the database with a default admin role, and the deploy script runs the CREATE USER and grants against that admin role. If you are deploying to RunxBuild’s backend services with managed PostgreSQL, the connection string and the admin role are in the dashboard, and the user-creation script runs as part of the deploy. For what running that database and API stack at production scale actually costs, the RunxBuild hosting calculator gives you the per-month number.

FAQ

What is the difference between CREATE USER and CREATE ROLE?

CREATE USER is an alias for CREATE ROLE ... LOGIN. Both do the same thing; CREATE USER defaults to LOGIN, CREATE ROLE defaults to NOLOGIN. Use CREATE USER for accounts that need to connect, CREATE ROLE for group roles.

How do I create a user with no password?

CREATE USER app_user; — no WITH PASSWORD clause. The user can still log in via peer authentication (on Linux) or other auth methods, just not via password.

How do I create a superuser?

CREATE USER admin WITH SUPERUSER PASSWORD '...'; — but most managed providers do not allow this. The superuser is reserved.

How do I grant all privileges on a database?

GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user; grants CONNECT, CREATE, TEMPORARY, and TEMP. It does not grant table-level permissions — you still need GRANT on the schema and tables.

How do I change a user’s password later?

ALTER USER app_user WITH PASSWORD 'new_password';. The old password stops working immediately.

How do I remove a user?

DROP USER app_user; or DROP ROLE app_user;. The role must not own any objects — transfer ownership first with REASSIGN OWNED BY app_user TO postgres; then DROP OWNED BY app_user;.

How do I list the permissions on a user?

\du app_user in psql. For the underlying attributes, query pg_roles where rolname = 'app_user'. For the granted privileges, query information_schema.role_table_grants where grantee = 'app_user'.

Can I create a user that can only read, never write?

CREATE USER readonly WITH LOGIN PASSWORD '...'; then GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; plus ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO readonly;. Skip the INSERT/UPDATE/DELETE grants entirely.

FAQ

What is the difference between CREATE USER and CREATE ROLE?

CREATE USER is an alias for CREATE ROLE ... LOGIN. Use CREATE USER for accounts that need to connect, CREATE ROLE for groups.

How do I create a user with no password?

CREATE USER app_user; — no WITH PASSWORD clause. The user can still log in via peer auth.

How do I create a superuser?

CREATE USER admin WITH SUPERUSER PASSWORD '...';. Most managed providers do not allow this.

How do I grant all privileges on a database?

GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user; grants CONNECT, CREATE, TEMPORARY. It does not grant table-level permissions.

How do I change a user’s password later?

ALTER USER app_user WITH PASSWORD 'new_password';. The old password stops working immediately.

How do I remove a user?

DROP USER app_user;. The role must not own any objects — transfer ownership first with REASSIGN OWNED BY.

How do I list the permissions on a user?

\du app_user in psql. For attributes: pg_roles where rolname = 'app_user'. For grants: information_schema.role_table_grants.

Can I create a user that can only read, never write?

Yes. CREATE USER readonly; plus GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; plus ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO readonly;.

#PostgreSQL#User Management#psql#Database Admin#Postgres Roles