Understand PostgreSQL roles

In PostgreSQL, users and groups are both "roles." A role can:

• Login (like a user) or not (like a group)
• Own database objects
• Have specific privileges
• Inherit privileges from other roles

-- View existing roles
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';

Create an application role

Create a dedicated role for your application:

-- Create a login role for your app
CREATE ROLE myapp_user WITH
  LOGIN
  PASSWORD 'use-a-strong-password-here'
  CONNECTION LIMIT 20;  -- Limit concurrent connections

-- Create the application database (if needed)
CREATE DATABASE myapp_db OWNER myapp_user;

-- Connect to the database
\c myapp_db

Grant schema permissions

Grant access to the schema and create privileges:

-- Grant usage on the public schema
GRANT USAGE ON SCHEMA public TO myapp_user;

-- Grant ability to create tables (only if app needs migrations)
GRANT CREATE ON SCHEMA public TO myapp_user;

-- Or for tighter security, only grant on specific schemas
CREATE SCHEMA app_schema;
GRANT ALL ON SCHEMA app_schema TO myapp_user;

Grant table-level permissions

Grant specific permissions on tables:

-- Grant full CRUD on specific tables
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO myapp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO myapp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON comments TO myapp_user;

-- Grant on all existing tables in schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;

-- Grant on future tables too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp_user;

-- Grant sequence usage (for auto-increment IDs)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO myapp_user;

Create a read-only role

For analytics, reporting, or debugging - create a read-only role:

-- Create read-only role
CREATE ROLE myapp_readonly WITH
  LOGIN
  PASSWORD 'another-strong-password';

-- Grant connect
GRANT CONNECT ON DATABASE myapp_db TO myapp_readonly;

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

-- Grant SELECT only
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myapp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO myapp_readonly;

-- Optionally, exclude sensitive tables
REVOKE SELECT ON users FROM myapp_readonly;  -- No user data access

Create role groups for team management

Use role inheritance for team access:

-- Create group roles (no login)
CREATE ROLE developers NOLOGIN;
CREATE ROLE analysts NOLOGIN;

-- Grant permissions to groups
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analysts;

-- Create individual users that inherit from groups
CREATE ROLE john_dev WITH LOGIN PASSWORD 'xxx' IN ROLE developers;
CREATE ROLE jane_analyst WITH LOGIN PASSWORD 'xxx' IN ROLE analysts;

-- John now has all developer permissions
-- Jane now has all analyst permissions

Revoke dangerous permissions

Remove permissions you don't want:

-- Revoke public access (PostgreSQL grants some by default)
REVOKE ALL ON DATABASE myapp_db FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Revoke create from public schema (prevents random table creation)
REVOKE CREATE ON SCHEMA public FROM myapp_user;

-- Revoke specific table access
REVOKE DELETE ON audit_logs FROM myapp_user;  -- App can't delete logs
REVOKE ALL ON admin_settings FROM myapp_user;  -- No admin access