Database Roles

PGRestify provides comprehensive database role management that integrates seamlessly with PostgREST's authentication and authorization system. The CLI automatically generates and manages database roles with proper permissions for different user types.

Overview

Database roles in PGRestify define what operations different types of users can perform on your database. The system follows PostgREST's role-based security model where:

• Anonymous Role (web_anon): For unauthenticated users with read-only access
• Authenticated Role (authenticated): For logged-in users with CRUD operations
• Admin Role (web_admin): For administrators with full database access

Role Configuration

PGRestify automatically generates roles based on your PostgREST configuration, ensuring consistency between your API and database permissions.

Default Roles

-- Anonymous users (read-only)
web_anon

-- Authenticated users (CRUD operations)  
authenticated

-- Administrative users (full access)
web_admin

PostgREST Integration

These roles are automatically referenced in your PostgREST configuration:

# postgrest.conf
db-anon-role = "web_anon"
db-authenticated-role = "authenticated"

Generating Database Roles

Use the CLI to generate database roles:

Basic Usage

# Generate roles SQL file
pgrestify api setup roles

# Generate with custom output path
pgrestify api setup roles --output sql/database-roles.sql

# Preview generated SQL without writing file
pgrestify api setup roles --dry-run

# Generate and execute directly
pgrestify api setup roles --execute

Example Output

The CLI generates comprehensive SQL for role setup:

-- Database Roles Setup
-- Generated by PGRestify CLI
-- This script creates the roles referenced in PostgREST configuration and RLS policies

-- Create roles if they don't exist
DO $$ BEGIN
    CREATE ROLE web_anon NOLOGIN;
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE ROLE authenticated NOLOGIN;
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE ROLE web_admin NOLOGIN;
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

-- Grant basic permissions to anonymous role
GRANT USAGE ON SCHEMA api TO web_anon;
GRANT SELECT ON ALL TABLES IN SCHEMA api TO web_anon;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA api TO web_anon;

-- Grant permissions to authenticated role
GRANT USAGE ON SCHEMA api TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA api TO authenticated;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA api TO authenticated;

-- Grant full permissions to admin role
GRANT ALL ON SCHEMA api TO web_admin;
GRANT ALL ON ALL TABLES IN SCHEMA api TO web_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA api TO web_admin;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT ON TABLES TO web_anon;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT ALL ON TABLES TO web_admin;

-- Set default privileges for future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT ON SEQUENCES TO web_anon;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT USAGE, SELECT ON SEQUENCES TO authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT ALL ON SEQUENCES TO web_admin;

-- Grant execute permissions on functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO web_anon;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO authenticated;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO web_admin;

Role Permissions

Anonymous Role (web_anon)

• Schema: USAGE permission
• Tables: SELECT only (read-only access)
• Sequences: SELECT only
• Functions: EXECUTE permission
• Use Case: Public data access, unauthenticated API calls

Authenticated Role (authenticated)

• Schema: USAGE permission
• Tables: SELECT, INSERT, UPDATE, DELETE (full CRUD)
• Sequences: USAGE and SELECT
• Functions: EXECUTE permission
• Use Case: Logged-in users with standard permissions

Admin Role (web_admin)

• Schema: ALL privileges
• Tables: ALL privileges
• Sequences: ALL privileges
• Functions: EXECUTE permission
• Use Case: Administrative operations, database management

Multi-Schema Support

PGRestify supports multiple schemas in your PostgREST configuration:

# Multiple schemas
db-schemas = "public,api,auth"

The generated roles will have appropriate permissions across all specified schemas:

-- Permissions are granted across all schemas
GRANT USAGE ON SCHEMA public TO web_anon;
GRANT USAGE ON SCHEMA api TO web_anon;
GRANT USAGE ON SCHEMA auth TO web_anon;

-- Similar grants for all roles across all schemas

Row Level Security Integration

Database roles work hand-in-hand with Row Level Security (RLS) policies:

-- Example RLS policy using roles
CREATE POLICY "Public posts are viewable by anyone"
    ON posts FOR SELECT
    TO web_anon
    USING (published = true);

