PostgreSQL Roles and Permissions (GRANT)
On this page
The access requirements
Your company's salesdb database is moving to production. Today everyone connects as postgres, the superuser. That means any bug, leaked password or mistyped command can do anything at all. You've been asked to set up proper access:
- A dashboard service that can read the
salesschema and nothing else. - An order service that can read and write
sales, but can't change the schema. - Analysts who can read everything except customers' email addresses.
- Migrations that run as their own role and create tables nobody has to remember to grant access to.
This lesson builds that setup and explains each piece along the way.
Roles: users and groups
PostgreSQL has one concept: the role. A role with the LOGIN attribute can connect, so it behaves like a user. A role without LOGIN can't connect, but other roles can be members of it, so it behaves like a group. CREATE USER is just CREATE ROLE ... LOGIN.
The design that ages well:
- Create group roles (no LOGIN) for each kind of access.
- Grant privileges to the group roles only.
- Create login roles for people and services, and make them members of the right groups.
Onboarding or offboarding someone then changes one membership, not dozens of grants.
CREATE SCHEMA sales;
CREATE TABLE sales.customers (id int PRIMARY KEY, name text, email text);
CREATE TABLE sales.orders (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id int, total numeric);
CREATE ROLE sales_read NOLOGIN;
CREATE ROLE sales_write NOLOGIN;
GRANT sales_read TO sales_write; -- writers can read too
CREATE ROLE dashboard_svc LOGIN PASSWORD 'change-me' CONNECTION LIMIT 10;
CREATE ROLE order_svc LOGIN PASSWORD 'change-me';
GRANT sales_read TO dashboard_svc;
GRANT sales_write TO order_svc;CONNECTION LIMIT stops one runaway service from using up every connection on the server. Use a real secret instead of change-me, and keep application roles away from SUPERUSER, CREATEDB and CREATEROLE.
GRANT at the database, schema and table level
Reaching a table takes three separate permissions, and missing any one of them blocks access:
| Layer | Privilege | Without it |
|---|---|---|
| Database | CONNECT | "permission denied for database" |
| Schema | USAGE | "permission denied for schema", even with table grants |
| Table | SELECT, INSERT, UPDATE, DELETE | "permission denied for table" |
GRANT CONNECT ON DATABASE salesdb TO sales_read;
GRANT USAGE ON SCHEMA sales TO sales_read;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO sales_write;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO sales_write;The sequence grant matters for serial columns. Inserting into one calls nextval, which needs USAGE on its sequence. Identity columns (Day 20) don't need it.
Lock down the defaults too. Every role is automatically a member of a pseudo-role called PUBLIC, and by default PUBLIC can connect to every database. Remove that so access is opt-in:
REVOKE ALL ON DATABASE salesdb FROM PUBLIC;Column-level privileges
Privileges can be granted per column. Analysts get every customer column except email:
CREATE ROLE analyst NOLOGIN;
GRANT CONNECT ON DATABASE salesdb TO analyst;
GRANT USAGE ON SCHEMA sales TO analyst;
GRANT SELECT ON sales.orders TO analyst;
GRANT SELECT (id, name) ON sales.customers TO analyst;An analyst who runs SELECT * FROM sales.customers gets "permission denied", because * includes email. They have to list the columns they're allowed to see. For hiding rows rather than columns, such as letting each tenant see only its own data, use row-level security (Day 29).
ALTER DEFAULT PRIVILEGES for future tables
GRANT ... ON ALL TABLES only covers tables that exist right now. Next week a migration adds sales.refunds, and the dashboard can't read it. Default privileges fix this. They say "whenever this role creates a table here, grant this":
CREATE ROLE migrator LOGIN PASSWORD 'change-me';
GRANT USAGE, CREATE ON SCHEMA sales TO migrator;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales
GRANT SELECT ON TABLES TO sales_read;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales
GRANT INSERT, UPDATE, DELETE ON TABLES TO sales_write;The FOR ROLE migrator part is the most common source of confusion. Default privileges apply to objects created by that role. Set them up for your own admin role while migrations run as migrator, and they never fire. In psql, \ddp shows what's configured.
Testing permissions with SET ROLE
Don't assume the setup works. Become each role and try. SET ROLE switches your session's privileges to another role you're a member of, which is how you test as a superuser:
SET ROLE dashboard_svc;
SELECT count(*) FROM sales.orders; -- works
INSERT INTO sales.orders (customer_id, total) VALUES (1, 10);
-- ERROR: permission denied for table orders
RESET ROLE;A few more tools worth knowing:
- Predefined roles. PostgreSQL ships roles like
pg_read_all_data(read every table) andpg_monitor(read monitoring views). They're handy for backup tools and monitoring agents. - NOINHERIT for break-glass access. Membership normally gives a role's privileges automatically. Grant an admin role with
GRANT db_admin TO bob WITH INHERIT FALSE(PostgreSQL 16+), and Bob must runSET ROLE db_adminon purpose before using it. - Audits.
information_schema.role_table_grantslists table grants. Review them now and then for privileges that crept in.
Check yourself
The role also needs GRANT USAGE ON SCHEMA billing. Schema usage is a separate privilege that controls whether a role can even look up objects in the schema, so table grants have no effect until it's granted. Grant both to a group role, then add the service's login role to that group.
The default privileges were probably created without FOR ROLE deployer, so they apply to a different creator role. Recreate them with FOR ROLE deployer, and grant on the tables that are already missing it.
Very little: revoke their membership in the group roles, or drop their login role after reassigning anything they own. Because every privilege was granted to group roles rather than to individuals, no table-level grants need to change, which is the main reason to design permissions around groups.
Use SET ROLE reporting_ro; in a session as a member of that role, try the queries, then RESET ROLE;. Functions like has_table_privilege('reporting_ro', 'billing.invoices', 'SELECT') also answer specific questions directly.
The owner can do anything to its tables, including DROP, ALTER and bypassing row-level security by default. If the application is compromised, so is the schema. Connect as a role that has only the SELECT, INSERT, UPDATE and DELETE it needs, and run migrations as a separate owner role.