PostgreSQL Roles and Permissions

PostgreSQL uses a role-based access control system to manage who can connect to the database and what actions they are permitted to perform. A role is a database entity that can own objects and hold privileges. Roles replace the older concept of separate "users" and "groups" — in PostgreSQL, both users and groups are simply roles with different attributes.

Roles, Users, and Groups

In PostgreSQL, there is no technical distinction between a user and a role. A role with the LOGIN attribute can connect to the database and is effectively a "user." A role without LOGIN acts as a group that other roles can be members of.

-- A role that can log in (a "user")
CREATE ROLE alice LOGIN PASSWORD 'securepass123';

-- A role that cannot log in (a "group")
CREATE ROLE readonly_group;

Creating Roles

Basic Syntax

CREATE ROLE role_name [options];

Common Role Attributes

-- Create a reporting user with limited access
CREATE ROLE reporter
    LOGIN
    PASSWORD 'report2024!'
    CONNECTION LIMIT 5
    VALID UNTIL '2025-12-31';

-- Create a superuser for administration
CREATE ROLE db_admin
    LOGIN
    SUPERUSER
    PASSWORD 'adminpass!';

Modifying and Dropping Roles

-- Change password
ALTER ROLE alice PASSWORD 'newpass456';

-- Grant superuser privilege
ALTER ROLE alice SUPERUSER;

-- Remove login ability
ALTER ROLE alice NOLOGIN;

-- Drop a role (must have no owned objects or dependencies)
DROP ROLE alice;

Listing Roles

-- In psql
\du

-- Via SQL
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles;

Granting Privileges

A role cannot do anything by default. Privileges must be explicitly granted using the GRANT command.

Database-Level Privileges

-- Allow a role to connect to a database
GRANT CONNECT ON DATABASE company_db TO alice;

-- Allow creating schemas in the database
GRANT CREATE ON DATABASE company_db TO alice;

Schema-Level Privileges

-- Allow using objects within a schema
GRANT USAGE ON SCHEMA public TO alice;

Table-Level Privileges

-- Grant SELECT on a single table
GRANT SELECT ON employees TO alice;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO alice;

-- Grant all privileges on a table
GRANT ALL PRIVILEGES ON orders TO alice;

-- Grant SELECT on all tables in a schema at once
GRANT SELECT ON ALL TABLES IN SCHEMA public TO alice;

Column-Level Privileges

Privileges can be restricted to specific columns, which is useful for hiding sensitive data:

-- Alice can only see name and department, not salary
GRANT SELECT (name, department) ON employees TO alice;

Revoking Privileges

-- Remove INSERT privilege
REVOKE INSERT ON orders FROM alice;

-- Remove all privileges
REVOKE ALL PRIVILEGES ON employees FROM alice;

Role Membership (Groups)

A role can be a member of another role, inheriting its privileges. This is the group mechanism.

-- Create a group role with read-only access
CREATE ROLE readonly_group NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

-- Create a group with read-write access
CREATE ROLE readwrite_group NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_group;

-- Add users to groups
GRANT readonly_group TO alice;
GRANT readwrite_group TO bob;

Alice now inherits all SELECT privileges of readonly_group. Bob can read and write through readwrite_group. Adding a new table and granting it to the group automatically applies to all members.

WITH GRANT OPTION

GRANT SELECT ON employees TO alice WITH GRANT OPTION;

This allows Alice to grant the same SELECT privilege to other roles herself. Without this option, only the original grantor (or a superuser) can re-grant the privilege.

Default Privileges

When new tables are created in the future, existing GRANTs do not automatically apply to them. Default privileges solve this:

-- Future tables created by alice will be readable by readonly_group
ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_group;

Row-Level Security (RLS)

Row-Level Security restricts which rows a role can see or modify within a table. Each role sees only the rows its policies permit.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create a policy: users see only their own orders
CREATE POLICY user_orders_policy ON orders
    FOR SELECT
    USING (customer_email = current_user);

-- Without a policy, the owner (superuser) still sees everything
-- For regular users, no matching policy means no rows visible

Practical Security Pattern: Least Privilege

The principle of least privilege means granting only the minimum access needed for a role to do its job.

-- Step 1: Create roles
CREATE ROLE app_read NOLOGIN;
CREATE ROLE app_write NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'apppass!';

-- Step 2: Grant appropriate privileges to group roles
GRANT CONNECT ON DATABASE company_db TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

GRANT app_read TO app_write;  -- write role inherits read
GRANT INSERT, UPDATE, DELETE ON orders, products TO app_write;

-- Step 3: Assign user to appropriate group
GRANT app_read TO app_user;

-- Step 4: Set default privileges for new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_read;

Key Points

• In PostgreSQL, users and groups are both roles. A role with LOGIN is a user; a role with NOLOGIN is a group.
• Roles start with no privileges; everything must be explicitly granted.
• GRANT assigns privileges; REVOKE removes them.
• Roles can be members of other roles, inheriting their privileges — this is how groups work.
• Column-level privileges allow selective exposure of sensitive table columns.
• Default privileges (ALTER DEFAULT PRIVILEGES) automatically apply grants to future objects.
• Row-Level Security (RLS) controls which rows each role can access within a table.
• Always apply the principle of least privilege: grant only what is necessary.