CREATE POLICY "Users can manage their own posts"
    ON posts FOR ALL
    TO authenticated
    USING (author_id = current_user_id());

CREATE POLICY "Admins can manage all posts"
    ON posts FOR ALL
    TO web_admin
    USING (true);

Authentication Flow

1. Unauthenticated Request: Uses web_anon role
2. JWT Authentication: Role is determined by JWT claims
3. PostgREST Processing: Switches to appropriate database role
4. RLS Enforcement: Database enforces row-level policies based on role

Best Practices

Role Naming

# Use descriptive role names that match your domain
web_anon          # Anonymous web users
authenticated     # Logged-in users
web_admin         # Administrative users
app_service       # Service accounts
read_only         # Read-only applications

Permission Principle

• Least Privilege: Grant only necessary permissions
• Progressive Access: Anonymous < Authenticated < Admin
• Future-Proof: Use ALTER DEFAULT PRIVILEGES for new objects

Security Considerations

-- Roles should be NOLOGIN
CREATE ROLE web_anon NOLOGIN;

-- Use connection pooling with authenticator role
CREATE ROLE authenticator LOGIN PASSWORD 'secure_password';
GRANT web_anon TO authenticator;
GRANT authenticated TO authenticator;

Executing Role Setup

Manual Execution

# Using psql
psql -d your_database -f sql/roles.sql

# Using Docker
docker compose exec -T postgres psql -U postgres -d your_db < sql/roles.sql

Automated Execution

# Execute directly through CLI
pgrestify api setup roles --execute

# Part of migration workflow
pgrestify api migrate

Troubleshooting

Role Already Exists

The generated SQL handles existing roles gracefully:

DO $$ BEGIN
    CREATE ROLE web_anon NOLOGIN;
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

Permission Denied

Ensure you're running as a database superuser:

# Connect as superuser
ADMIN_DATABASE_URL='postgresql://postgres:password@localhost:5432/postgres' \
pgrestify api setup roles --execute

PostgREST Connection Issues

Verify role configuration matches PostgREST:

# Check current configuration
pgrestify api config show

# Update if needed
pgrestify api config update

Advanced Configuration

Custom Role Names

Modify your PostgREST configuration:

db-anon-role = "anonymous_user"
db-authenticated-role = "app_user"

The CLI will automatically use these custom names.

Additional Roles

Create specialized roles for specific use cases:

-- Read-only reporting role
CREATE ROLE reporting NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA api TO reporting;

-- Service account role
CREATE ROLE service_account NOLOGIN;
GRANT USAGE ON SCHEMA api TO service_account;
GRANT SELECT, INSERT ON specific_tables TO service_account;

Integration Examples

With Row Level Security

-- Enable RLS on table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Create role-based policies
CREATE POLICY "anonymous_select" ON posts
    FOR SELECT TO web_anon
    USING (published = true);

CREATE POLICY "authenticated_crud" ON posts
    FOR ALL TO authenticated
    USING (author_id = current_user_id());

With JWT Claims

Configure PostgREST to use JWT role claims:

# postgrest.conf
jwt-role-claim-key = "role"

JWT payload:

{
  "role": "authenticated",
  "user_id": "123",
  "exp": 1234567890
}

With Connection Pooling

-- Create authenticator role for connection pooling
CREATE ROLE authenticator LOGIN PASSWORD 'secure_password';

-- Grant all application roles to authenticator
GRANT web_anon TO authenticator;
GRANT authenticated TO authenticator;
GRANT web_admin TO authenticator;

Monitoring and Maintenance

Check Role Permissions

-- View role permissions
SELECT 
    r.rolname,
    r.rolcanlogin,
    ARRAY(
        SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid
    ) AS memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname IN ('web_anon', 'authenticated', 'web_admin');

Audit Role Usage

-- Check table permissions by role
SELECT 
    schemaname,
    tablename,
    grantor,
    grantee,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee IN ('web_anon', 'authenticated', 'web_admin');

Next Steps

• Authentication - Set up JWT authentication
• Custom Schemas - Configure multi-schema applications