# Chapter 9: Identity & Access Control ## 9.0 - Access Control (The Bouncers and the VIP List) <img src="assets/arch_access_overview.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> In the initial stages of a system's life, security is often handled on the honor system. We assume that any process connecting to the backend is permitted to read any table, and any admin possesses total authority over the schema. ### What You'll Learn - The two-layer architecture: **Authentication** (`pg_hba.conf`) vs. **Authorization** (catalog ACLs) - How **Roles** and **Membership** inheritance create flexible permission trees - How **Row-Level Security** injects visibility predicates directly into execution plans - How **Security Definers** enable safe privilege escalation without permanent grants In a production environment, this is a significant architectural hazard. The database engine must ensure that sensitive data remains shielded from arbitrary access, and the system must prevent standard processes from altering data belonging to other operational owners. > [!NOTE] > **Technical Secret: The Front Gate vs. The Room Keys** > PostgreSQL security is built on two distinct architectural layers: **Authentication** (identity validation) and **Authorization** (resource permissioning). While the two are often conflated, understanding the boundary between them is critical for debugging "Permission Denied" errors and designing secure distributed systems. ### The Axis of Trust 1. **Authentication (AuthN): The Front Gate (`pg_hba.conf`)** The mechanism by which Postgres verifies that you are who you say you are. This happens at the very start of the connection lifecycle, governed by the **Host-Based Authentication** file. Whether you use SCRAM passwords, GSSAPI, or certificates, the goal of AuthN is to assign an **Identity** to your database backend process. 2. **Authorization (AuthZ): The Catalog Check** The ongoing process of checking if your assigned Identity has the required permissions to perform a specific action (e.g., `SELECT` from a table, `EXECUTE` a function). Authorization is enforced by the **Query Planner** and **Executor**, referencing the system catalogs: - **`pg_authid`**: The master vault of role attributes (see **[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**). - **`pg_class`**: Where table-level permissions (ACLs) live (see **[[Manuscript/02 - Physical Storage & MVCC/2.4 - Relation (The Table)|2.4 Relation]]**). - **`pg_namespace`**: The registry for schema-level access (see **[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**). ### Identity as a Resource In Postgres, everything is a **Role**. A "User" is simply a Role with the `LOGIN` attribute. A "Group" is simply a Role that possesses other Roles as members. By separating identity from permissions, Postgres allows for an incredibly flexible, inheritance-based security model. You can define a single `readonly_viewer` role, grant it specific permissions on your tables, and then grant that role to dozens of individual human users. When you update the permissions on the group, every member inherits the change instantly. ### The Principle of Least Privilege The goal of this chapter is to guide you through the process of hardening your "Security Posture." We will move from the coarse-grained permissions of the **Superuser** toward a more surgical, policy-driven model: - **Roles & Membership**: Building an inheritance tree of responsibility. - **Default Privileges**: Ensuring that new resources are born secure. - **Row-Level Security**: Injecting visibility rules directly into the query execution plan. - **Security Definers**: Safely delegating authority without elevating permanent status. By the end of this chapter, you will understand that security in Postgres is not a "Lock on the Door," but a pervasive, catalog-driven framework that governs every single byte retrieved by Postgres. --- ## 9.1 - Roles & Privileges (The Name Tags) <img src="assets/arch_access_nametags.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Postgres uses a unified **Role** system to manage authentication and authorization. Unlike systems that distinguish between users and groups, Postgres treats both as instances of the same entity. A Role is an identity identifier. It can be configured with a password and the ability to log in (acting as a "user"), or it can serve as a container of privileges (acting as a "group"). The behavior is determined by the attributes assigned to the role. ### Privilege Inheritance When you `GRANT` one role to another, the recipient inherits the privileges of the granted role. If a `cafe_manager` is granted the `cafe_waiter` role, the manager automatically acquires all permissions assigned to the waiter. Postgres's access controller then permits the manager to perform actions on the waiter's behalf. **Inheritance behavior**: - **`INHERIT` (default)**: Privileges flow to the member role automatically. The manager possesses the waiter's privileges at all times. - **`NOINHERIT`**: The manager must explicitly switch their active identity using `SET ROLE cafe_waiter` to use the waiter's privileges. ```sql CREATE ROLE cafe_manager WITH LOGIN PASSWORD 'peanuts' CREATEROLE; CREATE ROLE cafe_waiter; GRANT cafe_waiter TO cafe_manager; ``` > [!TIP] > **Delegating Authority**: Use `WITH ADMIN OPTION` when granting a role to allow the recipient to further distribute that membership to others. This enables decentralized role management. ### Identity Storage: `pg_authid` and `pg_roles` Role metadata is managed through two system catalogs: - **`pg_authid`**: The primary table storing all role information, including hashed passwords. Access is restricted to superusers. - **`pg_roles`**: A sanitized view of `pg_authid` that hides passwords and exposes public attributes. This view is queried by `psql` when running the `\du` command. > [!NOTE] > **The OID Secret**: as covered in [[Manuscript/02 - Physical Storage & MVCC/2.1 - Data Types (The Ingredient Labels)|Chapter 2.1]], every object in Postgres is internally identified by an **OID** — a 4-byte integer. A role is just a number. A table is just a number. Access control is just a list of `(role_oid, privilege_bits, object_oid)` tuples in `pg_class.relacl`. Each role possesses specific **Attributes**. In this example, `cafe_manager` has `rolcanlogin = true` (enabling connection) and `rolcreaterole = true` (allowing the creation of other roles). The `cafe_waiter` role is not login-capable and serves only as a container for permissions. > [!CAUTION] > **Superuser Privileges**: The `rolsuper` attribute bypasses all permission checks in the engine. A superuser can access any data, modify any configuration, and drop any object. This attribute should be restricted to recovery and administrative tasks, as its compromise results in complete control over the cluster. ### Object Privileges (`GRANT`) A logged-in role still cannot read tables it has not been explicitly granted access to. Every relation has its own access control list (ACL), modified via `GRANT` and `REVOKE`: ```sql GRANT SELECT ON orders TO cafe_waiter; ``` Every query Postgres runs checks `pg_class.relacl` to confirm the executing role has the matching privilege bit. ### Anatomy of an ACL Item The ACL on a table is stored as an array of `aclitem` values that look like this: `cafe_waiter=r/postgres`. Each item has three parts: ```text ┌──────────────────────────────────────────────────────────┐ │ aclitem │ ├─────────────────┬──────────────────┬─────────────────────┤ │ GRANTEE │ PRIVILEGES │ GRANTOR │ │ (Who gets it) │ (Which bits) │ (Who gave it) │ ├─────────────────┼──────────────────┼─────────────────────┤ │ cafe_waiter │ = r │ / postgres │ └─────────────────┴──────────────────┴─────────────────────┘ ``` - **Grantee**: the role receiving the privilege. - **Privileges**: a string of single-letter codes (one per privilege bit). - **Grantor**: the role that performed the `GRANT`. > [!WARNING] > **The PUBLIC Pseudo-Role**: If the grantee field in an ACL is empty (e.g., `=r/postgres`), the privilege is granted to **`PUBLIC`**. This means every existing and future role has access to the object. Ensure sensitive tables are revoked from `PUBLIC` to prevent unauthorized access. ### Privilege Codes Postgres encodes privileges as single letters in the ACL string: | Code | SQL Privilege | Meaning | | :--- | :--- | :--- | | **`r`** | `SELECT` | Read rows from the table. | | **`a`** | `INSERT` | Append new rows. | | **`w`** | `UPDATE` | Modify existing rows. | | **`d`** | `DELETE` | Remove rows. | | **`D`** | `TRUNCATE` | Empty the entire table at once. | | **`x`** | `REFERENCES` | Create a foreign key against this table. | | **`t`** | `TRIGGER` | Define triggers on this table. | | **`X`** | `EXECUTE` | Call a function or procedure. | | **`U`** | `USAGE` | Enter a schema, sequence, or other namespace. | So `{postgres=arwdDxt/postgres}` reads as "the `postgres` role holds every available privilege on this object, granted by itself" — typically the row Postgres writes when the owner first creates the table. > [!IMPORTANT] > **Schema-Level Permissions**: Granting `SELECT` on a table is insufficient if the role lacks `USAGE` on the schema. Postgres checks for schema-level access before evaluating object-level privileges. Both are required for a query to succeed. ### Membership Tracking: `pg_auth_members` The relationships between roles are recorded in the **`pg_auth_members`** catalog. This table tracks the membership graph, identifying which roles belong to which groups and who granted the membership. ```sql SELECT m.rolname AS member, g.rolname AS group_role, admin_option FROM pg_auth_members am JOIN pg_roles m ON am.member = m.oid JOIN pg_roles g ON am.roleid = g.oid; ``` Mastering role hierarchies enables a scalable, group-based security model. It allows the engine to enforce access control efficiently without requiring redundant individual permissions. --- ## 9.2 - Default Privileges (The Manager's Orders) <img src="assets/arch_access_default_privs.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> The biggest headache in Postgres security is the **New Object Problem**. In **[[Manuscript/02 - Physical Storage & MVCC/2.0 - Storage Foundations (The Building Blocks of Storage)|Chapter 2]]**, we saw that data is stored in relations (tables). When a process creates a new table, **it becomes the owner, and it is the ONLY entity with access by default**. Even if you previously granted a group `SELECT ON ALL TABLES IN SCHEMA public`, that grant only applied to the objects that existed *at that specific moment*. If a migration creates a new recipe book tomorrow, other roles will receive a "Permission Denied" error if they try to access it. **The Intuition**: Think of Default Privileges as a **Standing Order** for a personal assistant. The assistant only knows what to do when *their specific boss* (the Creator) produces a new document. They have no instructions for what to do when a different office (a different Role) brings in a table! **The Standing Order**: Default Privileges are **Personal Automation**. They do not pull permissions into the schema automatically; they are triggered only when a specific role performs a specific DDL action. This is managed through the `pg_default_acl` system catalog. > [!TIP] > **The Owner's Trap** > While a standing order automates the granting of keys, the **Owner** (the creator) always holds the master lock. They can explicitly `REVOKE` access later or drop the entire relation, regardless of what the default privileges specified. The creator remains the ultimate authority over their own objects. ### The Standing Order To fix this, you don't grant privileges on the ledger. You give a **Standing Order** to Postgres, known as `ALTER DEFAULT PRIVILEGES`. ```sql ALTER DEFAULT PRIVILEGES FOR ROLE cafe_manager IN SCHEMA public GRANT SELECT ON TABLES TO cafe_waiter; ``` This tells Postgres: *"Listen closely. Every time the `cafe_manager` creates a new ledger in the public room, I want you to immediately cut a 'read' key and hand it to `cafe_waiter`."* > [!NOTE] > **Technical Secret: The Implicit Creator Gotcha** > If you don't specify `FOR ROLE`, the database engine assumes the standing order is for **you**. If you are the one running the migrations (`cafe_manager`), that's fine. But if you are logged in as a `service_role` and someone else runs the migrations, the order will never trigger! Always be explicit about who the "Creator" is. One of the most common security failures in a growing database is the "New Object Gap." When you create a new table, it is owned by the role that created it. By default, no other roles (except superusers) have access to it. If you have a team of developers creating tables, you will constantly be running manual `GRANT` commands to keep your permissions in sync. To solve this, PostgreSQL provides **Default Privileges**. ### The Mechanism: `pg_default_acl` Default privileges allow you to define a set of access rules that will be applied automatically to any **future** objects created within a specific schema. These rules are stored in the `pg_default_acl` system catalog. Unlike standard `GRANT` commands which modify the ACL of an existing object, `ALTER DEFAULT PRIVILEGES` modifies the *blueprint* for new objects. ```sql -- Enforce that any new tables created by 'postgres' in the 'public' schema -- are automatically readable by the 'cafe_admin' group. ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO cafe_admin; ``` ### The Critical "Gotcha": The Creator Constraint Default privileges in Postgres possess a significant architectural limitation: **They are tied to the creator, not the schema.** If you run the command above for the `postgres` role, it will works perfectly—*as long as the `postgres` role is the one creating the tables*. If another user, `head_chef`, creates a table in the same schema, the default privileges you set for `postgres` **will not apply**. The `head_chef` is a different creator, and they have their own (likely empty) set of default ACLs. > [!WARNING] > **The Inheritance Fallacy**: Even if `head_chef` is a member of the `postgres` role, the default privileges of the group do not automatically apply to objects created by the member. Default privileges must be explicitly defined for every role that is expected to create objects, or you must ensure all creators use `SET ROLE` to become the authorized creator before running DDL. ### Auditing Defaults You can inspect your current default blueprints using the `\ddp` command in `psql` or by querying the catalog directly: ```sql -- View all default ACLs in the cluster SELECT defaclrole::regrole AS creator, defaclnamespace::regnamespace AS schema, defaclobjtype AS type, defaclacl AS acl FROM pg_default_acl; ``` - **`defaclrole`**: The role whose creations are being modified. - **`defaclnamespace`**: The schema filter (Zero means "all schemas"). - **`defaclobjtype`**: `r` for tables, `f` for functions, `S` for sequences. ### Strategy: The "Secure by Default" Pattern For a robust production environment, follow this sequence: 1. **Define a NOLOGIN Admin Role** (`cafe_mgr`) that will own all schemas. 2. **Define a NOLOGIN Read Role** (`cafe_reader`) for reporting. 3. **Establish Defaults** for the Admin Role so it always grants access to the Read Role. 4. **Enforce DDL execution** through the Admin Role only. By mastering default privileges, you ensure that security is an inherent property of your architecture, rather than a manual checklist that can be forgotten during a busy deployment. --- ## 9.3 - Row-Level Security (The VIP List) <img src="assets/arch_bouncer_rhino.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Table-level permissions are binary: a role either possesses `SELECT` privileges on a table or it does not. In multi-tenant environments where users share a single table but must be restricted to specific rows, a more granular mechanism is required. **Row-Level Security (RLS)** provides this granularity. It is a declarative filtering policy attached to a table that the engine evaluates for every query. RLS ensures that rows not satisfying the policy are silently excluded from the result set. Because this check occurs within the query planner, it cannot be bypassed by application-level logic. To see why this matters, imagine a `secrets` table that the Manager and the Waiter both have read access to: ```sql CREATE TABLE secrets (id int, owner name, secret text); INSERT INTO secrets VALUES (1, 'cafe_manager', 'The safe code is 555-1234'); INSERT INTO secrets VALUES (2, 'cafe_waiter', 'The Planner burned the toast'); GRANT SELECT ON secrets TO PUBLIC; ``` Without RLS, both roles see both rows. The waiter can read the safe code; the manager learns who burned the toast. Granular per-row access requires a different mechanism. ### The Mechanism: Query Rewriting RLS is implemented as a **Query Rewriting** transformation within the **[[Manuscript/04 - Query Planning & Execution/4.1 - Query Planner (The Blueprint of Execution)|Query Planner]]**. It is not a secondary screening process after data retrieval. When RLS is enabled, the planner retrieves policy expressions from the `pg_policy` catalog and injects them into the query tree. These expressions serve as additional `WHERE` clauses for read operations or validation predicates for write operations. ```sql -- Enabling RLS on a table ALTER TABLE secrets ENABLE ROW LEVEL SECURITY; -- Creating a policy: Users can only see their own records CREATE POLICY read_own ON secrets FOR SELECT USING (owner = current_user); ``` When a user runs `SELECT * FROM secrets`, the Planner internally rewrites the query to: `SELECT * FROM secrets WHERE (owner = current_user)`. ### Visibility vs. Validation (USING vs. WITH CHECK) Postgres distinguishes between existing data and new data being introduced to the system: - **`USING`**: Defines row visibility. If a row does not satisfy this expression, it is excluded from the result set. The engine treats the row as if it does not exist. - **`WITH CHECK`**: Validates new or modified data. If an `INSERT` or `UPDATE` operation produces a row that violates this expression, the operation fails with an error. > [!IMPORTANT] > **Silencing the Errors**: RLS is fundamentally a **Visibility** tool. When a user tries to read a row they don't own, Postgres does not throw an error; it simply returns an empty result set. This prevents "Identity Probing"—a side-channel attack where a hacker could guess the existence of IDs by watching for error messages. ### Performance Implications As a query rewriting mechanism, RLS introduces computational overhead: 1. **Plan Expansion**: Every policy is appended as a `Filter` node in the execution plan. Complex policies increase the work required by the planner to optimize the query. 2. **Index Suppression**: If a policy uses volatile functions or expressions the planner cannot optimize, it may revert to a **Sequential Scan**, bypassing indexes. 3. **Subquery Overhead**: Policies that perform lookups in other tables (e.g., `USING (user_id IN (SELECT id FROM staff))`) can significantly increase latency as the subquery may be executed for every row scanned. > [!TIP] > **Keep Policies Local**: To avoid the performance tax, keep your RLS policies as simple as possible. Use simple column-to-user comparisons (`owner = current_user`) whenever possible to ensure the Planner can still utilize indexes effectively. ### High-Performance Security: The Security Barrier Because RLS expressions are injected into the query, they must be evaluated frequently. To optimize this, the Planner often treats RLS-enabled tables as **`SECURITY_BARRIER`** views. This ensures that any user-provided functions or filters (which might contain malicious logging or side-effects) are only executed **after** the RLS security filters have been applied. Postgres creates a logical wall between the "Trusted" security filters and the "Untrusted" user filters. ### Auditing Policies Policy definitions are stored in the `pg_policy` system catalog. You can inspect the visceral, internal representation of these rules: ```sql -- View policies and their internal SQL expressions SELECT polname, polcmd, polroles, polqual FROM pg_policy; ``` - **`polcmd`**: The command type (`r` for SELECT, `a` for INSERT, `w` for UPDATE, `d` for DELETE). - **`polqual`**: The `USING` expression. - **`polwithcheck`**: The `WITH CHECK` expression. By implementing RLS, security logic is moved from the application layer to the database engine. This ensures that access control is a deterministic property of the data, enforced consistently across all applications and sessions. --- ## 9.4 - Security Definers (The Manager's Override) <img src="assets/arch_access_security_definer_v2.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> In a highly secure environment, you often face the "Access Paradox": a low-privileged role needs to perform a task that requires high-level permissions, but you do not want to grant that role permanent administrative rights. To solve this, PostgreSQL provides **`SECURITY DEFINER`** functions. ### The Mechanism: Identity Context Switching By default, functions in Postgres are defined as **`SECURITY INVOKER`**. This means the function executes with the privileges of the user who calls it. A **`SECURITY DEFINER`** function, however, executes with the privileges of the role that **created** the function. When a lower-privileged role calls a `SECURITY DEFINER` function: 1. **Identity Context Switch**: The database engine performs a literal switch of the `SessionUser` and `CurrentUser` variables. 2. **Elevation**: The effective user identity is temporarily elevated to that of the function's creator (the Definer). 3. **Privileged Execution**: The function body executes with elevated permissions (e.g., bypassing **[[Manuscript/09 - Identity & Access Control/9.3 - Row-Level Security (The VIP List)|RLS]]** or reading private relations). 4. **Restoration**: Once the function returns, the identity is restored to the original invoker. > [!CAUTION] > **Privilege Escalation Risk**: `SECURITY DEFINER` is a deliberate mechanism for **Privilege Escalation**. You are allowing a role to assume the authority of another user for a specific code path. This is a powerful tool for building secure APIs, but it represents a significant security hazard if the function body is not rigorously vetted. ### The Trojan Horse (The `search_path` Vulnerability) The most dangerous vulnerability in `SECURITY DEFINER` functions is a **Search Path Attack**. If the function references a table by an unqualified name, Postgres resolves that name through the caller's `search_path` at execution time — *not* the definer's. An attacker who controls a schema in front of `public` on the search path can substitute a malicious object that the function then reads or writes with elevated privileges. #### A Worked Example Suppose the cafe owner ships an audited helper function for waitstaff to mark an order served: ```sql -- Created by the cafe owner (a superuser-equivalent) CREATE FUNCTION audit.mark_served(order_id BIGINT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $ BEGIN -- Note: 'orders' is unqualified UPDATE orders SET status = 'Served' WHERE id = order_id; -- And 'audit_log' is also unqualified INSERT INTO audit_log(actor, action, target_id) VALUES (current_user, 'mark_served', order_id); END; $; GRANT EXECUTE ON FUNCTION audit.mark_served(BIGINT) TO cafe_waiter; ``` The function looks tight: only waitstaff can call it, it uses `SECURITY DEFINER` to update orders the waiter cannot directly modify, and every call is logged. The hole is that `orders` and `audit_log` are unqualified. A motivated waiter (or any role with `CREATE` on a schema that appears earlier in the resolution path) can install a shadow: ```sql -- Run as cafe_waiter SET search_path = my_schema, public; CREATE TABLE my_schema.orders (id BIGINT PRIMARY KEY, status TEXT); CREATE RULE bait AS ON UPDATE TO my_schema.orders DO INSTEAD -- Side effect that runs with definer privileges GRANT pg_read_server_files TO cafe_waiter; ``` Now when the waiter calls `audit.mark_served(123)`, the function runs as the cafe owner, resolves `orders` to `my_schema.orders` (because the waiter's `search_path` puts `my_schema` first), and triggers the rule — granting the waiter the right to read arbitrary files on the server, courtesy of the definer's elevated identity. #### Hardening the Vault Door Three rules close this attack surface: 1. **Pin `search_path` on the function itself**. The `SET` clause runs *before* the function body and overrides whatever the caller had set: ```sql ALTER FUNCTION audit.mark_served(BIGINT) SET search_path = pg_catalog, public, pg_temp; ``` Putting `pg_catalog` first prevents an attacker from shadowing built-in operators and types as well. 2. **Schema-qualify every object reference inside the body**. `UPDATE public.orders` and `INSERT INTO audit.audit_log` cannot be hijacked by `search_path` games at all. 3. **Revoke `PUBLIC` from the function and from `public`**: ```sql -- Functions are created with EXECUTE for PUBLIC by default REVOKE EXECUTE ON FUNCTION audit.mark_served(BIGINT) FROM PUBLIC; -- The public schema is writable by every login role on PG <= 14 REVOKE ALL ON SCHEMA public FROM PUBLIC; ``` Together these constraints reduce the trust boundary to exactly what the function body says: definer-privileged code reading definer-pinned objects, with no opportunity for the caller to redirect a name lookup. ### Auditing Your Posture Before concluding our exploration of Access Control, perform a final audit of the cluster's security state using these diagnostic axes: 1. **Identity Entropy**: Are you using dedicated Roles for services and humans, or are you over-relying on the `postgres` superuser? 2. **The Least Privilege Check**: Does every role have exactly the `GRANT` keys required for its specific schema (**[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**) and table (**[[Manuscript/02 - Physical Storage & MVCC/2.4 - Relation (The Table)|2.4 Relation]]**)? 3. **The "Secure by Default" Check**: Are **[[Manuscript/09 - Identity & Access Control/9.2 - Default Privileges (The Manager's Orders)|Default Privileges]]** configured so that new data is born within the correct security boundaries? 4. **The Visibility Audit**: Is **[[Manuscript/09 - Identity & Access Control/9.3 - Row-Level Security (The VIP List)|Row-Level Security]]** active on multi-tenant tables, and have you verified that policies correctly exclude rows using `USING` clauses? 5. **The Escalation Audit**: Have you vetted every `SECURITY DEFINER` function for `search_path` safety and unintended side-effects? By mastering the catalogs and the identity graph, you transition from a passive consumer of database security to an architect who builds systems that are **Secure by Construction**.