PostgreSQL Permissions

Overview

PostgreSQL uses roles and privileges to control access to database objects.

Create Role

-- Create login role
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- Create role with database
CREATE ROLE app_user WITH LOGIN PASSWORD 'password123' CREATEDB;

GRANT

-- Grant privileges on table
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

-- Grant all privileges
GRANT ALL PRIVILEGES ON users TO admin;

-- Grant on schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant on database
GRANT CONNECT ON DATABASE mydb TO app_user;

REVOKE

REVOKE INSERT, UPDATE ON users FROM app_user;
REVOKE ALL PRIVILEGES ON users FROM guest;

Role Attributes

-- Superuser
CREATE ROLE super WITH LOGIN PASSWORD 'pass' SUPERUSER;

-- Create database
CREATE ROLE db_admin WITH LOGIN PASSWORD 'pass' CREATEDB;

-- Create role
CREATE ROLE role_admin WITH LOGIN PASSWORD 'pass' CREATEROLE;

-- Replication
CREATE ROLE replicator WITH LOGIN PASSWORD 'pass' REPLICATION;

Overview

Group Roles

-- Create group
CREATE GROUP developers;

-- Add members
GRANT developers TO alice;
GRANT developers TO bob;

-- Grant to group
GRANT SELECT ON products TO developers;

Row Level Security

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY user_policy ON users
    FOR SELECT
    USING (user_id = current_setting('app.current_user')::INTEGER);

Create Role

Permissions:

• CREATE ROLE, GRANT, REVOKE
• Various privilege types
• Role attributes
• Group roles
• Row Level Security