How to Implement RBAC in PostgreSQL

How do I set up role-based access control (RBAC) in PostgreSQL?

RBAC (role-based access control) in PostgreSQL limits what users can read or change by assigning roles only the privileges they need.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is RBAC in PostgreSQL?

Role-based access control (RBAC) assigns database permissions to roles instead of individual users. A user inherits every privilege granted to its roles, making permission management simpler and safer.

How do roles and privileges work?

Roles can log in (LOGIN) or simply group privileges (NOLOGIN). Privileges include CONNECT, USAGE, SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.Grant or revoke them at database, schema, table, or column scope.

How to create roles for an ecommerce app?

Create application roles such as analytics_reader, order_manager, and admin. Then map human users or application services to those roles with GRANT commands.

CREATE ROLE analytics_reader NOLOGIN; CREATE ROLE order_manager NOLOGIN; CREATE ROLE admin NOLOGIN; CREATE ROLE app_user LOGIN PASSWORD 'strong_pw'; GRANT analytics_reader, order_manager TO app_user;

How to grant table-level privileges?

Grant roles only the statements they need on each table.For example, allow analytics_reader to query but not mutate data, while order_manager can change order records.

GRANT SELECT ON Customers, Orders, Products, OrderItems TO analytics_reader; GRANT SELECT, INSERT, UPDATE ON Orders, OrderItems TO order_manager;

How to use default privileges for future tables?

Default privileges ensure new tables inherit the correct grants automatically.Always set them immediately after creating a schema.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_reader; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO order_manager;

How to enforce row-level security?

Enable RLS and attach policies so even if a role has SELECT, it only sees authorized rows.Use current_user or custom session variables to filter data.

ALTER TABLE Orders ENABLE ROW LEVEL SECURITY; CREATE POLICY customer_isolation ON Orders FOR SELECT USING (customer_id = current_setting('app.current_customer')::int);

What are best practices for PostgreSQL RBAC?

Grant privileges to roles, never to users. Use NOLOGIN roles for grouping. Keep roles minimal—read, write, admin tiers. Document every grant in version-controlled migration files. Review privileges on every release.

Frequently asked questions

Can I nest roles?

Yes.Grant one role to another to create inheritance chains, but keep them shallow for clarity.

How do I audit role usage?

Enable log_connections and log_statement = 'ddl' to track role usage and privilege changes. Pair with pgAudit for detailed logs.

Does RBAC slow queries?

Privilege checks are lightweight. Only complex row-level security policies may add measurable overhead. Index filter columns to minimize impact.

Why How to Implement RBAC in PostgreSQL is important

How to Implement RBAC in PostgreSQL Example Usage


-- 1. Create read-only role and grant usage
CREATE ROLE analytics_reader NOLOGIN;
GRANT CONNECT ON DATABASE shop_db TO analytics_reader;
GRANT USAGE ON SCHEMA public TO analytics_reader;
GRANT SELECT ON Customers, Orders, Products, OrderItems TO analytics_reader;

-- 2. Map BI tool user to the role
CREATE ROLE bi_tool LOGIN PASSWORD 'bi_pwd';
GRANT analytics_reader TO bi_tool;

How to Implement RBAC in PostgreSQL Syntax


-- Create roles
CREATE ROLE role_name [LOGIN | NOLOGIN] [PASSWORD 'pwd'];

-- Assign roles to users
GRANT role_name [, ...] TO user_name;

-- Grant privileges
GRANT { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
ON { TABLE | SCHEMA | DATABASE } object_name [, ...]
TO role_name;

-- Revoke privileges
REVOKE privilege_list ON object_name FROM role_name;

-- Default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
GRANT privilege_list ON TABLES TO role_name;

-- Example (ecommerce)
CREATE ROLE analytics_reader NOLOGIN;
GRANT SELECT ON TABLE Customers, Orders, Products, OrderItems TO analytics_reader;

Common Mistakes

Granting privileges to individual users. This creates permission sprawl. Fix by creating NOLOGIN roles, granting privileges to roles, and assigning users to those roles.
Forgetting default privileges. New tables end up unprotected. Fix by running ALTER DEFAULT PRIVILEGES right after schema creation to mirror existing grants.