PostgreSQL CREATE USER

Basic syntax

CREATE USER name WITH PASSWORD 'password';

CREATE USER creates a new role that can log in. The WITH keyword is optional, and so is the password (though you should always set one for application accounts).

CREATE USER vs CREATE ROLE

These two commands are nearly identical. The only difference is the default for the LOGIN attribute.

These two statements produce the same result:

CREATE USER alice WITH PASSWORD 'hunter2';
CREATE ROLE alice WITH LOGIN PASSWORD 'hunter2';

In practice, use CREATE USER when you want a login-capable account. Use CREATE ROLE when creating a group role that will be granted to other users.

Creating a user with a password

CREATE USER appuser WITH PASSWORD 'str0ng-p@ssword';

PostgreSQL stores a hashed version of the password. To set a password expiry:

CREATE USER appuser WITH PASSWORD 'str0ng-p@ssword' VALID UNTIL '2027-01-01';

After the expiry date, the user can no longer log in until you update the password.

Granting database access

Creating a user is not enough on its own. You also need to grant access at three levels: the database, the schema, and the tables.

Here is the full sequence for setting up a minimal application user:

-- 1. Create the user
CREATE USER appuser WITH PASSWORD 'str0ng-p@ssword';

-- 2. Allow the user to connect to the database
GRANT CONNECT ON DATABASE myapp TO appuser;

-- 3. Allow the user to see objects in the schema
GRANT USAGE ON SCHEMA public TO appuser;

-- 4. Grant table-level permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

-- 5. Grant sequence permissions (needed for auto-increment columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

Skip any of these and the user will hit a permission error at that level.

Common privilege patterns

Read-only user

CREATE USER reporter WITH PASSWORD 'read0nly-pass';
GRANT CONNECT ON DATABASE myapp TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

Read-write user

CREATE USER appuser WITH PASSWORD 'str0ng-p@ssword';
GRANT CONNECT ON DATABASE myapp TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

Superuser

CREATE USER admin WITH SUPERUSER PASSWORD 'admin-pass';

Avoid superuser accounts for application connections. A superuser bypasses all permission checks, which means a compromised connection can read or destroy anything on the server. Reserve superuser access for database administrators.

Granting privileges on future tables

The GRANT ... ON ALL TABLES command only covers tables that exist at the time you run it. New tables created afterward will not inherit those permissions.

To fix this, use ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO appuser;

This tells PostgreSQL to apply those grants automatically whenever a new table or sequence is created in the public schema. Run it as the role that will be creating tables (usually the database owner or a migration user).

Viewing users

In psql, use \du to list all roles:

\du

Output:

                             List of roles
 Role name |                   Attributes
-----------+-----------------------------------------------
 alice     |
 appuser   |
 postgres  | Superuser, Create role, Create DB, Replication
 reporter  |

Use \du+ for additional detail including member roles and comments.

To query users from SQL, use pg_roles:

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolconnlimit
FROM pg_roles
ORDER BY rolname;

To see only login-capable users:

SELECT usename, usecreatedb, usesuper
FROM pg_user
ORDER BY usename;

Changing a password

ALTER USER appuser WITH PASSWORD 'new-str0ng-p@ssword';

To remove the password expiry on an existing user:

ALTER USER appuser WITH VALID UNTIL 'infinity';

Revoking privileges

To remove specific privileges:

REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM reporter;

To remove all privileges at once:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM appuser;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM appuser;
REVOKE USAGE ON SCHEMA public FROM appuser;
REVOKE CONNECT ON DATABASE myapp FROM appuser;

Dropping a user

DROP USER appuser;

PostgreSQL will refuse to drop a user who still owns objects or has privileges. To handle this, use DROP OWNED BY first:

-- Transfer or drop all objects owned by the user
DROP OWNED BY appuser;

-- Then drop the user
DROP USER appuser;

DROP OWNED BY also revokes all privileges granted to the user, so you do not need to run REVOKE separately.

If the user owns objects in multiple databases, you need to run DROP OWNED BY while connected to each database where they have objects.

The createuser shell command

PostgreSQL ships with createuser, a shell wrapper around CREATE USER. It lets you create users without opening psql:

createuser --pwprompt appuser

The --pwprompt flag prompts for a password interactively. Common options:

# Create a superuser
createuser --superuser admin

# Create a user that can create databases
createuser --createdb deploy

# Connect to a specific host and port
createuser -h localhost -p 5432 -U postgres appuser

createuser accepts the same connection flags as psql.

Quick reference