In Development: This textbook is currently undergoing development and should not be used as an authoritative source of... well, ANYTHING.

20  Security

20.1 Learning Objectives

By the end of this chapter, students will be able to:

• Explain the difference between a PostgreSQL role and a user and describe when each term applies.
• Create login roles and group roles using CREATE ROLE with appropriate attributes (LOGIN, PASSWORD, CONNECTION LIMIT).
• Modify role attributes using ALTER ROLE, including session-level configuration parameters.
• Grant role membership using GRANT role TO user and explain how the INHERIT attribute propagates privileges automatically.
• Drop a role safely using the REASSIGN OWNED BY / DROP OWNED BY / DROP ROLE sequence.
• Explain the fail-closed principle of PostgreSQL privileges: nothing is permitted by default.
• Grant and revoke object privileges (SELECT, INSERT, UPDATE, DELETE) using GRANT and REVOKE.
• Configure ALTER DEFAULT PRIVILEGES to automatically grant access to objects created in the future.
• Describe the two-layer access requirement: schema-level USAGE plus object-level privilege.
• Design a privilege model for a multi-user database with distinct read-only, read-write, and administrative roles.

Every database system needs to answer two questions about anyone who connects to it: who are you, and what are you allowed to do? PostgreSQL answers both questions through a single concept, the role, combined with an explicit privilege system that controls what each role can access.

This chapter covers both halves of that model. The first half explains roles: how to create them, group them, and manage their lifecycle. The second half covers privileges: how to grant and revoke access to specific database objects, and how to structure a clean, maintainable permission model.

The examples assume you are working on your local Docker container, where you have superuser access. Role management requires elevated privileges that are not available on shared or hosted databases, so this is the right environment to practice.

20.2 Roles and Users

20.2.1 Roles vs. Users

PostgreSQL makes no fundamental distinction between a role and a user. Both are created with CREATE ROLE. The only practical difference is that a role with the LOGIN attribute can open a database connection; one without it cannot. The CREATE USER command is simply shorthand for CREATE ROLE ... WITH LOGIN.

-- These two statements are equivalent
CREATE USER analyst WITH PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret';

The term user refers to a role that can log in. The term role is broader: it covers both login-capable accounts and pure permission groups that are never used to log in directly.

20.2.2 Creating Roles

CREATE ROLE role_name [WITH option [option ...]];

The most important options:

Option	Meaning
LOGIN	Allows the role to connect to the database
PASSWORD 'pw'	Sets the login password
SUPERUSER	Bypasses all permission checks
CREATEDB	Allows the role to create new databases
CREATEROLE	Allows the role to create and manage other roles
INHERIT	Automatically inherits privileges from parent roles (default)
CONNECTION LIMIT n	Caps concurrent connections; -1 means unlimited
VALID UNTIL 'date'	Expires the password (not the role) on a given date

Create a read-only analyst account:

CREATE ROLE analyst
    WITH LOGIN
         PASSWORD 'changeme'
         CONNECTION LIMIT 5;

Create a group role that will never log in directly but will hold shared privileges:

CREATE ROLE reporting;

20.2.3 Listing Roles

Inspect existing roles in the pg_roles catalog view:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb,
       rolcanlogin, rolconnlimit, rolvaliduntil
FROM   pg_roles
ORDER  BY rolname;

In psql, the \du meta-command shows a formatted role list with attributes and group memberships.

20.2.4 Modifying Roles

ALTER ROLE changes any attribute after creation:

-- Extend a password expiry date
ALTER ROLE analyst VALID UNTIL '2027-01-01';

-- Revoke the ability to create databases
ALTER ROLE analyst NOCREATEDB;

-- Change a password
ALTER ROLE analyst PASSWORD 'newpassword';

ALTER ROLE also sets session-level configuration parameters for a specific role. Every time that role logs in, the parameter is applied automatically:

-- Always search the reporting schema first for this role
ALTER ROLE analyst SET search_path TO reporting, public;

20.2.5 Dropping Roles

DROP ROLE analyst;

A role cannot be dropped if it owns any database objects or has any privileges granted to it. You must either reassign its objects or drop them first:

REASSIGN OWNED BY analyst TO postgres;
DROP OWNED BY analyst;
DROP ROLE analyst;

REASSIGN OWNED BY transfers ownership of every object the role owns to another role. DROP OWNED BY then removes any remaining grants before DROP ROLE can succeed.

20.2.6 Role Inheritance

A role can be a member of another role. If the member role has INHERIT enabled (the default), it automatically acquires all privileges belonging to the parent. This lets you define permission groups once and assign them to many users:

-- Group role with no login
CREATE ROLE reporting;

