🚀AdvancedSecurity
Roles, GRANT, and REVOKE
Control database access with roles, privileges, and the GRANT/REVOKE system.
Overview
PostgreSQL uses a role-based access control (RBAC) model. Roles are both users (with LOGIN) and groups (without LOGIN). Privileges on objects (tables, schemas, functions) are granted to roles. A role can belong to other roles (role inheritance), acquiring their privileges.
The privilege system has two levels: object privileges (SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER on tables) and database/schema privileges (CONNECT on database, USAGE on schema). Always follow least-privilege: grant only what is needed.
Key Points
- •CREATE ROLE name creates a role (group without LOGIN)
- •CREATE ROLE name LOGIN PASSWORD '...' creates a user
- •GRANT privilege ON object TO role
- •REVOKE privilege ON object FROM role
- •GRANT role TO other_role role membership
- •GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO readonly
Examples
Creating roles and granting privileges
-- Create roles
CREATE ROLE readonly;
CREATE ROLE app_user LOGIN PASSWORD 'secret';
-- Grant role membership
GRANT readonly TO app_user;
-- Grant privileges to readonly role
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- Grant for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
-- Revoke
REVOKE INSERT ON orders FROM app_user;
-- Check privileges
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'orders';Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4