Grant PostgreSQL permissions

Permissions in PostgreSQL are layered. To read one row, the runtime role must be allowed into the database, allowed to use the schema, and allowed to select from the table. Miss any layer and the query fails, each with a different error message. We will grant the layers one at a time so you can recognize each failure later.

Connect to notes_app, become the owner, and create a dedicated schema:

SET ROLE notes_owner;
CREATE SCHEMA app AUTHORIZATION notes_owner;
RESET ROLE;

Application tables will live in app, owned by notes_owner. Keeping them out of public makes every grant explicit.

Let the runtime role connect and resolve names inside that schema:

REVOKE CONNECT ON DATABASE notes_app FROM PUBLIC;
GRANT CONNECT ON DATABASE notes_app TO notes_app;
GRANT USAGE ON SCHEMA app TO notes_app;

The REVOKE line matters because PostgreSQL grants CONNECT to everyone by default through the PUBLIC pseudo-role. After revoking it, only roles you name can even open a connection to this database.

USAGE does not grant access to tables. It only lets the role look up names in the schema. Grant row operations separately:

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app TO notes_app;

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA app TO notes_app;

The sequence grant looks optional. It is not. Identity columns use sequences. Without sequence access, an insert can fail even when the role has INSERT on the table, with ERROR: permission denied for sequence notes_id_seq. That error confuses everyone the first time, because the grant they check is the table one.

Cover tables that do not exist yet

Existing grants do not cover tables created later. Every future migration would leave the runtime role locked out of the new tables. Set default privileges instead. Run these statements as notes_owner:

SET ROLE notes_owner;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO notes_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO notes_app;

RESET ROLE;

Default privileges apply to objects created by the role that ran the statement. That is why SET ROLE notes_owner comes first: migrations run as the owner, so the defaults must belong to the owner.

Verify the outcome from the runtime role’s point of view:

SET ROLE notes_app;
SELECT count(*) FROM app.notes;
RESET ROLE;

The runtime role can now change rows, but it does not own tables and cannot drop them. A leaked application credential is a bad day, not a lost schema.