(opens in a new tab)

Postgres CREATE USER: Roles, Passwords and Grants

September 11, 2026 by Chat2DB Team

PostgreSQL does not really have "users" as a separate concept. It has roles. A role that is allowed to log in is what most people call a user, and a role that cannot log in is what most people call a group. Once you understand that, CREATE USER, CREATE ROLE, ALTER USER, and GRANT stop looking like four unrelated commands and start looking like one small system. This guide walks through that system from creating a login to cleaning up a role that owns tables you no longer need.

Every statement below is plain SQL. Run it in psql or connect with Chat2DB (download at https://chat2db.ai/download (opens in a new tab) or use the web version at https://app.chat2db.ai (opens in a new tab)) as a superuser such as postgres, then reconnect as the new role to verify the grants.

CREATE USER is CREATE ROLE with LOGIN

The two commands share the same syntax and the same catalog. The only difference is the default for the LOGIN attribute.

-- These two statements are equivalent.
CREATE USER alice WITH PASSWORD 'change-me';
CREATE ROLE alice WITH LOGIN PASSWORD 'change-me';
 
-- This creates a role that cannot connect. It is a group.
CREATE ROLE readonly;

If you forget LOGIN on CREATE ROLE, the connection attempt fails with:

FATAL:  role "alice" is not permitted to log in

You can fix it without recreating the role:

ALTER ROLE alice LOGIN;

Role attributes

Attributes are flags stored on the role itself. They control what the role can do at the cluster level, not what it can do inside a specific database. The most common ones:

A realistic example that sets several at once:

CREATE ROLE etl_loader WITH
  LOGIN
  PASSWORD 'change-me'
  CONNECTION LIMIT 5
  VALID UNTIL '2027-01-01 00:00:00+00';

VALID UNTIL only affects password authentication. It does not lock the role, and a superuser can still SET ROLE to it. To extend or remove the expiry:

ALTER ROLE etl_loader VALID UNTIL '2027-07-01';
ALTER ROLE etl_loader VALID UNTIL 'infinity';

Passwords: setting, changing, and storing them safely

The PASSWORD clause and password_encryption

When you write PASSWORD 'change-me', the server hashes the plaintext before it is stored in pg_authid. Which hash is used depends on the password_encryption parameter at the moment the password is set. Since PostgreSQL 14 the default is scram-sha-256. Older clusters may still be on md5, which is weak and is deprecated in current releases.

Check what your server is using:

SHOW password_encryption;

 password_encryption
---------------------
 scram-sha-256

If it says md5, change it in postgresql.conf or at runtime, then re-set every password so the stored hash is regenerated:

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
 
-- Existing md5 hashes stay md5 until the password is set again.
ALTER USER alice WITH PASSWORD 'change-me-again';

You also need pg_hba.conf to allow the matching method. A line such as this accepts SCRAM logins over TCP:

host    all    all    0.0.0.0/0    scram-sha-256

If pg_hba.conf says md5 but the stored hash is SCRAM, authentication still works because the server negotiates SCRAM for that role. The reverse does not hold: a scram-sha-256 line in pg_hba.conf rejects roles that still have an md5 hash until their password is reset.

You can see which format each role currently has:

SELECT rolname,
       CASE
         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
         WHEN rolpassword LIKE 'md5%' THEN 'md5'
         WHEN rolpassword IS NULL THEN 'no password'
         ELSE 'other'
       END AS hash_type
FROM pg_authid
WHERE rolcanlogin
ORDER BY rolname;

Changing a password with ALTER USER

ALTER USER alice WITH PASSWORD 'new-secret';
 
-- ALTER ROLE is identical.
ALTER ROLE alice PASSWORD 'new-secret';

The change takes effect immediately for new connections. Existing sessions are not disconnected.

Why psql \password is safer

A plain ALTER USER ... PASSWORD 'literal' sends the plaintext to the server as part of the SQL text. That text can end up in log_statement output, in pg_stat_statements, in your shell history, and in the psql history file. The psql meta-command \password avoids all of that. It prompts twice, hashes the password on the client with the server's password_encryption setting, and sends only the hash:

postgres=# \password alice
Enter new password for user "alice":
Enter it again:

The server log then shows a SCRAM-SHA-256$... string instead of the real password. If your tooling does not support \password, the next best option is to temporarily disable statement logging for the session:

SET LOCAL log_statement = 'none';
ALTER USER alice WITH PASSWORD 'new-secret';

Note that SET LOCAL only works inside a transaction block.

Group roles and membership

A group role is just a role with NOLOGIN. Privileges are granted to the group, and login roles are made members of it.

CREATE ROLE readonly NOLOGIN;
CREATE ROLE readwrite NOLOGIN;
 
CREATE USER alice WITH PASSWORD 'change-me';
CREATE USER bob   WITH PASSWORD 'change-me';
 
GRANT readonly  TO alice;
GRANT readwrite TO bob;

Because roles default to INHERIT, alice can use any privilege granted to readonly without doing anything extra. If a role is created with NOINHERIT, it must switch explicitly:

-- As alice, when alice is NOINHERIT:
SET ROLE readonly;
SELECT current_user, session_user;

 current_user | session_user
--------------+--------------
 readonly     | alice

RESET ROLE returns to the session user. SET ROLE is also useful for testing: as a superuser you can SET ROLE alice and confirm that a SELECT is rejected before handing the credentials to a real person.

Granting access step by step

This is where most "permission denied" questions come from. A table grant alone is not enough. The role needs a path from the connection all the way to the table, and each step is a separate privilege.

Set up a sample database and schema to work against:

CREATE DATABASE shop;
\c shop
 
CREATE SCHEMA sales;
 
CREATE TABLE sales.orders (
  id         serial PRIMARY KEY,
  customer   text NOT NULL,
  amount     numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO sales.orders (customer, amount) VALUES
  ('acme', 120.00),
  ('globex', 75.50),
  ('initech', 310.25);

Step 1: CONNECT on the database

GRANT CONNECT ON DATABASE shop TO readonly;

By default the PUBLIC pseudo-role already has CONNECT on every new database, so this grant is often redundant. It becomes required when you lock things down with REVOKE CONNECT ON DATABASE shop FROM PUBLIC, which is a reasonable thing to do on a production server.

Step 2: USAGE on the schema

GRANT USAGE ON SCHEMA sales TO readonly;

Without USAGE, any reference to sales.orders fails with permission denied for schema sales, even if the table itself has been granted. In PostgreSQL 15 and later the public schema no longer gives CREATE to everyone, so an explicit USAGE grant matters for that schema too.

Step 3: privileges on existing tables

GRANT SELECT ON ALL TABLES IN SCHEMA sales TO readonly;

This grants on every table and view that exists right now. Sequences are separate objects:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales TO readonly;

Step 4: default privileges for future tables

GRANT ... ON ALL TABLES does nothing for tables created tomorrow. ALTER DEFAULT PRIVILEGES fixes that, but note that it applies to objects created by a specific role, which by default is the role that runs the statement:

-- Run as the role that will own future tables (here: postgres).
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO readonly;
 
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT USAGE, SELECT ON SEQUENCES TO readonly;

If migrations run as a different role, say app_migrator, use FOR ROLE:

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA sales
  GRANT SELECT ON TABLES TO readonly;

Verify from the other side:

SET ROLE alice;
SELECT customer, amount FROM sales.orders ORDER BY id;
INSERT INTO sales.orders (customer, amount) VALUES ('umbrella', 1.00);
RESET ROLE;

 customer | amount
----------+--------
 acme     | 120.00
 globex   |  75.50
 initech  | 310.25
(3 rows)

ERROR:  permission denied for table orders

A read-write application role

The same pattern with a wider set of privileges. INSERT on a table with a serial column also needs USAGE on its sequence.

GRANT CONNECT ON DATABASE shop TO readwrite;
GRANT USAGE ON SCHEMA sales TO readwrite;
 
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO readwrite;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA sales TO readwrite;
 
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO readwrite;

Now bob, a member of readwrite, can insert:

SET ROLE bob;
INSERT INTO sales.orders (customer, amount) VALUES ('umbrella', 1.00) RETURNING id;
RESET ROLE;

 id
----
  4

Keep the application connecting as bob (or a dedicated app login) rather than as the table owner. An owner can DROP TABLE; a member of readwrite cannot.

Listing roles and their privileges

In psql, \du prints every role with its attributes and memberships:

                          List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-------------
 alice     |                                                            | {readonly}
 bob       |                                                            | {readwrite}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 readonly  | Cannot login                                               | {}
 readwrite | Cannot login                                               | {}

The same information from SQL, which works from any client:

SELECT r.rolname,
       r.rolsuper,
       r.rolcanlogin,
       r.rolcreatedb,
       r.rolcreaterole,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
             FROM pg_auth_members m
             JOIN pg_roles b ON m.roleid = b.oid
             WHERE m.member = r.oid) AS member_of
FROM pg_roles r
WHERE r.rolname NOT LIKE 'pg\_%'
ORDER BY r.rolname;

To see table-level grants, query information_schema.role_table_grants:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'sales'
ORDER BY grantee, table_name, privilege_type;

Renaming and dropping roles

Renaming is a single statement, but it clears any md5 password because the md5 hash includes the role name. SCRAM hashes are not affected.

ALTER ROLE alice RENAME TO alice_smith;

Dropping is where people hit trouble. If the role owns anything or holds any grant, DROP ROLE refuses:

CREATE USER carol WITH PASSWORD 'change-me';
GRANT CREATE ON SCHEMA sales TO carol;
SET ROLE carol;
CREATE TABLE sales.carol_notes (id int);
RESET ROLE;
 
DROP ROLE carol;

ERROR:  role "carol" cannot be dropped because some objects depend on it
DETAIL:  owner of table sales.carol_notes
privileges for schema sales

You have two choices. Hand the objects to another role, or delete them. Both statements must be run inside every database where the role owns something, because ownership is per database.

-- Option A: keep the objects, give them to postgres.
REASSIGN OWNED BY carol TO postgres;
-- REASSIGN does not remove privileges granted to carol; DROP OWNED does.
DROP OWNED BY carol;
DROP ROLE carol;

-- Option B: throw everything away.
DROP OWNED BY carol;   -- drops owned objects and revokes all grants
DROP ROLE carol;

DROP OWNED BY after REASSIGN OWNED BY looks odd but is the standard sequence: after reassigning, nothing is owned by carol any more, so DROP OWNED only revokes her remaining privileges, which is exactly what still blocks the DROP ROLE.

Membership grants do not block dropping. If carol was a member of readonly, that entry is removed automatically.

The createuser CLI

The createuser program is a thin wrapper that connects and runs CREATE ROLE for you. It is handy in shell scripts:

createuser --login --pwprompt --connection-limit=5 alice
createuser --no-login readonly
createuser --superuser --createdb dba_admin

--pwprompt asks for the password interactively and sends it hashed, like \password. Add --echo to print the SQL it generates, which is a good way to learn the exact mapping between flags and role attributes.

What changed in PostgreSQL 16

Before version 16, a role with CREATEROLE was close to a superuser in practice. It could grant membership in any non-superuser role to itself, including roles with wide privileges, and GRANT role TO member made the new member automatically inherit and able to SET ROLE.

PostgreSQL 16 tightened this:

1. A CREATEROLE role can only manage roles it has ADMIN OPTION on. When it creates a role, it receives that admin grant automatically, but it does not get admin on pre-existing roles.
2. CREATEROLE no longer implies membership in the roles it creates. The createrole_self_grant setting can restore the old behavior for a given session if you need it.
3. GRANT role TO member accepts three independent options: ADMIN, INHERIT, and SET.

-- Member can use readonly's privileges automatically, cannot SET ROLE to it,
-- and cannot grant readonly to others.
GRANT readonly TO alice WITH INHERIT TRUE, SET FALSE;
 
-- Member must SET ROLE readwrite to use it, and may grant it to others.
GRANT readwrite TO bob WITH INHERIT FALSE, SET TRUE, ADMIN TRUE;

INHERIT on the membership overrides the member's own INHERIT/NOINHERIT attribute for that one grant. SET FALSE is useful for a role whose privileges you want applied but that should never be impersonated. ADMIN TRUE replaces the older WITH ADMIN OPTION spelling, which still works.

You can inspect the flags in pg_auth_members:

SELECT r.rolname AS role, m.rolname AS member,
       am.admin_option, am.inherit_option, am.set_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE r.rolname IN ('readonly', 'readwrite');

A note on Docker

The official postgres image reads POSTGRES_USER and POSTGRES_PASSWORD only on the first start, when the data directory is empty, and uses them to create the initial superuser. If you omit POSTGRES_USER the superuser is named postgres. Changing those environment variables later has no effect on an existing volume; you must run ALTER USER ... PASSWORD inside the container or reinitialize the volume. Application roles such as readonly and readwrite should be created by an init script mounted in /docker-entrypoint-initdb.d/ or by your migration tool, not by the image itself.

Summary

• CREATE USER is CREATE ROLE ... LOGIN. Everything else about the two commands is the same.
• Role attributes (SUPERUSER, CREATEDB, CREATEROLE, CONNECTION LIMIT, VALID UNTIL) live on the role; object privileges are granted separately per database.
• Use scram-sha-256 for password_encryption, and set passwords with psql \password or --pwprompt so plaintext never reaches the server log.
• Access needs a full chain: CONNECT on the database, USAGE on the schema, privileges on tables and sequences, and ALTER DEFAULT PRIVILEGES for objects created later.
• Put privileges on NOLOGIN group roles and grant membership to login roles.
• Before DROP ROLE, run REASSIGN OWNED BY and/or DROP OWNED BY in every database the role touches.
• PostgreSQL 16 made CREATEROLE safer and added ADMIN, INHERIT, and SET options on membership grants.

FAQ

What is the difference between CREATE USER and CREATE ROLE in PostgreSQL?

There is exactly one difference: CREATE USER sets LOGIN by default and CREATE ROLE sets NOLOGIN by default. Both create an entry in pg_authid, accept the same attribute list, and can be modified with either ALTER USER or ALTER ROLE. Use CREATE USER for people and applications that connect, and CREATE ROLE for groups that only hold privileges.

Why does my user get "permission denied for schema" after I granted SELECT on the table?

Table privileges are not enough on their own. The role also needs USAGE on the schema that contains the table, and CONNECT on the database if PUBLIC connect was revoked. Run GRANT USAGE ON SCHEMA schema_name TO role_name and try again. If the table was created after your GRANT ... ON ALL TABLES, you also need ALTER DEFAULT PRIVILEGES so future tables are covered.

How do I change a PostgreSQL user password without it appearing in logs?

Use the psql meta-command \password username. It prompts for the password, hashes it on the client using the server's password_encryption method, and sends only the hash, so the server log and pg_stat_statements never see the plaintext. If you must use ALTER USER ... WITH PASSWORD, wrap it in a transaction with SET LOCAL log_statement = 'none' first.