PostgreSQL Users, Roles, and Privilege Management
1. What You'll Learn
- CREATE ROLE / CREATE USER (in PG, USER = a LOGIN role)
- GRANT / REVOKE privilege management
- Privilege hierarchy: database / schema / table / column / function / sequence
- DEFAULT PRIVILEGES
- Row-Level Security policies (RLS / Row Security Policies, PG feature)
- SCHEMA privilege control
- Role inheritance (INHERIT / NOINHERIT)
- System views: pg_roles / pg_auth_members
2. The Story
Charlie is the DBA for a SaaS e-commerce platform and needs to configure database privileges for 5 teams:
| Team | Privileges needed |
|---|---|
| dev (development) | Read/write all business tables, create test tables |
| analytics (analytics) | Read-only all business tables, create materialized views |
| ops (operations) | Manage users, monitor the database |
| audit (audit) | Read only audit logs, not business data |
| app (application service) | Read/write business tables, no DDL |
Charlie uses role inheritance and RLS to implement the principle of least privilege.
3. Concept: Roles and Users
(1) CREATE ROLE vs CREATE USER
| Command | LOGIN privilege | Equivalent form |
|---|---|---|
CREATE ROLE r1; |
None (can't log in) | — |
CREATE USER u1; |
Yes (can log in) | CREATE ROLE u1 LOGIN; |
In PG, users and roles are the same concept; USER is just a ROLE with the LOGIN attribute.
(2) Role Attributes at a Glance
| Attribute | Description | Creation syntax |
|---|---|---|
| LOGIN | Allow connecting to the database | LOGIN / NOLOGIN |
| SUPERUSER | Superuser, bypasses all privileges | SUPERUSER |
| CREATEDB | Can create databases | CREATEDB |
| CREATEROLE | Can create/manage roles | CREATEROLE |
| INHERIT | Automatically inherits privileges of owned roles | INHERIT (default) |
| NOINHERIT | Doesn't auto-inherit; needs SET ROLE | NOINHERIT |
| PASSWORD | Set password | PASSWORD 'xxx' |
| VALID UNTIL | Password expiry time | VALID UNTIL 'timestamp' |
▶ Example: Create Roles and Users
SQL
-- Group roles (cannot login)
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team NOINHERIT;
CREATE ROLE audit_team NOINHERIT;
-- Login users
-- ⚠️ Don't hardcode passwords in production; use environment variables or a secrets manager
CREATE USER alice_dev PASSWORD 'SecureP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'SecureP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'SecureP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'SecureP@ss4' IN ROLE ops_team INHERIT;
Output:
TEXT
CREATE TABLE
▶ Example: Modify Role Attributes
SQL
-- Add CREATEDB privilege to ops team lead
ALTER ROLE diana_ops CREATEDB;
-- Set password expiry
ALTER ROLE charlie_analyst VALID UNTIL '2025-12-31';
-- Rename a role
ALTER ROLE dev_team RENAME TO engineering_team;
-- Disable login temporarily
ALTER ROLE bob_dev NOLOGIN;
Output:
TEXT
CREATE TABLE
4. Concept: GRANT / REVOKE Privileges
(1) Privilege Hierarchy
flowchart TD
A[Database<br/>CONNECT / CREATE / TEMP] --> B[Schema<br/>CREATE / USAGE]
B --> C[Table<br/>SELECT / INSERT / UPDATE / DELETE / TRUNCATE / REFERENCES / TRIGGER]
C --> D[Column<br/>SELECT / INSERT / UPDATE / REFERENCES]
B --> E[Function<br/>EXECUTE]
B --> F[Sequence<br/>USAGE / SELECT / UPDATE]
A --> G[Role<br/>MEMBER / SET]
style A fill:#e1f5fe
style B fill:#bbdefb
style C fill:#c8e6c9
style D fill:#fff9c4
(2) Common Privilege Keywords
| Object | Grantable privileges | Description |
|---|---|---|
| DATABASE | CONNECT, CREATE, TEMP | Connect, create schema, temp tables |
| SCHEMA | CREATE, USAGE | Create objects, access objects |
| TABLE | SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | Full CRUD + foreign key reference |
| COLUMN | SELECT, INSERT, UPDATE, REFERENCES | Column-level control |
| FUNCTION | EXECUTE | Call function |
| SEQUENCE | USAGE, SELECT, UPDATE | Use sequence |
▶ Example: GRANT Table-Level Privileges
SQL
-- Dev team: full CRUD on business tables
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO dev_team;
-- Analytics team: read-only
GRANT SELECT ON products TO analytics_team;
GRANT SELECT ON orders TO analytics_team;
GRANT SELECT ON customers TO analytics_team;
-- Audit team: only audit logs
GRANT SELECT ON price_audit_log TO audit_team;
GRANT SELECT ON ddl_audit_log TO audit_team;
Output:
TEXT
INSERT 0 1
▶ Example: GRANT Schema and Database Privileges
SQL
-- Dev team can create objects in dev schema
GRANT CREATE, USAGE ON SCHEMA dev TO dev_team;
-- Analytics team can use but not create in public schema
GRANT USAGE ON SCHEMA public TO analytics_team;
-- Allow analytics to create materialized views in their own schema
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;
-- App service connects to the database
GRANT CONNECT ON DATABASE shop_db TO app_service;
Output:
TEXT
CREATE TABLE
▶ Example: REVOKE Privileges
SQL
-- Revoke DELETE from dev team on products
REVOKE DELETE ON products FROM dev_team;
-- Revoke all privileges on a table
REVOKE ALL PRIVILEGES ON orders FROM public;
-- Revoke schema creation right
REVOKE CREATE ON SCHEMA dev FROM dev_team;
-- Cascade: revoke and dependent grants
REVOKE SELECT ON products FROM analytics_team CASCADE;
Output:
TEXT
DELETE 2
(3) GRANT ALL and GRANT SELECT ALL TABLES
| Command | Effect |
|---|---|
GRANT ALL ON t TO r; |
Grant all privileges on table t |
GRANT ALL ON SCHEMA s TO r; |
Grant all privileges on schema s |
GRANT SELECT ON ALL TABLES IN SCHEMA s TO r; |
Grant SELECT on all tables in schema s |
GRANT USAGE ON ALL SEQUENCES IN SCHEMA s TO r; |
Grant USAGE on all sequences |
5. Concept: DEFAULT PRIVILEGES
(1) Why Default Privileges Are Needed
A plain GRANT only affects already existing objects. Tables created in the future won't automatically get privileges. DEFAULT PRIVILEGES solves this.
| Command | Effect |
|---|---|
ALTER DEFAULT PRIVILEGES IN SCHEMA s GRANT SELECT ON TABLES TO r; |
New tables in s are auto-granted |
ALTER DEFAULT PRIVILEGES FOR ROLE owner GRANT ... |
Default privileges for a specific creator |
▶ Example: Set Default Privileges
SQL
-- All future tables in public schema are readable by analytics_team
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analytics_team;
-- All future tables created by app_service are writable by dev_team
ALTER DEFAULT PRIVILEGES FOR ROLE app_service IN SCHEMA public
GRANT SELECT, INSERT, UPDATE ON TABLES TO dev_team;
-- All future sequences in public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO dev_team;
-- All future functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO analytics_team;
Output:
TEXT
INSERT 0 1
▶ Example: View Current Default Privileges
SQL
SELECT
pg_get_userbyid(defaclrole) AS grantor,
pg_get_userbyid(defaclnamespace) AS namespace_owner,
n.nspname AS schema,
defaclobjtype AS object_type,
defaclacl AS acl
FROM pg_default_acl d
JOIN pg_namespace n ON n.oid = d.defaclnamespace;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
6. Concept: Row-Level Security Policies (RLS)
(1) How RLS Works (PG Feature)
RLS lets you control data access at the row level—different roles see different rows in the same table.
| Step | Command | Description |
|---|---|---|
| 1. Enable RLS | ALTER TABLE t ENABLE ROW LEVEL SECURITY; |
Enable row-level security on the table |
| 2. Create policy | CREATE POLICY ... ON t ...; |
Define the row-level rule |
| 3. Superuser bypass | — | SUPERUSER is not subject to RLS by default |
| 4. Table owner bypass | — | The table owner is not subject by default; use FORCE to enforce |
(2) Policy Types
| Policy type | Keyword | Description |
|---|---|---|
| SELECT | FOR SELECT |
Controls visible rows |
| INSERT | FOR INSERT |
Controls insertable rows |
| UPDATE | FOR UPDATE |
Controls updatable rows (includes BEFORE/AFTER) |
| DELETE | FOR DELETE |
Controls deletable rows |
| ALL | FOR ALL |
Shared across all operations |
▶ Example: Enable RLS and Create a Policy
SQL
-- Enable RLS on orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Analysts can only see completed orders
CREATE POLICY pol_analytics_completed
ON orders FOR SELECT
TO analytics_team
USING (order_status = 'completed');
-- Dev team can see all rows
CREATE POLICY pol_dev_all
ON orders FOR ALL
TO dev_team
USING (true)
WITH CHECK (true);
Output:
TEXT
CREATE TABLE
▶ Example: RLS Based on the Current User
SQL
-- Enable RLS on customers table
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
-- Each customer service user sees only their assigned region
CREATE POLICY pol_region_access
ON customers FOR SELECT
USING (region = current_setting('app.region', true));
-- Force the table owner to also comply with RLS
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
Output:
TEXT
CREATE TABLE
▶ Example: WITH CHECK for INSERT/UPDATE
SQL
-- Sales team can only insert orders in their region
CREATE POLICY pol_sales_insert
ON orders FOR INSERT
TO dev_team
WITH CHECK (region = current_setting('app.region', true));
-- Sales team can only update orders in their region
CREATE POLICY pol_sales_update
ON orders FOR UPDATE
TO dev_team
USING (region = current_setting('app.region', true))
WITH CHECK (region = current_setting('app.region', true));
Output:
TEXT
INSERT 0 1
(3) USING vs WITH CHECK
| Clause | Role | Applies to |
|---|---|---|
| USING | Filters visible rows (the WHERE of SELECT/UPDATE/DELETE) | SELECT/UPDATE/DELETE |
| WITH CHECK | Validates whether the new row is allowed (new values of INSERT/UPDATE) | INSERT/UPDATE |
7. Concept: Role Inheritance and System Views
(1) INHERIT vs NOINHERIT
| Attribute | Behavior | Scenario |
|---|---|---|
| INHERIT (default) | Automatically gains privileges of owned roles | Regular users |
| NOINHERIT | Needs SET ROLE r to use privileges |
Temporary privilege escalation, audit separation |
▶ Example: NOINHERIT and SET ROLE
SQL
-- Create a powerful role with NOINHERIT
CREATE ROLE admin_role NOINHERIT;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;
-- Diana is in admin_role but cannot use admin privileges automatically
CREATE USER diana PASSWORD 'SecureP@ss5' IN ROLE admin_role NOINHERIT;
-- Diana must explicitly switch to use admin rights
SET ROLE admin_role;
-- Now diana can perform admin operations
RESET ROLE;
-- Back to normal privileges
Output:
TEXT
CREATE TABLE
(2) System Views
| View | Content |
|---|---|
pg_roles |
All roles and their attributes |
pg_auth_members |
Role–member relationships |
information_schema.role_table_grants |
Table-level privileges |
information_schema.role_usage_grants |
Schema/function privileges |
▶ Example: Query Roles and Privileges
SQL
-- List all roles and their attributes
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolinherit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;
-- List role membership
SELECT
r1.rolname AS member,
r2.rolname AS role
FROM pg_auth_members m
JOIN pg_roles r1 ON r1.oid = m.member
JOIN pg_roles r2 ON r2.oid = m.roleid
ORDER BY r2.rolname, r1.rolname;
-- Check table privileges for a role
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'analytics_team'
ORDER BY table_name, privilege_type;
Output:
TEXT
CREATE TABLE
8. Flowchart: Privilege Configuration Decision
flowchart TD
A[Configure privileges] --> B{Need row-level control?}
B -->|Yes| C[Enable RLS<br/>CREATE POLICY]
B -->|No| D{Privilege granularity?}
D -->|Whole table| E[GRANT ON TABLE]
D -->|Specific column| F[GRANT column_name ON TABLE]
D -->|All tables in schema| G[GRANT ALL TABLES IN SCHEMA]
E --> H{Need auto-grant on new tables?}
G --> H
H -->|Yes| I[ALTER DEFAULT PRIVILEGES]
H -->|No| J[Manual GRANT]
C --> K{Need to restrict table owner too?}
K -->|Yes| L[FORCE ROW LEVEL SECURITY]
K -->|No| M[Owner bypasses RLS by default]
I --> N{User needs temporary escalation?}
J --> N
N -->|Yes| O[NOINHERIT + SET ROLE]
N -->|No| P[INHERIT by default]
style C fill:#fff9c4
style I fill:#c8e6c9
style O fill:#ffcdd2
9. Comprehensive Example
Charlie configures a complete privilege system for the 5 teams:
SQL
-- Step 1: Create group roles
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team INHERIT;
CREATE ROLE audit_team NOINHERIT;
CREATE ROLE app_service LOGIN PASSWORD 'AppSecRet!';
-- Step 2: Grant schema permissions
GRANT CREATE, USAGE ON SCHEMA public TO dev_team;
GRANT USAGE ON SCHEMA public TO analytics_team;
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;
GRANT USAGE ON SCHEMA public TO ops_team;
GRANT USAGE ON SCHEMA audit TO audit_team;
-- Step 3: Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev_team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO audit_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON products, orders, order_items, customers TO app_service;
-- Step 4: Default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analytics_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dev_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit
GRANT SELECT ON TABLES TO audit_team;
-- Step 5: Sequence and function permissions
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dev_team;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO dev_team;
-- Step 6: RLS - analysts only see completed orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_analytics_orders
ON orders FOR SELECT
TO analytics_team
USING (order_status = 'completed');
-- Step 7: RLS - audit team only sees audit schema
ALTER TABLE price_audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_audit_read
ON price_audit_log FOR SELECT
TO audit_team
USING (true);
-- Step 8: Create login users and assign roles
CREATE USER alice_dev PASSWORD 'DevP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'DevP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'AnlP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'OpsP@ss4' IN ROLE ops_team CREATEROLE;
CREATE USER eve_audit PASSWORD 'AudP@ss5' IN ROLE audit_team;
❓ FAQ
Q What's the difference between CREATE USER and CREATE ROLE?
A CREATE USER is equivalent to CREATE ROLE ... LOGIN. In PG, users and roles are the same concept; USER is just a role that carries the LOGIN attribute by default.
Q Does GRANT SELECT ON ALL TABLES include tables created in the future?
A No. ALL TABLES grants only to currently existing tables. Future tables need ALTER DEFAULT PRIVILEGES for auto-granting or a manual GRANT.
Q Does RLS affect superusers?
A Not by default. SUPERUSER bypasses all RLS policies. To force it, use ALTER TABLE ... FORCE ROW LEVEL SECURITY, but this only applies to the table owner—SUPERUSER still bypasses.
Q How does a NOINHERIT role temporarily obtain privileges?
A Use SET ROLE target_role to switch to the target role and gain its privileges; after finishing, RESET ROLE restores the original privileges. SET ROLE only affects the current session.
Q Does REVOKE need CASCADE?
A If the revoked privilege was in turn granted to another role (a dependent grant), you need CASCADE to revoke it all at once. The default RESTRICT will error when there's a dependency.
Q How do I view which privileges a user actually has?
A Query the information_schema.role_table_grants view, or use psql's
\dp and \du+ commands. Note that INHERIT roles automatically gain the privileges of the roles they belong to.Q Can RLS's USING and WITH CHECK be written separately?
A Yes. If only USING is written, WITH CHECK defaults to the same as USING. For an ALL policy, explicitly writing both is recommended to make control clear.
Q What if USAGE on a schema isn't enough to access a table?
A USAGE only allows you to "enter" the schema and see the object list; you also need a privilege like SELECT on the specific table. Both layers of privilege must be satisfied.
📖 Summary
- In PG, USER = LOGIN ROLE; the role is the core unit of privilege management
- Role attributes: LOGIN/SUPERUSER/CREATEDB/CREATEROLE/INHERIT control different capabilities
- GRANT/REVOKE manage privileges; hierarchy is: database → schema → table → column
- DEFAULT PRIVILEGES solve the auto-grant problem for newly created objects
- RLS (PG feature) implements row-level access control; USING filters visible rows, WITH CHECK validates new rows
- INHERIT auto-inherits privileges; NOINHERIT needs SET ROLE for temporary escalation
- pg_roles / pg_auth_members / information_schema views query privilege configuration
- Principle of least privilege: grant on demand, avoid over-privileging
📝 Exercises
-
⭐ Create a role
readonlyand a userreport_user, grantreadonlySELECT on all tables in the public schema, and set default privileges so newly created tables are also auto-granted. -
⭐⭐ Enable RLS on the
orderstable and create policies:sales_teamcan only see orders in their own region (region = current_setting('app.region'));audit_teamcan only see orders withorder_status = 'completed'. -
⭐⭐⭐ Design a complete privilege system: create 3 group roles (
backend_dev,data_analyst,db_admin), granting each different levels of privilege (schema/table/column/function/sequence), configure DEFAULT PRIVILEGES, grant thesalarycolumn separately (analyst can't see it), and usepg_rolesandinformation_schemato query and verify the configuration result.