-- Two login users added to the group
CREATE ROLE alice WITH LOGIN PASSWORD 'pw1';
CREATE ROLE bob   WITH LOGIN PASSWORD 'pw2';

GRANT reporting TO alice;
GRANT reporting TO bob;

Any privileges granted to reporting are automatically available to both alice and bob. When you need to give both users access to a new table, you grant it to reporting once rather than to each user individually.

20.2.6.1 INHERIT vs. SET ROLE

Even with INHERIT, there is a distinction between having a privilege through inheritance and acting as a role. Some sensitive operations require a role to explicitly switch its active identity using SET ROLE:

SET ROLE reporting;
SELECT current_role;   -- returns 'reporting'

RESET ROLE;            -- reverts to the original login role

SET ROLE is useful when a user belongs to a powerful group role and you want those elevated privileges exercised intentionally rather than by default.

20.2.7 The Superuser

A superuser bypasses every permission check except login restrictions. PostgreSQL installations include a built-in postgres superuser used for administration. Treat it like root on a Unix system: use it only when necessary, never for routine application queries.

CREATE ROLE dba
    WITH LOGIN
         SUPERUSER
         PASSWORD 'strongpassword'
         CONNECTION LIMIT 2;

Creating a named DBA role is preferable to sharing the postgres account, because individual actions can be attributed to a specific person in the logs.

20.2.8 Practical Role Design

A common pattern for a data platform with multiple consumer types:

-- Permission groups (no login)
CREATE ROLE db_reader;
CREATE ROLE db_writer;
CREATE ROLE db_admin;

-- Individual accounts assigned to groups
CREATE ROLE alice   WITH LOGIN PASSWORD '...';
CREATE ROLE bob     WITH LOGIN PASSWORD '...';
CREATE ROLE svc_etl WITH LOGIN PASSWORD '...';

GRANT db_reader TO alice;
GRANT db_writer TO svc_etl;
GRANT db_admin  TO bob;

Privileges are then granted to the three group roles, not to individuals. Adding a new team member is a single GRANT rather than a full permission audit.

20.3 Privileges

A privilege is an explicit permission that grants a role the right to perform a specific action on a specific object. Nothing is permitted by default. A newly created role that logs in successfully cannot read any table, execute any function, or create any schema until a privilege is granted. This fail-closed design means security is opt-in rather than opt-out.

20.3.1 Object Privileges

PostgreSQL assigns privileges at the level of individual database objects. The privileges available depend on the object type:

Object	Available Privileges
Table or view	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
Sequence	USAGE, SELECT, UPDATE
Schema	USAGE, CREATE
Database	CONNECT, CREATE, TEMP
Function	EXECUTE

For most data work, the privileges you care about most are SELECT, INSERT, UPDATE, DELETE on tables, USAGE on schemas, and CONNECT on databases.

20.3.2 GRANT

The GRANT statement gives one or more privileges to one or more roles:

GRANT privilege [, privilege ...]
ON   object_type object_name
TO   role_name [, role_name ...];

Grant read access on the students table to the reporting group role:

GRANT SELECT ON students TO reporting;

Because alice and bob are members of reporting and have INHERIT enabled, they immediately acquire SELECT on students without needing individual grants.

Grant multiple privileges in a single statement:

GRANT SELECT, INSERT, UPDATE ON students TO reporting;

Grant on all tables in a schema at once using ALL TABLES IN SCHEMA:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

20.3.2.1 Granting to PUBLIC

The special grantee PUBLIC represents every role in the database. PostgreSQL grants CONNECT on every new database and USAGE on the public schema to PUBLIC by default. You can revoke these defaults to tighten security:

REVOKE CONNECT ON DATABASE university FROM PUBLIC;
REVOKE CREATE  ON SCHEMA public FROM PUBLIC;

After revoking the schema CREATE privilege from PUBLIC, users can still read existing tables but cannot create new objects in public without an explicit grant.

20.3.2.2 WITH GRANT OPTION

A role that receives a privilege with WITH GRANT OPTION can pass that privilege on to other roles:

GRANT SELECT ON students TO alice WITH GRANT OPTION;

Use this carefully. If alice later has her grant revoked, any grants she made downstream are also revoked automatically.

20.3.3 REVOKE

REVOKE removes a previously granted privilege:

REVOKE privilege [, privilege ...]
ON   object_type object_name
FROM role_name [, role_name ...];

Revoke write access from reporting while leaving read access intact:

REVOKE INSERT, UPDATE ON students FROM reporting;

To revoke all privileges on an object at once:

REVOKE ALL ON students FROM reporting;

If a role granted a privilege WITH GRANT OPTION and that role’s grant is now revoked, use CASCADE to also revoke any downstream grants that role made:

