Privileges

Grant an application role only what it uses, and let a separate migration role own the schema. Everything on this page is ordinary SQL your managed elevated role can run.

There is one step people miss, and it fails a week later rather than at the time. It is the third section down.

The shape that works #

Own the schema with a migration role, and grant the application role only what it uses.

-- Ownership sits with the role that runs migrations.
CREATE ROLE app_migrator LOGIN PASSWORD 'REDACTED-EXAMPLE-ONLY';
CREATE SCHEMA app AUTHORIZATION app_migrator;

-- The application role connects and uses, and owns nothing.
CREATE ROLE app_user LOGIN PASSWORD 'REDACTED-EXAMPLE-ONLY';
GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user;

The step everyone forgets #

GRANT ... ON ALL TABLES grants on the tables that exist right now. The next migration creates a table your application cannot read, and the error appears in production rather than in the migration.

Set default privileges so future objects are covered:

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA app
  GRANT USAGE ON SEQUENCES TO app_user;

FOR ROLE app_migrator matters. Default privileges attach to the role that creates the object, so setting them for the wrong role produces exactly the same failure with an extra hour of confusion.

Revoke the public schema #

New databases give everyone rights on public. If you are using your own schema, take them away:

REVOKE ALL ON SCHEMA public FROM PUBLIC;

Read-only, properly #

A reporting role that is genuinely read-only needs the default privileges treatment too, or it starts failing the week after the next migration:

CREATE ROLE app_readonly LOGIN PASSWORD 'REDACTED-EXAMPLE-ONLY';
GRANT CONNECT ON DATABASE appdb TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;

What you will see #

Check what a role actually holds:

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

One row per table per privilege. An application role set up as above shows SELECT, INSERT, UPDATE and DELETE on each table in its schema, and nothing in public.

Default privileges are in a different catalogue, because they are grants on objects that do not exist yet:

SELECT pg_get_userbyid(defaclrole) AS granted_by,
       defaclnamespace::regnamespace AS schema,
       defaclobjtype AS object_type,
       defaclacl AS grants
FROM pg_default_acl;

An empty result here is the usual cause of "the application could read the table yesterday".

Troubleshooting #

permission denied for schema app. The role has no USAGE on the schema. CONNECT on the database is not enough.

permission denied for table, on a table created by the last migration. Default privileges are missing, or they were set FOR ROLE the wrong role. They attach to the role that creates the object, so set them for your migration role.

permission denied for sequence. An INSERT into a table with a bigserial column needs USAGE on its sequence. Grant it, and add sequences to the default privileges too.

A read-only role can still write. Check it did not inherit through another role, and check public. A new database grants everyone rights on public until you revoke them.

Related #

• Databases and roles
• Privilege model