PostgreSQL roles and permissions: least privilege that holds

Most applications connect to PostgreSQL as the superuser, because that is the password the host handed over and it works. It works until the day a bug, an injected query or a tired person at a terminal does something the application should never have been able to do: drop a table, read another tenant's rows, or turn off a setting that protects the server. Least privilege is not paranoia here, it is about ten minutes of SQL, and this post is that SQL plus the parts of the model that make it stick.

The short version: create a role that owns the schema and runs migrations, a role the application logs in as with data rights only, and a read-only role for you and your reporting tools. Revoke what PostgreSQL grants to everybody by default. Then set default privileges, because without them the next migration creates tables your application cannot read.

Roles are users and groups at the same time#

PostgreSQL has one concept where other systems have two. A role can log in, hold privileges, and contain other roles. CREATE USER is CREATE ROLE ... LOGIN with a different spelling, and nothing else distinguishes them.

CREATE ROLE app_owner NOLOGIN;CREATE ROLE app LOGIN PASSWORD 'generated-not-chosen';CREATE ROLE readonly LOGIN PASSWORD 'also-generated' CONNECTION LIMIT 3;GRANT app_owner TO app;   -- only if you want app to act as the owner

The attributes a role can carry are few and each is a decision:

INHERIT is the one worth thinking about. With it on, a member of a group has the group's rights the moment it connects. With NOINHERIT, it has to ask: SET ROLE app_owner;. That extra step is a decent seatbelt for a human account that occasionally needs to run a migration, because the dangerous rights are not active by accident.

\du in psql prints the whole picture: every role, its attributes and its memberships. It is the first command to run on a database you have inherited from someone else.

One note on versions: PostgreSQL 16 tightened what CREATEROLE can do, so a role with it can no longer hand out membership in arbitrary roles it does not administer. If you built a self-service user-creation flow on older behaviour, test it on your version rather than assuming.

What a fresh database gives away#

A new database is not empty of permissions. PostgreSQL has a pseudo-role called PUBLIC that every role belongs to implicitly and that you cannot drop, and out of the box PUBLIC holds more than people expect:

• CONNECT on the database, so any role that can authenticate can open it.
• TEMP, so it can create temporary tables.
• USAGE on the public schema, so it can see what is in there.
• EXECUTE on functions, including the ones your extensions installed.

Before PostgreSQL 15, PUBLIC also had CREATE on the public schema, which meant any role that could connect could create tables in it. That changed in 15: the public schema is now owned by pg_database_owner and CREATE is not granted to PUBLIC. This is the single most common version-dependent surprise in this area, in both directions. On 15 and later you will hit permission denied for schema public when a migration runs as a role that is not the owner. On 14 and earlier you will find that a read-only role can still create tables.

Close the gap explicitly, on any version:

REVOKE ALL ON DATABASE shop FROM PUBLIC;REVOKE ALL ON SCHEMA public FROM PUBLIC;GRANT CONNECT ON DATABASE shop TO app, readonly;

Do that before you create the application roles and you start from deny rather than from a default you have to remember.

A least-privilege setup, in full#

Three roles, one schema, run once as the superuser or the database owner. Adjust the names; the shape is the point.

-- 1. The owner. Owns every object, runs migrations, never logs in from the app.CREATE ROLE shop_owner NOLOGIN;ALTER DATABASE shop OWNER TO shop_owner;CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION shop_owner;-- 2. The application. Reads and writes rows, changes nothing structural.CREATE ROLE shop_app LOGIN PASSWORD 'from-a-password-manager';GRANT CONNECT ON DATABASE shop TO shop_app;GRANT USAGE ON SCHEMA app TO shop_app;GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO shop_app;GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO shop_app;-- 3. Reporting and humans. Reads, and only reads.CREATE ROLE shop_read LOGIN PASSWORD 'a-different-one' CONNECTION LIMIT 3;GRANT CONNECT ON DATABASE shop TO shop_read;GRANT USAGE ON SCHEMA app TO shop_read;GRANT SELECT ON ALL TABLES IN SCHEMA app TO shop_read;

Four things in there are deliberate and worth spelling out.

The application role is not the owner. GRANT SELECT, INSERT, UPDATE, DELETE is not the same as owning the table: the owner can ALTER and DROP it and no GRANT gives that away. An injected DROP TABLE orders from the application role fails with a permission error instead of ending your evening.

The migration role is separate. Your migration tool connects as shop_owner (or as a login role that is a member of it) with a different password, used by a deploy job rather than by request handlers. That is also what makes the expand-and-contract pattern in schema migrations without downtime safe to automate.

