Who may do what: roles, GRANT and a read-only door

Everyone connects as the owner, until the day that matters

A small project starts with one database user, the one that created the tables, and every tool, script and person uses it. It works until an analyst’s exploratory query includes a DELETE by mistake, or an AI assistant generating SQL decides that the cleanest way to answer a question is to create a temporary table, or a laptop with the password on it is lost. Permissions are the difference between “that could not have happened” and “we are restoring from backup”.

Roles and grants

A role is a named set of permissions, and a user is a role that can log in:

CREATE ROLE analyst LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE school TO analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

The last line is the one people forget. GRANT ... ON ALL TABLES covers the tables that exist today; ALTER DEFAULT PRIVILEGES covers the ones created next month, so the analyst does not lose access to every new table until somebody remembers. With this, analyst can read everything and write nothing: an UPDATE fails with permission denied for table orders, which is the error you want them to see.

A belt to go with the braces: ALTER ROLE analyst SET default_transaction_read_only = on; makes every transaction the role opens read-only at the session level, so that even a table they have somehow been granted write access to cannot be changed without an explicit override.

Three users, not one

The usual shape for a real system:

• The migration user owns the tables and can alter them. Used only by the migration tool, from a deployment pipeline, never interactively.
• The application user can SELECT, INSERT, UPDATE and DELETE on the tables the application needs, and cannot alter anything. If the application is compromised, the attacker cannot drop a table.
• The analyst user can SELECT, and often only from views that omit personal data.

The superuser, postgres, is used by nobody day to day. Its password lives in a password manager and its use is an event.

Hiding columns and rows

GRANT SELECT (id, display_name, city) ON customers TO analyst grants three columns and not the phone or email beside them. A view, as the earlier lesson said, does the same more readably and can also transform: left(email, 3) || '***' for a partly masked address. Row-level security goes further:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY branch_only ON orders FOR SELECT TO branch_manager
  USING (branch_id = current_setting('app.branch_id')::int);

A branch manager’s session sets app.branch_id and sees only their branch’s orders, in every query, without any WHERE clause. The policy is enforced by the database rather than by the application remembering, which is the same argument as for constraints.

The read-only door for an AI

An assistant that writes SQL from a question is a user that types fast, does not read the schema carefully, and cannot be embarrassed. Give it the analyst role and nothing more. Add SET statement_timeout = '30s' for the role, so that a query it writes with an accidental cross join is killed rather than left to run for an hour. Point it at a read replica if one exists, so that its scans do not compete with the application. And keep personal columns behind views, because a model that has seen a phone number may repeat it. The final module of this course is about getting good SQL out of an AI; this lesson is about making sure the bad SQL cannot do harm.

The engines that have no roles

SQLite has no users. Access is the file’s permissions on disk, and a read-only connection is opened with ?mode=ro in the connection string or by giving the process a file it cannot write. DuckDB attaches a database read-only with ATTACH 'school.duckdb' (READ_ONLY). Both are the right level of control for a single machine and no substitute for roles on a shared server.

The habit

When a new person, tool or program needs the database, create a role for it with the least it needs, and write down why. When a role has not been used for a quarter, revoke it. The list of roles is the list of everyone who could have changed the data, and it should be short enough to read aloud.