Roles & Permissions

PostgreSQL uses a role-based permission system. Roles can represent users, groups, or both. Controlling who can access what in your database is essential for security. This lesson covers how to manage roles and permissions effectively.

Creating Roles

A role is a database entity that can own objects and have privileges:

-- Create a role for a user
CREATE ROLE alice LOGIN PASSWORD 'secure_password';

-- Create a role for a group
CREATE ROLE developers NOLOGIN;

LOGIN allows the role to connect to the database. NOLOGIN creates a group role that cannot log in directly but can be assigned to users.

Granting Privileges

Privileges control what actions roles can perform:

-- Grant database connection
GRANT CONNECT ON DATABASE my_database TO alice;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO alice;

-- Grant table permissions
GRANT SELECT, INSERT, UPDATE ON students TO alice;
GRANT SELECT, INSERT, UPDATE, DELETE ON enrollments TO alice;

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

-- Grant permissions on all tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO developers;

Revoking Privileges

To remove permissions:

-- Revoke specific privileges
REVOKE DELETE ON students FROM alice;

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON students FROM alice;

-- Revoke from all roles
REVOKE ALL PRIVILEGES ON students FROM PUBLIC;

Role Membership

Add users to groups to manage permissions more easily:

-- Grant developers group to alice
GRANT developers TO alice;

-- Now alice has all permissions granted to developers
-- To remove:
REVOKE developers FROM alice;

This pattern is very powerful. Instead of granting permissions to individual users, grant them to group roles and assign users to those groups.

Default Privileges

Set up automatic permissions for future objects:

-- Future tables in public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO developers;

-- Future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO developers;

Default privileges save you from having to manually grant permissions every time you create a new table.

Row-Level Security

PostgreSQL can restrict which rows a user can see:

-- Enable row-level security
ALTER TABLE students ENABLE ROW LEVEL SECURITY;

-- Users can only see their own data
CREATE POLICY student_isolation ON students
  USING (department_id = current_setting('app.user_department')::INTEGER);

-- Set the user's department in the session
SET app.user_department = '5';

Row-level security is powerful for multi-tenant applications where different users should only see their own data.

Viewing Permissions

To check what permissions exist:

\du                    -- List roles
\dp                    -- List table privileges
SELECT * FROM information_schema.role_table_grants;