Sequences are granted separately. A serial column calls nextval on a sequence, and a role with INSERT on the table but nothing on the sequence gets permission denied for sequence orders_id_seq on the first insert. Identity columns (GENERATED ... AS IDENTITY) behave differently, so run one insert as the application role and confirm rather than assuming either way.

`ALL TABLES IN SCHEMA` means all tables that exist right now. It is a loop over today's objects, not a standing rule. Which brings us to the step that makes the whole thing survive.

Default privileges: the step everyone misses#

The first migration after you set up roles creates a table, the application says permission denied for table invoices, and somebody re-runs the GRANT by hand. Then it happens again next month. ALTER DEFAULT PRIVILEGES is the fix:

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_app;ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app  GRANT USAGE, SELECT ON SEQUENCES TO shop_app;ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app  GRANT SELECT ON TABLES TO shop_read;

The trap is FOR ROLE. Default privileges attach to the role that creates the object, not to the schema in general. If you set them for shop_owner and then run a migration as the superuser, the new table gets none of them. Whichever role your migrations run as, that is the role in the FOR ROLE clause, and it should be the same role every time.

\ddp in psql lists the default privileges currently in force, which is the fastest way to confirm you attached them to the right role.

Schemas and the search path#

A schema is a namespace for tables, and it is also the natural boundary for permissions. GRANT USAGE ON SCHEMA is the gate: without it, no privilege on any table inside is reachable.

The search_path decides which schema an unqualified table name resolves to. Its default is "$user", public, meaning a schema named after the connecting role if one exists, then public. Set it per role so the application does not have to qualify every name:

ALTER ROLE shop_app SET search_path = app, public;ALTER ROLE shop_app SET statement_timeout = '30s';ALTER ROLE shop_read SET statement_timeout = '120s';

Those last two lines are worth the trouble on a small server. A role-level statement_timeout means a runaway report cannot hold a connection for an hour, and it applies without touching a line of application code. The other settings in that family are in PostgreSQL tuning for small servers.

search_path also has a security edge. A SECURITY DEFINER function that calls an unqualified table name will resolve it through the caller's search_path, which lets a caller point it at a table they control. Always set the path explicitly on such functions:

CREATE FUNCTION app.recalc_totals() RETURNS void  LANGUAGE sql SECURITY DEFINER SET search_path = app, pg_tempAS $$ UPDATE app.orders SET total = ... $$;

Built-in roles worth knowing#

PostgreSQL ships a set of predefined roles so that common jobs do not need a superuser. Granting one of these to a human account is almost always better than granting SUPERUSER.

pg_monitor plus pg_signal_backend covers nearly everything an on-call person needs to diagnose a stuck database, and neither can read a row of customer data. pg_read_all_data is the honest way to give an analyst access to everything without also giving them the ability to change it.

Row-level security, and when it is worth it#

Table-level grants say who may read a table. Row-level security says which rows. It is the correct tool for multi-tenant data where one mistaken WHERE clause would leak another customer's records.

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;CREATE POLICY tenant_isolation ON app.orders  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);GRANT SELECT, INSERT, UPDATE, DELETE ON app.orders TO shop_app;

The application then sets the tenant once per transaction, and every query is filtered whether or not it remembered to be:

BEGIN;SET LOCAL app.tenant_id = '9f1c...';SELECT * FROM app.orders;   -- only this tenant's rows, alwaysCOMMIT;

Three caveats decide whether this is a good idea for you. Enabling RLS with no policy denies everything, which is a safe default and a confusing first five minutes. The table owner is not subject to its own policies unless you add FORCE ROW LEVEL SECURITY, so test as the application role, not as the owner. And superusers and any role with BYPASSRLS ignore policies entirely, which is one more reason the application should not be a superuser.

There is a cost: the policy expression is added to every query, so index the column the policy filters on. Beyond that, the performance question is the usual one, covered in PostgreSQL indexes explained.

Auditing what you granted, and taking it back#

Permissions drift. Check them the way you check anything else, with a query rather than a memory.

-- Who can do what to a table\dp app.orders-- Every table privilege held by one roleSELECT table_schema, table_name, privilege_typeFROM information_schema.role_table_grantsWHERE grantee = 'shop_app'ORDER BY table_schema, table_name;-- A direct questionSELECT has_table_privilege('shop_app', 'app.orders', 'DELETE');

In \dp output the privileges are letters: r for SELECT, a for INSERT, w for UPDATE, d for DELETE, D for TRUNCATE, x for REFERENCES, t for TRIGGER. An entry of =r/shop_owner means PUBLIC has SELECT, granted by shop_owner, and is usually something to revoke.

