# 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 ### The Axis of Trust Postgres splits security into two distinct architectural layers: **Authentication** (identity validation) and **Authorization** (resource permissioning). 1. **Authentication (AuthN): The Front Gate (`pg_hba.conf`)** Verifies *who* you are at the start of the connection lifecycle. Whether using SCRAM passwords, GSSAPI, or client certificates, AuthN's sole goal is to map your connection to a specific database **Role**. 2. **Authorization (AuthZ): The Room Keys (Catalogs)** The ongoing verification of *what* you can do. Every query is checked by the planner and executor against the system catalogs to see if your Role OID possesses the necessary privilege bits: - **`pg_authid`**: Master role attributes (see **[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|9.1 Roles & Privileges]]**). - **`pg_class`**: Table-level Access Control Lists (see **[[Manuscript/02 - Physical Storage & MVCC/2.4 - Relation (The Table)|2.4 Relation]]**). - **`pg_namespace`**: Schema-level access lists (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. > [!NOTE] The Click > **Concept**: Postgres has no concept of "users" or "groups." There are only **Roles**. > **Payoff**: A role is a single primitive. If you give it `LOGIN` capabilities, it acts as a user. If you grant it to other roles, it acts as a group. By unifying these under a single concept, the privilege checker only has to traverse a single directed acyclic graph of roles (`pg_auth_members`) to resolve access rights, making authorization fast and clean. ### 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. **Role inheritance chain** (membership in `pg_auth_members`): - `postgres` (superuser, grantor) - grants **`cafe_waiter`** → `SELECT` on `orders` - grants membership **`cafe_waiter` → `cafe_manager`** - **`cafe_manager`** (login role) - inherits waiter's table privileges via **`INHERIT`** - can `SET ROLE cafe_waiter` if configured with **`NOINHERIT`** ```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 (Knicks, knacks, bits, and bobs)|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. --- ### 🧪 Manipulation Lab: Build a SaaS Tenant Model To see how Row-Level Security (RLS) guarantees data isolation in a multi-tenant SaaS application, we will enable RLS, construct a policy that reads a session variable, and verify isolation using a non-superuser role. #### The Setup Connect to the database and run the following statements as the superuser: ```sql CREATE TABLE tenant_data ( tenant_id TEXT, data TEXT ); -- 1. Enable RLS ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY; -- 2. Define a policy that matches tenant_id to a session parameter CREATE POLICY tenant_isolation_policy ON tenant_data USING (tenant_id = current_setting('app.current_tenant_id', true)); -- Insert data for two separate tenants INSERT INTO tenant_data VALUES ('tenant_alpha', 'Alpha Secret Data'), ('tenant_beta', 'Beta Secret Data'); -- 3. Create a non-superuser application role and grant access CREATE ROLE cafe_app NOLOGIN; GRANT SELECT ON tenant_data TO cafe_app; ``` #### The Task 1. Switch your session identity to the application role: ```sql SET ROLE cafe_app; ``` 2. Query the table without setting a tenant session ID: ```sql SELECT count(*) FROM tenant_data; ``` Output: ``` count ------- 0 ``` Because the session variable `app.current_tenant_id` is empty, no rows match the policy, and Postgres returns an empty result set. 3. Set the tenant ID in the session and query again: ```sql SET app.current_tenant_id = 'tenant_alpha'; SELECT data FROM tenant_data; ``` Output: ``` data ------------------- Alpha Secret Data ``` #### The Observation Notice that Postgres now returns only the record matching `'tenant_alpha'`. The query planner appended the RLS policy filter to the query automatically. ```sql -- Clean up RESET ROLE; DROP TABLE tenant_data; DROP ROLE cafe_app; ``` > [!WARNING] > **The Superuser Loophole**: If you ran the query above as the default `postgres` superuser, you would see *both* records, bypassing RLS. Postgres superusers always bypass RLS check maps. When deploying RLS in production, you must ensure your application connects using a dedicated, non-superuser role (like `cafe_app`). #### The Payoff By enforcing tenant isolation at the database layer using RLS, you prevent "cross-tenant leakage" bugs in the application layer. Even if a developer forgets to add a `WHERE tenant_id = ...` filter in their API query, Postgres guarantees that the session context filters out other tenants' data at the physical engine level. --- ## 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;" /> ### The Access Paradox Imagine you have a restricted database user role named `cafe_waiter`. If they run a direct command like `UPDATE orders SET status = 'Served' WHERE id = 123;`, the query fails instantly with a **`Permission Denied`** error. The waiter is forbidden from altering the order ledger. Yet, when the waiter executes a specific database function—`SELECT audit.mark_served(123);`—the order status is successfully updated. The waiter has modified the forbidden table, and the engine accepted the write without raising any security alerts. How does Postgres allow a restricted user to bypass table-level access rules for a specific operation without permanently granting them administrative privileges? > [!IMPORTANT] Prediction Checkpoint: Bypassing Access Controls > How does a function allow a restricted caller to edit a forbidden table? What mechanism shifts the privilege context during function execution, and does it run as the caller or another role? Pause and formulate a guess. You might expect that database functions simply run with "superuser" bypass authority implicitly, or that they check the caller's privileges but make exceptions for specific lines of code. Neither is true. If all functions bypassed security, database functions would represent a massive, unmanageable security hole, and checking line-by-line exceptions would be slow and complex. Instead, Postgres resolves this using the **`SECURITY DEFINER`** attribute during function creation. By default, database functions are created as **`SECURITY INVOKER`**. This means that when a user calls them, the function executes using the privileges of the caller (the invoker). If the caller lacks permission on the target table, the function crashes. However, if a function is declared as **`SECURITY DEFINER`** (think of it as the **Manager's Override**): 1. **Identity Context Switch**: When a user executes the function, Postgres literally swaps the `CurrentUser` variable in the active session. 2. **Definer Elevation**: The function body executes with the privileges of the role that **created** the function (the Definer), not the user calling it. 3. **Restoration**: Once the function finishes and returns, Postgres restores the identity back to the original caller. This allows the Cafe Owner (a superuser-equivalent Definer) to compile a tightly controlled API function, grant `cafe_waiter` permission to *execute* the function, and let the waiter perform the update under elevated privileges without ever granting them direct access to the underlying table. `SECURITY DEFINER` enables secure API boundaries inside the database, allowing restricted users to perform highly audited actions on private tables. However, this delegation represents a serious **Privilege Escalation** vector. If the function is not designed with strict security rules, it can be hijacked to execute arbitrary code under the definer's identity. ### 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: reading a sensitive file with definer privileges SELECT pg_read_file('/etc/passwd'); ``` 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**. --- ## 9.5 - DDL Audit Logging (The Paper Trail) <img src="assets/arch_ddl_audit_log_v1.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Every mechanism in this chapter so far has answered the same question: *who is allowed to do what?* Roles define identity. Privileges define capability. RLS defines visibility. Security Definers define delegation. But none of them answer the question that surfaces at 3 AM during an incident: *who actually did what, and when?* DDL statements — `CREATE`, `ALTER`, `DROP`, `GRANT`, `REVOKE` — are the commands that reshape the schema itself. A misplaced `DROP TABLE` or a silent `ALTER COLUMN` can cause cascading application failures, and if no audit record exists, the forensic trail is cold. Postgres provides three mechanisms for capturing DDL activity, each with a different tradeoff between simplicity and queryability. ### The Blunt Instrument: `log_statement` The fastest path to DDL visibility is the `log_statement` server parameter. It accepts four values: `none`, `ddl`, `mod`, and `all`. Setting it to `ddl` causes the server to emit a log line for every DDL statement executed by any session. ```sql -- Enable DDL logging (requires reload, not restart) ALTER SYSTEM SET log_statement = 'ddl'; SELECT pg_reload_conf(); ``` After this reload, every `CREATE TABLE`, `ALTER INDEX`, `DROP FUNCTION`, `GRANT`, and `REVOKE` is written to the server log — the same destination configured by `log_destination` (typically `stderr` or `csvlog`). > [!TIP] > **Enrich the log line.** By default, log entries contain the statement text but limited session context. Add `%u` (user), `%d` (database), and `%r` (remote host) to `log_line_prefix` to make each entry forensically useful: > ``` > log_line_prefix = '%m [%p] %u@%d ' > ``` This approach has a critical limitation: the output is a flat text stream, not a relational table. You cannot `SELECT` from it, join it, or filter it with SQL. Log rotation policies may silently discard old entries. For development and low-stakes environments, `log_statement = 'ddl'` is sufficient. For compliance, it is not. ### The Compliance-Grade Extension: `pgaudit` The `pgaudit` extension closes the gap between "we log things" and "we can prove we logged things." It is a `shared_preload_libraries` module that hooks into the executor and emits structured audit records to the server log with a well-defined, parseable format. ``` -- postgresql.conf shared_preload_libraries = 'pgaudit' pgaudit.log = 'DDL' ``` After a server restart, pgaudit intercepts DDL execution and emits records containing: audit type (`SESSION` or `OBJECT`), statement class (`DDL`, `READ`, `WRITE`, `ROLE`, `FUNCTION`), command tag (`CREATE TABLE`, `ALTER INDEX`), object type and name, the full statement text, and parameter values. pgaudit operates in two modes: 1. **Session-level** (`pgaudit.log`): Logs all statements matching the configured classes for every session. Broad and simple. 2. **Object-level** (`pgaudit.role`): Assigns a dedicated audit role. Only statements affecting objects where that role has been granted privileges are logged. This allows surgical precision — audit the `orders` table without drowning in noise from `pg_temp` operations. > [!WARNING] > **pgaudit is not a default extension.** It must be compiled and installed separately, or be available in your distribution's extension catalog. Many managed Postgres services (RDS, Cloud SQL, Supabase) include it; vanilla Docker images typically do not. Check `SELECT * FROM pg_available_extensions WHERE name = 'pgaudit';` before relying on it. Even with pgaudit, the output destination remains the server log. The records are structured and machine-parseable, but they are not queryable with SQL until an external pipeline (e.g., Fluentd, CloudWatch, Datadog) ingests them into a searchable store. ### The Relational Approach: Event Triggers Postgres provides a native mechanism for capturing DDL events and writing them to an ordinary table: the **Event Trigger**. Unlike regular triggers (which fire on DML against a specific table), event triggers fire on DDL commands across the entire database. An event trigger hooks into one of four firing points: | Event | Fires | Available Context | | :--- | :--- | :--- | | `ddl_command_start` | Before the DDL executes | Command tag only | | `ddl_command_end` | After successful DDL | `pg_event_trigger_ddl_commands()` — full object metadata | | `sql_drop` | After a DROP completes | `pg_event_trigger_dropped_objects()` — cascade details | | `table_rewrite` | When ALTER TABLE rewrites data | `pg_event_trigger_table_rewrite_oid()` | The `ddl_command_end` event is the most useful for auditing: it fires only after a successful DDL, and the `pg_event_trigger_ddl_commands()` function exposes the command tag, object type, schema name, and fully qualified object identity. Combined with `current_user` and `current_query()`, you have every field needed for a compliance record — stored in a table you own. > [!NOTE] > **Why not `ddl_command_start`?** The start event fires before the command executes, so `pg_event_trigger_ddl_commands()` is not yet populated. It is useful for *preventing* DDL (by raising an exception), but not for *recording* it. The key architectural advantage of event triggers is that the audit data lives inside the database itself. It is queryable, joinable, and subject to the same backup and replication policies as every other table. No external log pipeline required. ### 🧪 Manipulation Lab: Build a DDL Audit Log This lab constructs a self-contained DDL audit system using an event trigger, a drop trigger, and a relational log table. #### The Setup Connect to the database as the superuser and create the audit infrastructure: ```sql -- 1. Create a dedicated audit schema CREATE SCHEMA IF NOT EXISTS audit; -- 2. Create the log table CREATE TABLE audit.ddl_log ( id BIGSERIAL PRIMARY KEY, logged_at TIMESTAMPTZ NOT NULL DEFAULT now(), actor TEXT NOT NULL DEFAULT current_user, command_tag TEXT NOT NULL, object_type TEXT, schema_name TEXT, object_name TEXT, statement TEXT ); -- 3. Create the event trigger function for CREATE/ALTER CREATE OR REPLACE FUNCTION audit.log_ddl_event() RETURNS event_trigger LANGUAGE plpgsql AS $ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP INSERT INTO audit.ddl_log(command_tag, object_type, schema_name, object_name, statement) VALUES ( r.command_tag, r.object_type, r.schema_name, r.object_identity, current_query() ); END LOOP; END; $; -- 4. Create a separate function for DROP events CREATE OR REPLACE FUNCTION audit.log_drop_event() RETURNS event_trigger LANGUAGE plpgsql AS $ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP INSERT INTO audit.ddl_log(command_tag, object_type, schema_name, object_name, statement) VALUES (tg_tag, r.object_type, r.schema_name, r.object_identity, current_query()); END LOOP; END; $; -- 5. Register both event triggers CREATE EVENT TRIGGER audit_all_ddl ON ddl_command_end EXECUTE FUNCTION audit.log_ddl_event(); CREATE EVENT TRIGGER audit_drops ON sql_drop EXECUTE FUNCTION audit.log_drop_event(); ``` #### The Task Fire a sequence of DDL statements that simulate a typical schema evolution: ```sql CREATE TABLE public.test_audit_target (id INT, label TEXT); ALTER TABLE public.test_audit_target ADD COLUMN created_at TIMESTAMPTZ; CREATE INDEX test_audit_idx ON public.test_audit_target(id); DROP INDEX public.test_audit_idx; DROP TABLE public.test_audit_target; ``` Now query the audit log: ```sql SELECT id, logged_at::TIME(0) AS logged_at, actor, command_tag, object_type, object_name FROM audit.ddl_log ORDER BY id; ``` Output: ``` id | logged_at | actor | command_tag | object_type | object_name ----+-----------+----------+-----------------+-------------+------------------------------- 1 | 07:40:12 | postgres | CREATE TABLE | table | public.test_audit_target 2 | 07:40:12 | postgres | ALTER TABLE | table | public.test_audit_target 3 | 07:40:12 | postgres | CREATE INDEX | index | public.test_audit_idx 4 | 07:40:27 | postgres | DROP INDEX | index | public.test_audit_idx 5 | 07:40:27 | postgres | DROP TABLE | table | public.test_audit_target 6 | 07:40:27 | postgres | DROP TABLE | type | public.test_audit_target 7 | 07:40:27 | postgres | DROP TABLE | type | public.test_audit_target[] 8 | 07:40:27 | postgres | DROP TABLE | toast table | pg_toast.pg_toast_17159 ``` #### The Observation Several things are worth noting: 1. **Every DDL is captured.** `CREATE TABLE`, `ALTER TABLE`, `CREATE INDEX`, `DROP INDEX`, and `DROP TABLE` all appear as discrete entries with the acting role and timestamp. 2. **Drops cascade.** The `DROP TABLE` statement produced four log entries: the table itself, its composite type, its array type, and its TOAST table. The `sql_drop` event exposes the full cascade — objects the user never explicitly named but which Postgres quietly destroyed. 3. **Two triggers, two events.** `ddl_command_end` captures constructive DDL (CREATE, ALTER). `sql_drop` captures destructive DDL (DROP). You need both for complete coverage. ```sql -- Clean up DROP EVENT TRIGGER audit_all_ddl; DROP EVENT TRIGGER audit_drops; DROP TABLE audit.ddl_log; DROP FUNCTION audit.log_ddl_event(); DROP FUNCTION audit.log_drop_event(); DROP SCHEMA audit; ``` #### The Payoff Event triggers transform DDL auditing from an external infrastructure problem into a database-native capability. The log table is subject to the same MVCC, backup, and replication guarantees as every other relation. You can query it with SQL, join it against `pg_roles` to enrich actor information, set up RLS policies to restrict who can read the audit trail, and even attach regular triggers to the log table itself for real-time alerting. For organizations that need to answer "who changed the schema, when, and why" — without standing up an external log aggregation pipeline — event triggers are the lowest-friction path to compliance-grade DDL auditing that Postgres offers out of the box. --- ## 9.6 - Summary: Permission Is Data Before this chapter, “permission denied” may have felt like a wall. Now it looks like a queryable fact. Roles are rows. Privileges are recorded relationships. Row-level policies are predicates. `SECURITY DEFINER` functions deliberately change execution context. Event triggers can turn schema changes into an audit trail. That changes how you debug access. You do not have to guess whether the problem is the user, the schema, the table, the default privilege, the RLS policy, or the function context. You can inspect each layer until the missing permission becomes visible. The achievement is auditability. You can now treat security as part of the database model rather than a separate fog around it. > [!NOTE] The Click > **Concept**: Access control is not magic. It is metadata with consequences. <div style="page-break-after: always;"></div>