An Overview of PostgreSQL’s Role-Based Access Control System

Table of Contents

1. What is Role-Based Access Control (RBAC)?
2. PostgreSQL’s RBAC Model: Key Concepts
3. Core Components of PostgreSQL RBAC
   • Roles: The Foundation
   • Users vs. Groups
4. Role Creation and Management
   • Creating Roles
   • Altering and Dropping Roles
5. Privilege Types in PostgreSQL
6. Granting and Revoking Privileges
   • GRANT: Assigning Privileges
   • REVOKE: Removing Privileges
   • WITH GRANT OPTION: Delegating Privileges
7. Inheritance and Role Membership
8. Advanced: Row-Level Security (RLS)
9. Best Practices for PostgreSQL RBAC
10. Common Pitfalls to Avoid
11. Conclusion
12. References

What is Role-Based Access Control (RBAC)?

RBAC is a security paradigm where access to resources is governed by “roles”—abstract entities that represent job functions, responsibilities, or permissions. Instead of granting permissions directly to users, permissions are assigned to roles, and users are assigned to roles. This decouples users from permissions, making access management more modular, scalable, and auditable.

For example, instead of granting SELECT on the customers table to every sales representative individually, you create a sales_rep role, grant SELECT on customers to sales_rep, and then add all sales reps to the sales_rep role. If a new sales rep joins, simply add them to the role; if permissions change, update the role once.

PostgreSQL’s RBAC Model: Key Concepts

PostgreSQL’s RBAC implementation is flexible and aligns with SQL standards, with some extensions. At its core:

• Roles are the primary unit: PostgreSQL does not distinguish between “users” and “groups”—both are roles. A role can act as a user (with login access), a group (with members), or both.
• Privileges are granular: Permissions can be granted at multiple levels (database, schema, table, column, etc.) and tailored to specific actions (e.g., SELECT, INSERT).
• Inheritance: Roles can inherit privileges from other roles (e.g., a user role inherits permissions from a group role it belongs to).

Core Components of PostgreSQL RBAC

Roles: The Foundation

A “role” in PostgreSQL is a named entity that can own database objects (tables, schemas, etc.) and have privileges granted to it. Roles are global to the PostgreSQL cluster (not per-database), meaning a role exists across all databases in the cluster.

Users vs. Groups

PostgreSQL does not have separate “user” and “group” entities—these are just roles with different attributes:

• User roles: Roles with the LOGIN attribute, allowing them to connect to the database. They typically represent individual users (e.g., alice, bob).
• Group roles: Roles without the LOGIN attribute, used to manage sets of permissions. They act as containers for privileges and can have other roles (users or groups) as members (e.g., sales_team, read_only).

This flexibility allows roles to be both users and groups (e.g., a role could have LOGIN and also be a member of other groups).

Role Creation and Management

PostgreSQL provides SQL commands to create, modify, and delete roles.

Creating Roles

Use CREATE ROLE to define a new role. Key attributes include:

• LOGIN: Allows the role to connect to the database (required for user roles).
• PASSWORD: Sets a password (for LOGIN roles).
• NOSUPERUSER: Ensures the role does not have superuser privileges (default, and recommended for most roles).
• INHERIT: Determines if the role inherits privileges from roles it is a member of (default: INHERIT).

Examples:

-- Create a user role (with login access)
CREATE ROLE alice WITH LOGIN PASSWORD 'secure_password123' NOSUPERUSER INHERIT;

-- Create a group role (no login, for shared permissions)
CREATE ROLE sales_team WITH NOSUPERUSER NOLOGIN;

Shortcut: CREATE USER is an alias for CREATE ROLE ... WITH LOGIN, so:

-- Equivalent to CREATE ROLE bob WITH LOGIN ...
CREATE USER bob WITH PASSWORD 'bob_password456';

Altering and Dropping Roles

Use ALTER ROLE to modify existing roles (e.g., change passwords, add/remove attributes). Use DROP ROLE to delete roles.

Examples:

-- Change alice's password
ALTER ROLE alice WITH PASSWORD 'new_secure_password';

-- Allow alice to create databases
ALTER ROLE alice CREATEDB;

-- Remove login access from a role (convert to group)
ALTER ROLE alice NOLOGIN;

-- Delete a role (must have no dependent objects/members)
DROP ROLE IF EXISTS obsolete_role;

Privilege Types in PostgreSQL

PostgreSQL supports granular privileges at multiple levels. Below are common privilege types and their scopes:

A special PUBLIC role exists, representing all roles. By default, some privileges (e.g., CONNECT on databases, USAGE on the public schema) are granted to PUBLIC—restrict this for sensitive databases!

Granting and Revoking Privileges

GRANT: Assigning Privileges

The GRANT command assigns privileges to roles. Syntax:

GRANT <privileges> ON <object> TO <role> [WITH GRANT OPTION];

Examples:

-- Grant CONNECT on the 'sales_db' database to sales_team
GRANT CONNECT ON DATABASE sales_db TO sales_team;

-- Grant USAGE on the 'public' schema to sales_team
GRANT USAGE ON SCHEMA public TO sales_team;

-- Grant SELECT and INSERT on the 'customers' table to sales_team
GRANT SELECT, INSERT ON TABLE customers TO sales_team;

