Essential Commands for Managing Users, Roles, and Permissions in PostgreSQL

PostgreSQL users, roles, and permissions decide who can connect, what they can read, and what they can change. If those permissions grow by guesswork, your database becomes hard to audit and easy to overexpose.

PostgreSQL uses one concept, the role, for both login users and groups. A role with LOGIN can connect. A role without LOGIN is often used as a group that carries shared privileges.

Create and Change Roles

Create an application login role with a password:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Create a group role for shared read access:

CREATE ROLE reporting_readonly NOLOGIN;

Common role attributes include:

Attribute	What it allows
LOGIN	Connect to the database server.
CREATEDB	Create databases.
CREATEROLE	Create, alter, and drop many roles, with important limits around superuser roles.
REPLICATION	Use replication connections.
SUPERUSER	Bypass normal permission checks. Use only for tightly controlled admin roles.
INHERIT	Use privileges from roles it is a member of. This is the default.

Change a password or disable login with ALTER ROLE:

ALTER ROLE app_user WITH PASSWORD 'new_strong_password';
ALTER ROLE old_employee NOLOGIN;

Before dropping a role, handle anything it owns and any privileges it has been granted:

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNED transfers owned objects. DROP OWNED removes privileges granted to the role and can drop objects still owned by it in the current database, so review carefully before running it.

Use Group Roles for Cleaner Access

Grant permissions to group roles, then add people or application roles to those groups.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION lets the member grant and revoke that role membership for others. It does not by itself make the member a superuser or give unrestricted power over the database.

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

This pattern keeps access easier to audit. Instead of checking 50 users for table grants, you can check the group role.

Grant Object Permissions

Use GRANT to allow specific actions on specific objects.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

Common table privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.

Schema permissions are separate from table permissions. A role often needs USAGE on the schema and privileges on the tables inside it:

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

For inserts that use sequences, grant sequence access too:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

For functions, grant execution explicitly:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

Revoke Permissions

REVOKE removes a direct grant.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

If a user still has access after a revoke, check group membership. The user may inherit the same privilege through another role.

Set Permissions for Future Tables

GRANT ON ALL TABLES affects existing tables only. For tables created later, use ALTER DEFAULT PRIVILEGES from the role that will create those tables.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

This is one of the most common missing steps in PostgreSQL permission setups. A user can read today's tables but gets denied on tomorrow's table because default privileges were never configured.

Audit Current Access

In psql, these commands are fast checks:

\du
\du+ app_user
\dp app.orders
\dn+

Catalog views are better for repeatable reports:

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

For table grants:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Security Habits That Pay Off

Give applications dedicated roles instead of using a human DBA account. Grant the smallest set of privileges the application needs. Avoid SUPERUSER and broad ALL PRIVILEGES grants for day-to-day work.

Use group roles for shared access, and review memberships regularly. When someone changes teams, removing one group membership is safer than hunting down table-level grants.

The practical next step is to pick one production schema and list its roles, memberships, table grants, and default privileges. Fix the gaps there before expanding the same pattern to the rest of your databases.