Removing a role is where people get stuck. DROP ROLE fails while the role owns anything or holds any privilege, with a message listing dependent objects. The sequence is:

REASSIGN OWNED BY old_role TO shop_owner;DROP OWNED BY old_role;DROP ROLE old_role;

REASSIGN OWNED BY transfers ownership of objects; DROP OWNED BY removes the remaining grants. Both act on the current database only, so run them in each database the role touched before the final DROP ROLE, which is cluster-wide.

For rotation, change the password rather than the role. In psql, use \password shop_app: it prompts, hashes the password on your machine and sends only the hash, so the plain text never reaches the server log or your shell history. ALTER ROLE shop_app PASSWORD 'literal' does the opposite.

Two RE:NODE-specific notes, because the two layers get confused. The database superuser password is generated per server and it is yours; the roles above are things you create inside that database and the panel knows nothing about them. Panel access is separate: subusers, roles and teams control who can open the console, touch files or take a backup, and a person can have one without the other. Give a teammate a panel subuser with the permissions they need, and a database role with the rows they need, and treat the two as different questions. Subusers and least privilege covers the panel side, and the database security checklist covers what is left.

The permission errors you will actually meet#

PostgreSQL's messages are precise once you know which layer they come from. These are the ones that account for most of the lost afternoons.

`permission denied for schema public` - almost always PostgreSQL 15 or later, where PUBLIC no longer has CREATE on that schema. The role running the migration is not the schema owner. Either run migrations as the owner, or GRANT CREATE ON SCHEMA public TO shop_owner, or move your tables into a schema of your own, which is tidier anyway.

`permission denied for table orders` - three candidates, in order of likelihood: the grant was never made for this table because it was created after you ran GRANT ... ON ALL TABLES; the role is reading a different schema than you think, so check SHOW search_path; or you granted to the wrong role name and nobody noticed because the superuser kept working.

`permission denied for sequence orders_id_seq` - the insert path. Grant USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app and add the matching default privilege.

`must be owner of table orders` - the role is trying to ALTER, DROP, add an index or add a constraint. Ownership is not a privilege you can grant; it is transferred with ALTER TABLE ... OWNER TO. This error means your migration is running as the application role, which is exactly the separation working as designed.

`must be superuser to execute ALTER SYSTEM` and similar - server-level configuration. Note that CREATE EXTENSION is not always in this category: since PostgreSQL 13 a set of extensions are marked trusted and a database owner can install those without a superuser.

A query that returns zero rows and no error - if row-level security is enabled on the table, a policy that does not match is not an error, it is an empty result. Check pg_policies and confirm the session variable your policy reads is actually set.

`role "old_app" cannot be dropped because some objects depend on it` - the REASSIGN OWNED BY and DROP OWNED BY sequence above, in every database the role touched.

`no pg_hba.conf entry for host ...` - not a permission problem at all. That is host-based authentication refusing the connection before any role privilege is consulted, and it is fixed in a different file. PostgreSQL remote connections walks through that layer.

FAQ#

What is the difference between a role and a user in PostgreSQL?

Nothing, apart from spelling. CREATE USER is shorthand for CREATE ROLE ... LOGIN. A role with LOGIN can connect; a role without it is used as a group, or as an owner that nobody logs in as.

Why does my app get "permission denied" only for new tables?

Because GRANT ... ON ALL TABLES IN SCHEMA applied to the tables that existed when you ran it. Set ALTER DEFAULT PRIVILEGES FOR ROLE <the role your migrations run as> so future tables carry the grant automatically.

Should the application connect as the database owner?

No. The owner can drop and alter every object, and nothing in your application needs that. Let a migration job connect as the owner and let request handlers connect as a role with data rights only.

How do I give someone read-only access safely?

Create a login role, grant CONNECT on the database, USAGE on the schemas, SELECT on the tables, and add default privileges for future ones. On PostgreSQL 14 and later, GRANT pg_read_all_data TO analyst does the same job in one line across every schema.

Do I need row-level security for a multi-tenant app?

Not always, but it is the only approach that survives a forgotten WHERE clause. If a leak between tenants would be a serious incident, enable it, add FORCE ROW LEVEL SECURITY on the tables, and index the tenant column.

How do I rotate a database password without downtime?

Create a second login role with the same grants, deploy the application with the new credentials, confirm nothing is still connecting as the old one in pg_stat_activity, then drop it. Changing a password in place works too, but it disconnects nothing and reconnects everything at once, which is a worse moment to discover a typo.