-- Grant UPDATE on the 'email' column of 'customers' to alice
GRANT UPDATE (email) ON TABLE customers TO alice;

-- Add alice to the sales_team group (grant membership)
GRANT sales_team TO alice;

REVOKE: Removing Privileges

Use REVOKE to remove previously granted privileges:

REVOKE <privileges> ON <object> FROM <role> [CASCADE | RESTRICT];

Examples:

-- Revoke INSERT on 'customers' from sales_team
REVOKE INSERT ON TABLE customers FROM sales_team;

-- Remove alice from sales_team
REVOKE sales_team FROM alice;

-- Revoke CONNECT from PUBLIC on sales_db (restrict public access)
REVOKE CONNECT ON DATABASE sales_db FROM PUBLIC;

WITH GRANT OPTION: Delegating Privileges

The WITH GRANT OPTION clause allows a role to grant its privileges to others. Use cautiously—overuse can lead to permission sprawl.

Example:

-- Grant SELECT on 'customers' to bob WITH GRANT OPTION
GRANT SELECT ON TABLE customers TO bob WITH GRANT OPTION;

-- Now bob can grant SELECT on 'customers' to charlie
GRANT SELECT ON TABLE customers TO charlie; -- Executed by bob

To revoke GRANT OPTION without revoking the privilege itself:

REVOKE GRANT OPTION FOR SELECT ON TABLE customers FROM bob;

Inheritance and Role Membership

By default, roles inherit privileges from the group roles they are members of (due to the INHERIT attribute). For example, if alice is a member of sales_team, and sales_team has SELECT on customers, alice automatically has SELECT on customers.

Disabling Inheritance

If a role is created with NOINHERIT, it does not inherit privileges from group roles. Members must explicitly SET ROLE to use the group’s privileges:

-- Create a role with NOINHERIT
CREATE ROLE temp_analyst WITH LOGIN NOINHERIT;

-- Add temp_analyst to sales_team (group with SELECT on customers)
GRANT sales_team TO temp_analyst;

-- temp_analyst cannot use sales_team's privileges by default
SELECT * FROM customers; -- Fails: permission denied

-- Explicitly switch to sales_team role to inherit privileges
SET ROLE sales_team;
SELECT * FROM customers; -- Succeeds

Advanced: Row-Level Security (RLS)

Row-Level Security (RLS) extends RBAC by restricting which rows a role can access or modify, based on policies. RLS is enforced at the database level, ensuring security even if the application layer is compromised.

Enabling RLS

To use RLS:

1. Enable RLS on a table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
2. Create a policy defining row-level access rules with CREATE POLICY.

Example:

Suppose we have a employees table with a department column. We want the sales_team role to only access rows where department = 'Sales'.

-- Create employees table
CREATE TABLE employees (
  id INT,
  name TEXT,
  department TEXT,
  salary NUMERIC
);

-- Enable RLS on employees
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- Create policy: sales_team sees only Sales department rows
CREATE POLICY sales_team_employees_policy 
  ON employees 
  FOR SELECT 
  USING (department = 'Sales')
  TO sales_team;

-- Grant SELECT on employees to sales_team (RLS works with RBAC!)
GRANT SELECT ON employees TO sales_team;

Now, any member of sales_team will only see rows where department = 'Sales'.

Best Practices for PostgreSQL RBAC

1. Principle of Least Privilege: Grant only the minimal privileges needed for a role to perform its job. Avoid over-privileged roles.
2. Use Group Roles for Shared Permissions: Define group roles (e.g., read_only, data_analysts) to manage permissions for teams, rather than granting to individual users.
3. Avoid Superuser for Daily Tasks: Reserve the postgres superuser for administrative tasks only. Use regular roles for application access.
4. Revoke from PUBLIC: By default, PUBLIC has broad privileges (e.g., CONNECT on databases). Revoke these in production:

   REVOKE ALL ON DATABASE production_db FROM PUBLIC;

5. Audit Permissions Regularly: Use \du (in psql) or pg_roles/pg_permissions system catalogs to audit role privileges.
6. Limit WITH GRANT OPTION: Only grant WITH GRANT OPTION to trusted roles, and audit usage.

Common Pitfalls to Avoid

• Confusing Cluster-Wide Roles: Roles are global to the PostgreSQL cluster, not per-database. A role exists across all databases in the cluster.
• Forgetting Schema Privileges: Users need USAGE on a schema to access objects within it, even if they have table-level privileges.
• Overusing WITH GRANT OPTION: This can lead to “privilege creep”—track who has GRANT OPTION and revoke it when no longer needed.
• Ignoring RLS: For multi-tenant databases or sensitive data, RLS is critical to prevent unauthorized row access.
• Disabling Inheritance Accidentally: INHERIT is default, but NOINHERIT can break expected access if misconfigured.

Conclusion

PostgreSQL’s RBAC system is a powerful tool for securing your database. By leveraging roles, granular privileges, inheritance, and advanced features like RLS, you can enforce strict access controls while keeping management scalable. Adopting best practices like least privilege and regular audits will ensure your database remains secure as your organization grows.

References

• PostgreSQL Official Documentation: Database Roles
• PostgreSQL Official Documentation: Privileges
• PostgreSQL Official Documentation: Row-Level Security
• PostgreSQL 11 Administration Cookbook (Chapter on Security)