REVOKE SELECT ON students FROM alice CASCADE;

Without CASCADE, the revoke statement will fail if downstream grants exist.

20.3.4 Checking Privileges

In psql, \dp table_name displays the access control list (ACL) for a table. The output uses a compact notation where each entry takes the form grantee=privilege_letters/grantor. The letters map to privileges: r = SELECT, a = INSERT, w = UPDATE, d = DELETE, D = TRUNCATE, x = REFERENCES, t = TRIGGER.

For a more readable view, query information_schema:

SELECT grantee, privilege_type, is_grantable
FROM   information_schema.role_table_grants
WHERE  table_name = 'students'
ORDER  BY grantee, privilege_type;

The has_table_privilege() function tests whether a specific role has a specific privilege, which is useful for auditing before making changes:

SELECT has_table_privilege('alice', 'students', 'SELECT');
SELECT has_table_privilege('bob',   'students', 'INSERT');

20.3.5 DEFAULT PRIVILEGES

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting covers only tables that already exist. New tables created later receive no grants automatically. ALTER DEFAULT PRIVILEGES solves this by specifying privileges that should be granted whenever a new object is created:

ALTER DEFAULT PRIVILEGES
    FOR ROLE postgres
    IN SCHEMA public
    GRANT SELECT ON TABLES TO reporting;

This says: whenever the postgres role creates a new table in the public schema, automatically grant SELECT on that table to reporting. The FOR ROLE clause specifies whose future creates this applies to. If multiple roles create tables, you must set default privileges separately for each.

20.3.6 Schema-Level Access Control

The schema is an important but easily overlooked layer of the privilege system. A role needs both:

1. USAGE on the schema to see objects inside it.
2. The appropriate object privilege (SELECT, INSERT, etc.) on each object.

Having SELECT on a table but not USAGE on its schema produces a “permission denied for schema” error. Having USAGE on the schema but not SELECT on a table produces a “permission denied for table” error. Both grants are required.

When building a permission model for multiple user types, schemas serve as a useful organizing principle:

-- Create schemas
CREATE SCHEMA registrar;
CREATE SCHEMA finance;

-- Grant access to appropriate groups
GRANT USAGE ON SCHEMA registrar TO reporting;
GRANT USAGE ON SCHEMA finance   TO db_admin;

-- Grant default privileges within each schema
ALTER DEFAULT PRIVILEGES IN SCHEMA registrar
    GRANT SELECT ON TABLES TO reporting;

With this structure, the reporting group can read registrar data but has no visibility into finance tables.

20.4 Putting It All Together

Here is a coherent permission model for a database with a read-only reporting group:

-- Step 1: Restrict public access
REVOKE CONNECT ON DATABASE university FROM PUBLIC;
REVOKE CREATE  ON SCHEMA public FROM PUBLIC;

-- Step 2: Allow the reporting group to connect and see the schema
GRANT CONNECT ON DATABASE university TO reporting;
GRANT USAGE   ON SCHEMA public       TO reporting;

-- Step 3: Grant read access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

-- Step 4: Set default privileges for future tables
ALTER DEFAULT PRIVILEGES
    FOR ROLE postgres
    IN SCHEMA public
    GRANT SELECT ON TABLES TO reporting;

After these four steps, members of reporting can connect, see tables in public, and query any current or future table, without being able to write or create objects.

20.5 Exercises

20.5.1 Reflection

1. PostgreSQL has no separate concept of a “user” and a “group.” Explain how the LOGIN attribute is the only meaningful distinction, and describe when you would create a role without LOGIN.

2. What is the difference between GRANT reporting TO alice and SET ROLE reporting? Under what circumstance would you use SET ROLE?

3. Why does GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting not automatically cover tables created in the future? What command closes that gap?

4. Explain the two-layer requirement for schema access: why must a role have both USAGE on the schema and SELECT on the table? What error does each missing grant produce?

20.5.2 Coding

5. Create a role named data_analyst with login access, a password of your choice, and a connection limit of 3. Verify it appears in pg_roles.

6. Create a group role named read_only with no login. Then create two login roles, alice and bob, and add both as members of read_only. Query pg_auth_members to confirm the memberships.

7. Grant SELECT on the students table to alice directly. Run \dp students and confirm the grant appears. Then revoke it and grant to reporting instead. Connect as alice using SET ROLE and verify she can still query the table.

8. Run GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting. Create a new table courses. Query it as alice using SET ROLE. Does it work? Set up ALTER DEFAULT PRIVILEGES so that future tables are covered automatically, then drop and recreate courses and verify.

9. Implement the complete four-step privilege setup from this chapter against the university database. Verify that a member of reporting can connect and query tables but cannot create or modify objects.