Separate owner and runtime roles

PostgreSQL uses roles for both users and groups. There is no separate “user” concept: a role with the LOGIN attribute can authenticate, and a role without it can own objects without becoming an application credential. CREATE USER is just CREATE ROLE with LOGIN added.

We will use that flexibility to solve a real problem. If the role your application connects with also owns every table, then anyone who steals the application’s credentials can drop your schema. Ownership and runtime access do not need to travel together.

Two roles, two jobs

Create one role that owns the schema, and another that runs the application:

CREATE ROLE notes_owner NOLOGIN;
CREATE ROLE notes_app LOGIN;

notes_owner cannot log in at all. Try psql -U notes_owner and the server answers FATAL: role "notes_owner" is not permitted to log in. That is the point: an owner that is not a credential cannot be phished, leaked, or brute-forced.

notes_app is what your application will use. It needs a password. Set it with \password notes_app in psql, which prompts for the password interactively. This avoids putting the cleartext password in the command history, where CREATE ROLE ... PASSWORD 'secret' would leave it.

Verify both roles with \du:

                       List of roles
  Role name  |          Attributes
-------------+------------------------------
 notes_app   |
 notes_owner | Cannot login

Becoming the owner deliberately

Let your administrator become the owner role only while running migrations:

GRANT notes_owner TO CURRENT_USER
  WITH INHERIT FALSE, SET TRUE;

SET ROLE notes_owner now activates the owner deliberately. With INHERIT FALSE, your admin session does not carry the owner’s privileges all day; it opts in for the migration and runs RESET ROLE after. That keeps a fat-fingered DROP TABLE in a routine session from touching objects it happens to own.

One more rule that trips people up: role attributes such as LOGIN, CREATEDB, and CREATEROLE are never inherited like table privileges. Granting a role membership in another role passes along its object permissions, not its attributes. If a role needs to create databases, that attribute goes on the role itself.

The next lessons give these two roles a database of their own and exactly the permissions each one needs.