Grant least privilege to PostgreSQL roles

The fix

-- The owner holds the schema; nothing logs in as it.
CREATE ROLE app_owner NOLOGIN;
CREATE SCHEMA app AUTHORIZATION app_owner;

-- The runtime role logs in and can only use data, not reshape it.
CREATE ROLE app_rw LOGIN PASSWORD 'generated-secret';
GRANT USAGE ON SCHEMA app TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- And for tables created later:
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE ON SEQUENCES TO app_rw;

Why it matters

Most applications connect as a role that owns their tables, and a surprising number connect as postgres. Both give a SQL injection — or a bug in a migration script, or a mistyped psql command — powers it does not need.

Two separations do most of the work:

Superuser is not an application role. A superuser bypasses all permission checks, including row-level security. It can also COPY ... FROM PROGRAM, which runs shell commands as the postgres OS user, and read arbitrary files with pg_read_file(). An injection against a superuser connection is not a data breach; it is a host compromise.

The owner is not the runtime role. This is the subtler one. A role that owns a table can always DROP or ALTER it, regardless of what you granted — ownership implies those rights and they cannot be revoked from an owner. So an app connecting as the owner can drop its own tables no matter how carefully you grant. Splitting owner from runtime means DROP TABLE users fails with a permission error rather than succeeding.

Default privileges are the part people forget

GRANT SELECT ON ALL TABLES IN SCHEMA app grants on the tables that exist at that moment. The next migration creates a table, and the app cannot read it — which is why so many teams end up re-running grants after every deploy, or give up and hand the app ownership.

ALTER DEFAULT PRIVILEGES fixes it properly: it says what to grant on objects created in future, by a specific role. Note the FOR ROLE app_owner clause — default privileges attach to the creating role, so if your migrations run as someone else, name that role instead or the rule never fires.

Read-only means more than SELECT

A reporting role needs SELECT, but it also needs USAGE on the schema, and it should not be able to write anywhere:

CREATE ROLE app_ro LOGIN PASSWORD 'generated-secret';
GRANT USAGE ON SCHEMA app TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;
ALTER ROLE app_ro SET default_transaction_read_only = on;

That last line is a genuine safety net rather than a permission: it makes the session refuse writes even if a grant is wrong somewhere.

Verify it worked

Find roles with superuser — the list should be short and contain no application:

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolcreatedb OR rolcreaterole OR rolbypassrls
ORDER BY rolname;

Confirm the app role does not own its tables:

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'app';

Expect app_owner, not app_rw.

Then prove the separation, which is the only check that really counts — connected as app_rw:

DROP TABLE app.users;

Expect ERROR: must be owner of table users. If it succeeds, the separation is not real.

Check default privileges are actually set:

SELECT defaclrole::regrole AS granting_role,
       defaclnamespace::regnamespace AS schema,
       defaclacl
FROM pg_default_acl;

Before you apply this

Migrations break first. If your deploy runs CREATE TABLE or ALTER TABLE as the application’s runtime role, splitting owner from runtime stops the deploy, not the app.

Decide deliberately how migrations run. Either they connect as app_owner (usually right — a separate credential, used briefly, not held by the running service), or you grant the runtime role CREATE on the schema and accept a weaker separation.

Existing tables don’t move. Creating app_owner doesn’t change who owns what. On an existing database you must reassign:

REASSIGN OWNED BY app_rw TO app_owner;

Run that in a maintenance window — it takes locks on every affected object.

Don’t DROP ROLE to clean up. A role that owns objects or holds grants cannot be dropped, and forcing it with DROP OWNED BY deletes the objects it owns. REASSIGN OWNED BY first, always.

Related

• Lock down the public schema — the schema this replaces.
• Postgres exposed to the internet — why superuser matters so much.