5.2 Roles, Privileges & Object Ownership

5.2 Roles, Privileges & Object Ownership

[!NOTE] Historical Evolution: In early versions of PostgreSQL (prior to 8.1), user accounts and group accounts were distinct catalog entities managed via CREATE USER and CREATE GROUP. Since PostgreSQL 8.1, these concepts have been completely unified under the single concept of a ROLE. A user is simply a role with the LOGIN attribute, while a group is a role without LOGIN that contains member roles.

PostgreSQL employs a comprehensive, granular Discretionary Access Control (DAC) model, optionally extended by Row-Level Security (RLS) policies on individual tables. Understanding role attributes, object ownership, and privilege propagation is central to securing production PostgreSQL databases and excelling on the certification exam.

---

The Unified Role Model

A role is an entity that can own database objects and hold database privileges. Roles are cluster-global: they exist across all databases in the cluster and are defined in the shared system catalogs (pg_authid and the pg_roles system view).

-- CREATE USER is syntactic sugar for CREATE ROLE ... LOGIN
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecureSecret123!';
-- Exactly equivalent to:
CREATE USER app_user WITH PASSWORD 'SecureSecret123!';

-- Creating a group role (no LOGIN attribute by default)
CREATE ROLE sales_team;

System Catalogs for Roles

• pg_authid: The actual physical catalog table storing all role definitions and authentication secrets (e.g., scram-sha-256 password hashes). It is strictly protected and readable only by superusers.
• pg_roles: A publicly accessible system view that displays role metadata while masking the sensitive password hash column (rolpassword shows as ********).

---

Key Role Attributes

When defining or altering a role, administrators assign specific administrative attributes that govern system-level privileges:

CREATE ROLE dev_admin WITH
    LOGIN
    CREATEDB
    CREATEROLE
    INHERIT
    CONNECTION LIMIT 20
    PASSWORD 'StrongPassword#2026';

[!CAUTION] SUPERUSER Omnipotence: A role with SUPERUSER bypasses all access privilege checks inside the database. It can read, modify, or truncate any table, alter any function, and read sensitive files from the server filesystem via functions like pg_read_file(). Enterprise security standards dictate minimizing the use of superuser accounts in application connection strings.

---

Role Membership and Privilege Inheritance

PostgreSQL implements role hierarchies by granting one role to another:

-- Create group roles
CREATE ROLE readonly_analysts;
CREATE ROLE engineering_group;

-- Create individual login user
CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePassword!2026';

-- Grant group membership to alice
GRANT readonly_analysts TO alice;
GRANT engineering_group TO alice WITH ADMIN OPTION;

INHERIT vs. NOINHERIT

Privilege inheritance determines how a user exercises the permissions of their group roles:

1. INHERIT (Default):

   • Alice automatically possesses every permission granted to readonly_analysts (e.g., SELECT on sales tables) without executing any preparatory commands.
   • System-level role attributes (like CREATEDB, CREATEROLE, SUPERUSER) are never inherited; only object permissions (SELECT, INSERT, UPDATE, DELETE, USAGE) are inherited.

2. NOINHERIT:

   • Alice does not automatically possess group permissions.
   • To use the permissions granted to readonly_analysts, Alice must explicitly execute the SET ROLE command during her active session:

     SET ROLE readonly_analysts;
     -- Current session now acts with readonly_analysts privileges
     RESET ROLE;
     -- Reverts session identity back to alice
     

The WITH ADMIN OPTION Clause

When granting role membership WITH ADMIN OPTION, the member role (Alice) is authorized to grant or revoke membership in that group to other database roles, without possessing the cluster-wide CREATEROLE attribute.

---

Object Privileges & Access Control (DCL)

Database objects (tables, views, schemas, sequences, functions) are protected by an Access Control List (ACL). Privileges are assigned using GRANT and removed using REVOKE.

+-------------------------------------------------------------------------+
|                    Relational Object Access Pipeline                    |
+-------------------------------------------------------------------------+
| Client queries: SELECT * FROM sales.orders;                             |
|                                                                         |
| Step 1: Does the role have USAGE privilege on schema 'sales'?           |
|         ├── NO  -> ERROR: permission denied for schema sales            |
|         └── YES -> Proceed to Step 2                                    |
|                                                                         |
| Step 2: Does the role have SELECT privilege on table 'orders'?          |
|         ├── NO  -> ERROR: permission denied for table orders            |
|         └── YES -> Execute Query                                        |
+-------------------------------------------------------------------------+

Crucial Schema Requirement: The USAGE Privilege

A common administration pitfall occurs when an administrator grants SELECT on a table, but the user still receives permission denied:

-- Fails if user lacks USAGE on the schema!
GRANT SELECT ON sales.orders TO alice;

-- Mandatory prerequisite:
GRANT USAGE ON SCHEMA sales TO alice;

Without USAGE on the parent schema, a role cannot look up or access any object contained inside that schema, regardless of table-level grants.

Granting and Revoking Table Privileges

-- Grant specific DML privileges on a table
GRANT SELECT, INSERT, UPDATE ON TABLE sales.orders TO alice;

-- Grant all privileges on all existing tables in a schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales TO engineering_group;

-- Revoke privileges
REVOKE INSERT, UPDATE ON TABLE sales.orders FROM alice;

-- The PUBLIC pseudo-role (represents all current and future roles)
REVOKE ALL ON SCHEMA public FROM PUBLIC;

The WITH GRANT OPTION Clause

If a privilege is granted WITH GRANT OPTION, the recipient can grant that same privilege to other roles:

GRANT SELECT ON TABLE sales.orders TO alice WITH GRANT OPTION;

When revoking privileges granted with grant options, the administrator must choose between RESTRICT and CASCADE:

• REVOKE SELECT ON TABLE sales.orders FROM alice RESTRICT;: Fails if Alice has granted the privilege to other users.
• REVOKE SELECT ON TABLE sales.orders FROM alice CASCADE;: Automatically cascades the revocation to all downstream users who received the privilege from Alice.

---

Object Ownership Rules and Ownership Transfers

When an object (table, view, index, sequence) is created, the role that executed the CREATE statement is automatically designated as the OWNER of that object.

Ownership Privileges

The owner of an object enjoys special non-revocable rights:

• Can ALTER the object definition (add columns, rename tables, add constraints).
• Can DROP the object.
• Can GRANT and REVOKE any privilege on the object to other roles.
• Bypasses Row-Level Security (RLS) policies by default.

[!IMPORTANT] Only the Owner or Superuser: A user who has ALL PRIVILEGES on a table still cannot drop or alter that table unless they are the table's owner or a superuser! Privileges grant data manipulation rights, not object lifecycle ownership.

Transferring Ownership

Ownership can be explicitly reassigned using ALTER ... OWNER TO:

ALTER TABLE sales.orders OWNER TO sales_admin;
ALTER SCHEMA sales OWNER TO sales_admin;

Dropping Roles That Own Objects

A role cannot be dropped if it owns objects or holds privileges:

-- Attempting to drop an active owner fails:
-- DROP ROLE dev_lead;
-- ERROR: role "dev_lead" cannot be dropped because some objects depend on it

-- Reassign all owned objects across the current database in one command
REASSIGN OWNED BY dev_lead TO new_owner;

-- Drop all remaining privilege grants held by dev_lead
DROP OWNED BY dev_lead;

-- Now the role can be dropped cleanly
DROP ROLE dev_lead;

---

Managing Future Privileges: ALTER DEFAULT PRIVILEGES

A critical challenge in production schema management is the "new table problem". Running GRANT SELECT ON ALL TABLES IN SCHEMA sales TO analyst; applies only to tables that exist at that exact instant. When an application deployment script creates a new table tomorrow, the analyst role receives permission denied.

To solve this, PostgreSQL provides ALTER DEFAULT PRIVILEGES:

-- Define default privileges for future tables
ALTER DEFAULT PRIVILEGES 
    FOR ROLE dev_deployer 
    IN SCHEMA sales 
    GRANT SELECT, INSERT ON TABLES TO reporting_role;

Syntax Mechanics and Best Practices

• FOR ROLE creator_role: Specifies the role that will create future objects. If omitted, it defaults to the role executing the ALTER DEFAULT PRIVILEGES command!
• IN SCHEMA schema_name: Restricts the rule to a specific schema. If omitted, the default privileges apply across the entire database whenever creator_role creates an object.
• In team environments where multiple developers create tables, the best practice is to assign ownership of the schema to a dedicated group role (e.g., schema_owner) and have developers execute SET ROLE schema_owner; prior to running migration scripts.

---

Row-Level Security (RLS) Fundamentals

While table privileges govern whether a user can access a table as a whole, Row-Level Security (RLS) restricts which individual rows a query can view or modify based on user identity or session context.

-- 1. Enable RLS on the table (Mandatory step)
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

-- 2. Define a security policy for SELECT queries
CREATE POLICY user_account_policy ON accounts
    FOR SELECT
    TO application_user
    USING (owner_role_name = CURRENT_USER);

-- 3. Define a security policy for INSERT/UPDATE validation
CREATE POLICY insert_account_policy ON accounts
    FOR INSERT
    TO application_user
    WITH CHECK (department_id = 10);

Key Concepts of RLS

• Default-Deny Model: Once RLS is enabled on a table, non-owner users see zero rows by default until at least one policy is explicitly created that permits access.
• USING clause: Evaluates existing rows during SELECT, UPDATE, and DELETE. Rows that evaluate to false or NULL are silently filtered out as if they did not exist.
• WITH CHECK clause: Evaluates new or modified rows during INSERT and UPDATE. If the expression evaluates to false or NULL, the command aborts with an error.
• Bypassing RLS: Table owners and superusers bypass RLS by default. To force RLS evaluation on the table owner as well, run:

  ALTER TABLE accounts FORCE ROW LEVEL SECURITY;
  

---

Comparison: Role Attributes vs. Object Privileges

---

Exam Tips and Common Pitfalls

• Exam Trap: Role Attribute Inheritance: System attributes such as SUPERUSER, CREATEDB, and CREATEROLE are never inherited through group membership. Even if user Bob is a member of group dba_group which has CREATEDB, Bob cannot create a database unless Bob's own role has CREATEDB or Bob runs SET ROLE dba_group.
• Exam Trap: Dropping Tables vs. Table Privileges: Having ALL PRIVILEGES on a table does NOT grant permission to DROP TABLE or ALTER TABLE. Only the owner of the table or a superuser can drop or alter it.
• Exam Trap: Default Privileges Target Creator: If an exam question asks why a new table is inaccessible despite running ALTER DEFAULT PRIVILEGES ... GRANT SELECT ..., check the creator. If the command omitted FOR ROLE, it only applies when the administrator who ran the command creates tables, not when other developers create them.
• Exam Trap: Schema USAGE Prerequisite: A query against sales.customers will fail with permission denied if the role lacks USAGE on the sales schema, even if granted SELECT on sales.customers.