Users, Roles and Permissions in PostgreSQL

PostgreSQL doesn't distinguish "users" from "roles" internally — a user is just a role with login permission. Roles can also represent groups of permissions that get assigned to other roles.

Creating a Role / User

CREATE ROLE app_user WITH LOGIN PASSWORD 'secret123';
-- can log in, since LOGIN is specified

CREATE ROLE readonly_group;
-- a group role — no LOGIN, so it can't connect on its own

CREATE USER is just shorthand for CREATE ROLE ... WITH LOGIN.

Granting Privileges

GRANT SELECT ON employees TO app_user;
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;
GRANT ALL PRIVILEGES ON employees TO app_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Adding a User to a Group Role

GRANT readonly_group TO app_user;
-- app_user now inherits every privilege readonly_group has

Revoking Privileges

REVOKE INSERT, UPDATE ON employees FROM app_user;
REVOKE ALL PRIVILEGES ON employees FROM app_user;

Column-Level Privileges

GRANT SELECT (name, department) ON employees TO app_user;
-- app_user can read name and department, but NOT salary

Listing Roles

\du

The Principle of Least Privilege

Grant only what a role genuinely needs:

• An application's database user should rarely need DROP TABLE or CREATE ROLE privileges.
• Read-only reporting tools should only ever get SELECT.
• Reserve the postgres superuser role for administration, never for routine application connections.

Dropping a Role

DROP ROLE app_user;
-- fails if the role still owns objects or has active grants — revoke/reassign those first

Common Errors & Troubleshooting

• ERROR: permission denied for table employees — the connecting role wasn't granted the needed privilege on that table; check with \dp employees in psql to see current grants.
• Granting a privilege but the user still can't connect — table-level grants don't help if the role lacks CONNECT privilege on the database itself, or LOGIN if it's meant to be a real user — check both.
• DROP ROLE failing with "role cannot be dropped because some objects depend on it" — the role still owns tables/other objects; either reassign ownership (REASSIGN OWNED BY old_role TO new_role;) or drop those objects first.

Key Takeaways

• In PostgreSQL, "user" and "role" are the same concept — a user is simply a role that can log in.
• GRANT/REVOKE control exactly which operations (SELECT, INSERT, etc.) a role can perform on which objects, down to individual columns.
• Group roles let you manage permissions for many users at once, instead of granting the same privileges repeatedly.