PostgreSQL — Topic 17: Security & RLS

1

Users

Creating and managing database users (login accounts)

▾

Users vs Roles — The Secret

In PostgreSQL, users and roles are the same thing. A user is simply a role that has the LOGIN privilege. Both are stored in pg_roles. This unified model is more flexible than traditional user/group systems.

PostgreSQL Identity Model pg_roles (single table for everything) ┌─────────────────────────────────────────────┐ │ rolname │ rolcanlogin │ rolsuper │ ... │ ├─────────────────────────────────────────────┤ │ postgres │ true │ true │ │ ← superuser + login │ app_user │ true │ false │ │ ← regular user (LOGIN) │ readonly │ false │ false │ │ ← role (no LOGIN) │ admin │ false │ false │ │ ← role (no LOGIN) └─────────────────────────────────────────────┘ User = Role WITH LOGIN Role = Role WITHOUT LOGIN (a group/permission bundle)

Creating Users

          
-- Create a basic user with a password
CREATE USER app_user WITH PASSWORD 'StrongPassword123!';

-- Equivalent (CREATE USER is alias for CREATE ROLE WITH LOGIN)
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPassword123!';

-- Create user with connection limit (prevent runaway connections)
CREATE USER api_service
    WITH PASSWORD 'SecurePass!'
    CONNECTION LIMIT 20;

-- Create a superuser (use sparingly!)
CREATE USER admin_user
    WITH PASSWORD 'VerySecure!'
    SUPERUSER;

-- Create a user that cannot create databases
CREATE USER readonly_user
    WITH PASSWORD 'ReadOnly!'
    NOCREATEDB NOCREATEROLE;

-- Change password
ALTER USER app_user WITH PASSWORD 'NewPassword456!';

-- Disable a user (prevent login without deleting)
ALTER USER app_user NOLOGIN;

-- Re-enable
ALTER USER app_user LOGIN;

-- Drop user (must revoke all privileges first)
DROP USER app_user;
        

Viewing Users

          
-- List all users/roles
\du                  -- psql shortcut

-- Or query directly
SELECT
    rolname           AS username,
    rolsuper          AS is_superuser,
    rolcreatedb       AS can_create_db,
    rolcreaterole     AS can_create_roles,
    rolcanlogin       AS can_login,
    rolconnlimit      AS conn_limit
FROM  pg_roles
ORDER BY rolname;

-- See which roles a user belongs to
SELECT
    r.rolname   AS role,
    m.rolname   AS member_of
FROM  pg_roles r
JOIN  pg_auth_members am ON r.oid = am.member
JOIN  pg_roles m        ON am.roleid = m.oid
WHERE r.rolname = 'app_user';
        

2

Roles

Grouping privileges with roles for clean, maintainable access control

▾

What is a Role?

A role (without LOGIN) is a named bundle of permissions. Instead of granting individual privileges to each user, you grant them to a role, then add users to that role. This is the PostgreSQL equivalent of "groups" in other systems.

Role Hierarchy — Real SaaS Example readonly readwrite admin_role (SELECT only) (SELECT+INSERT (full access +UPDATE+DELETE) + DDL) │ │ │ ┌───────────┘ ┌───────┘ ┌───────┘ ▼ ▼ ▼ analytics_user api_service backend_admin reporting_user worker_service │ ▼ Inherits readonly permissions (no need to grant SELECT again!)

Creating and Using Roles

          
-- Step 1: Create role bundles (no LOGIN — these are groups)
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin_role;

-- Step 2: Grant privileges to roles (covered in detail in Section 3)
GRANT SELECT                          ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE  ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES                  ON ALL TABLES IN SCHEMA public TO admin_role;

-- Step 3: Create actual users
CREATE USER analytics_user  WITH PASSWORD 'pass1';
CREATE USER api_service      WITH PASSWORD 'pass2';
CREATE USER backend_admin    WITH PASSWORD 'pass3';

-- Step 4: Assign roles to users
GRANT readonly    TO analytics_user;
GRANT readwrite   TO api_service;
GRANT admin_role  TO backend_admin;

-- Step 5: A user can also have multiple roles
GRANT readonly TO api_service;  -- api_service now has both readonly + readwrite

-- Remove user from a role
REVOKE readonly FROM analytics_user;

-- Drop a role
DROP ROLE readonly;
        

Role Inheritance

          
-- By default, roles INHERIT permissions from assigned roles
-- You can disable this with NOINHERIT

CREATE ROLE senior_dev NOINHERIT;

-- Without INHERIT, the user must manually SET ROLE to use the permissions
GRANT admin_role TO senior_dev;

-- senior_dev now must do this to use admin privileges:
SET ROLE admin_role;
-- ... do admin stuff ...
RESET ROLE;  -- switch back

-- Check current role
SELECT current_user, session_user;
        

3

Privileges

What users can and cannot do — the complete privilege system

▾

Privilege Levels

PostgreSQL has a layered privilege system. You need to grant access at each level:

Privilege Layers (all must be granted for access to work) Level 1: DATABASE └── CONNECT → Can the user connect to this database? Level 2: SCHEMA └── USAGE → Can the user see inside this schema? Level 3: TABLE / SEQUENCE / FUNCTION └── SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER Missing ANY level = access denied Example: Granting SELECT on a table is useless if the user doesn't also have USAGE on the schema and CONNECT on the database!

Table Privileges Reference

Privilege	Allows	Example use
`SELECT`	Read rows	Analytics, reporting users
`INSERT`	Add new rows	API service writing data
`UPDATE`	Modify existing rows	App service updating records
`DELETE`	Remove rows	Cleanup jobs
`TRUNCATE`	Empty entire table fast	Admin/maintenance scripts
`REFERENCES`	Create foreign keys referencing this table	Schema management
`TRIGGER`	Create triggers on the table	Admin only
`ALL PRIVILEGES`	Everything above	Owner / admin role

Default Privileges Problem

⚠️

Watch out! Granting SELECT ON ALL TABLES only covers existing tables. New tables created later will NOT be covered. Use ALTER DEFAULT PRIVILEGES to fix this.

          
-- PROBLEM: This only grants SELECT on tables that exist RIGHT NOW
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- SOLUTION: Also set default privileges so future tables are covered
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- For sequences (needed for SERIAL / auto-increment columns)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO readwrite;

-- For functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO readwrite;
        

4

Grants

Giving access — the complete GRANT syntax

▾

Full Grant Examples

          
-- ── DATABASE LEVEL ──────────────────────────────────
GRANT CONNECT ON DATABASE myapp_db TO app_user;

-- ── SCHEMA LEVEL ─────────────────────────────────────
GRANT USAGE ON SCHEMA public TO app_user;

-- ── TABLE LEVEL ──────────────────────────────────────
-- Specific table, specific privilege
GRANT SELECT ON orders TO analytics_user;

-- Multiple privileges on one table
GRANT SELECT, INSERT, UPDATE ON orders TO api_service;

-- All privileges on all tables (existing)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- ── COLUMN LEVEL ─────────────────────────────────────
-- Only allow reading specific columns (e.g., hide salary column)
GRANT SELECT (id, name, email) ON users TO analytics_user;
-- analytics_user CANNOT see the 'salary' column

-- ── FUNCTION LEVEL ───────────────────────────────────
GRANT EXECUTE ON FUNCTION get_user_stats(integer) TO api_service;

-- ── SEQUENCE LEVEL ───────────────────────────────────
-- Needed when users INSERT rows with SERIAL columns
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO api_service;

-- ── WITH GRANT OPTION ────────────────────────────────
-- Allow the grantee to also grant this privilege to others
GRANT SELECT ON orders TO team_lead WITH GRANT OPTION;
        

Complete Setup for an App User

          
-- Full setup script for a typical backend API user

-- 1. Create the user
CREATE USER api_service WITH PASSWORD 'SuperSecret!' CONNECTION LIMIT 50;

-- 2. Allow connecting to the database
GRANT CONNECT ON DATABASE myapp_db TO api_service;

-- 3. Allow seeing inside the schema
GRANT USAGE ON SCHEMA public TO api_service;

-- 4. Grant table access (existing tables)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO api_service;

-- 5. Grant sequence access (for SERIAL/auto-increment inserts)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_service;

-- 6. Auto-grant on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO api_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO api_service;
        

5

Revokes

Taking access away — and the public schema trap

▾

REVOKE Syntax

          
-- Revoke specific privilege
REVOKE INSERT ON orders FROM analytics_user;

-- Revoke multiple privileges
REVOKE SELECT, INSERT ON orders FROM analytics_user;

-- Revoke all privileges on a table
REVOKE ALL PRIVILEGES ON orders FROM analytics_user;

-- Revoke all privileges on all tables
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM analytics_user;

-- Revoke with CASCADE (also removes privileges granted by this user to others)
REVOKE SELECT ON orders FROM team_lead CASCADE;

-- Revoke role membership
REVOKE readonly FROM analytics_user;
        

The Public Schema Security Trap

🚨

Default PostgreSQL is insecure! By default, every user has CREATE and USAGE on the public schema. Any logged-in user can create tables! Lock this down immediately in production.

          
-- PostgreSQL 14 and earlier: public schema is wide open
-- Everyone can CREATE tables in public by default. Fix this:

-- 1. Remove default CREATE from public schema
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 2. Also remove default CONNECT to database (optional but safer)
REVOKE CONNECT ON DATABASE myapp_db FROM PUBLIC;

-- 3. Now grant only what's needed explicitly
--    (PUBLIC here means "all roles" — revoke from PUBLIC = removes default)

-- PostgreSQL 15+ improved defaults: CREATE is no longer granted to PUBLIC by default
SHOW server_version;
        

Audit Who Has What Privilege

          
-- See all table-level grants
SELECT
    grantee,
    table_name,
    privilege_type,
    is_grantable
FROM  information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

-- See schema-level grants
SELECT *
FROM  information_schema.role_usage_grants;

-- psql shortcut to show privileges on a table
\dp orders
        

6

Row Level Security (RLS)

The most powerful security feature — automatically filter rows per user

▾

What is RLS?

Row Level Security lets you attach a WHERE clause to a table that automatically applies to every query — based on who is running the query. Users literally cannot see rows they don't have access to, even if they run SELECT * FROM orders.

Without RLS: User Alice runs: SELECT * FROM orders; Result: ALL 1,000,000 rows from ALL customers ← security leak! With RLS: User Alice runs: SELECT * FROM orders; PostgreSQL auto-adds: WHERE tenant_id = current_setting('app.tenant_id') Result: Only Alice's company's 500 rows ← secure ✓ The filter is INVISIBLE to the user and cannot be bypassed.

🏆

Critical for SaaS: RLS is how you build true multi-tenant data isolation at the database level — even if a bug in your app code sends the wrong user ID, the database itself enforces the boundary.

Step 1 — Enable RLS on a Table

          
-- Enabling RLS on a table does nothing by itself
-- until you add policies. But once enabled, if no policy matches,
-- ALL rows are DENIED by default (safe default!).

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table OWNER (important!)
-- Without this, the table owner bypasses RLS
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Disable RLS
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

-- Check which tables have RLS enabled
SELECT relname, relrowsecurity, relforcerowsecurity
FROM  pg_class
WHERE relkind = 'r'
  AND  relrowsecurity = true;
        

Step 2 — Create Policies

A policy is the WHERE clause that gets added to queries. You can have different policies for SELECT, INSERT, UPDATE, DELETE.

          
-- Basic policy syntax:
-- CREATE POLICY name ON table [FOR command] [TO role] USING (expression);

-- Example 1: Users can only see their own orders
-- (using current_user — the logged-in DB user name)
CREATE POLICY user_orders_policy
    ON orders
    USING (user_id = current_user::integer);

-- Example 2: Tenant isolation using a session variable
-- (the app sets this when a request starts)
CREATE POLICY tenant_isolation
    ON orders
    USING (tenant_id = current_setting('app.tenant_id')::integer);

-- Example 3: Separate policies for read vs write
CREATE POLICY orders_select_policy
    ON orders
    FOR SELECT
    USING (tenant_id = current_setting('app.tenant_id')::integer);

CREATE POLICY orders_insert_policy
    ON orders
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::integer);
-- WITH CHECK applies to INSERT/UPDATE (verifies the new row, not existing row)
-- USING applies to SELECT/UPDATE/DELETE (filters which rows to consider)

-- Example 4: Admin role bypasses the policy
CREATE POLICY tenant_isolation
    ON orders
    TO api_service     -- only applies to this role
    USING (tenant_id = current_setting('app.tenant_id')::integer);
-- admin_role is not mentioned → no policy → full access
        

USING vs WITH CHECK

Clause	Used For	What It Checks
`USING (expr)`	SELECT, UPDATE, DELETE	Filters EXISTING rows — rows not matching are invisible
`WITH CHECK (expr)`	INSERT, UPDATE	Validates NEW/modified rows — prevents inserting to wrong tenant

          
-- Combined policy with both clauses (most common for UPDATE)
CREATE POLICY orders_update_policy
    ON orders
    FOR UPDATE
    USING      (tenant_id = current_setting('app.tenant_id')::integer)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::integer);
-- USING:      can only UPDATE rows that belong to this tenant
-- WITH CHECK: after update, row must still belong to this tenant
-- (prevents "moving" a row to another tenant via UPDATE)
        

Managing Policies

          
-- List all policies
SELECT *
FROM  pg_policies
WHERE tablename = 'orders';

-- Alter an existing policy
ALTER POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Drop a policy
DROP POLICY tenant_isolation ON orders;

-- Temporarily bypass RLS as superuser (for debugging)
SET row_security = off;   -- only works for superusers
SELECT * FROM orders;     -- sees everything
SET row_security = on;
        

7

RLS — Multi-Tenant SaaS Pattern

Complete end-to-end example: isolating data between organizations

▾

The Architecture

Multi-Tenant SaaS with RLS Internet │ ┌────────▼────────┐ │ FastAPI / Node │ ← Your backend │ app server │ └────────┬────────┘ │ ┌────────────▼────────────────────────┐ │ Connection setup per request: │ │ SET app.tenant_id = '42'; │ ← Set tenant context │ SET app.user_id = '1001'; │ └────────────┬────────────────────────┘ │ ┌────────▼────────┐ │ PostgreSQL │ │ │ │ RLS Policy: │ │ WHERE │ │ tenant_id = │ │ current_ │ │ setting( │ │ 'app.tenant_id'│ │ ) │ └─────────────────┘ Tenant 42 can NEVER see Tenant 99's data — database enforces it.

Complete Multi-Tenant Setup

          
-- ── STEP 1: Schema ──────────────────────────────────────
CREATE TABLE organizations (
    id         bigserial PRIMARY KEY,
    name       text NOT NULL,
    created_at timestamptz DEFAULT now()
);

CREATE TABLE users (
    id              bigserial PRIMARY KEY,
    organization_id bigint REFERENCES organizations(id),
    email           text UNIQUE NOT NULL,
    role            text DEFAULT 'member'
);

CREATE TABLE documents (
    id              bigserial PRIMARY KEY,
    organization_id bigint REFERENCES organizations(id),
    title           text   NOT NULL,
    content         text,
    created_by      bigint REFERENCES users(id)
);

-- ── STEP 2: Create app DB user ───────────────────────────
CREATE USER app_service WITH PASSWORD 'AppPass!';
GRANT CONNECT ON DATABASE myapp TO app_service;
GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_service;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;

-- ── STEP 3: Enable RLS ──────────────────────────────────
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;

-- ── STEP 4: Create RLS Policies ─────────────────────────
-- Documents: only visible within same organization
CREATE POLICY tenant_documents
    ON documents
    USING      (organization_id = current_setting('app.organization_id')::bigint)
    WITH CHECK (organization_id = current_setting('app.organization_id')::bigint);

-- Users: only see users in your organization
CREATE POLICY tenant_users
    ON users
    USING      (organization_id = current_setting('app.organization_id')::bigint)
    WITH CHECK (organization_id = current_setting('app.organization_id')::bigint);
        

Step 5 — App Code Sets the Context

Your backend (FastAPI/Node) sets the tenant context at the start of every request, before any query runs:

          
-- In your app, before any query in a request:
-- (Python SQLAlchemy / asyncpg example shown as SQL)

-- Set the tenant context (from JWT / session)
SET app.organization_id = '42';
SET app.user_id         = '1001';

-- Now all queries are automatically scoped
SELECT * FROM documents;
-- ↑ Returns ONLY documents where organization_id = 42

INSERT INTO documents (organization_id, title, content, created_by)
VALUES (42, 'My Doc', 'Content here', 1001);
-- ↑ WITH CHECK ensures organization_id matches — insert succeeds

INSERT INTO documents (organization_id, title, content, created_by)
VALUES (99, 'Hacked Doc', '...', 1001);
-- ↑ organization_id=99 ≠ current_setting=42 → ERROR: new row violates policy!
        

Python / FastAPI Integration Pattern

          
-- Pseudocode showing how to integrate with Python/FastAPI
-- (shown as SQL + comments for clarity)

-- In Python (asyncpg):
--
-- async def set_tenant_context(conn, organization_id: int, user_id: int):
--     await conn.execute(
--         "SELECT set_config('app.organization_id', $1, true),"    -- true = local to tx
--                         "set_config('app.user_id', $2, true)",
--         str(organization_id), str(user_id)
--     )
--
-- # In FastAPI middleware / dependency:
-- async def get_db(request: Request):
--     async with pool.acquire() as conn:
--         user = request.state.user          # from JWT auth
--         await set_tenant_context(conn, user.org_id, user.id)
--         yield conn
--         # Connection returns to pool — settings reset automatically

-- The set_config 'true' parameter = local to current transaction
-- When transaction ends, setting resets → no leakage between requests!

-- Verify isolation works:
SET app.organization_id = '42';
SELECT current_setting('app.organization_id');  -- returns '42'
SELECT COUNT(*) FROM documents;  -- only org 42 docs counted
        

Advanced: User-Level Policies Within a Tenant

          
-- Add a role column to documents for finer control
ALTER TABLE documents ADD COLUMN visibility text DEFAULT 'team';
-- 'team' = all org members can see; 'private' = only creator can see

-- Drop old policy and create a smarter one
DROP POLICY tenant_documents ON documents;

CREATE POLICY tenant_documents_v2
    ON documents
    USING (
        organization_id = current_setting('app.organization_id')::bigint
        AND (
            visibility = 'team'
            OR created_by = current_setting('app.user_id')::bigint
        )
    );

-- Now:
-- 'team' documents → visible to all members of the org
-- 'private' documents → visible only to the creator
-- Documents from other orgs → always invisible
        

8

Security Best Practices

Production-ready security checklist for PostgreSQL

▾

Production Security Checklist

1

Never use the postgres superuser in your app. Create a dedicated app user with only the privileges it needs.

2

Revoke CREATE from public schema. REVOKE CREATE ON SCHEMA public FROM PUBLIC;

3

Use roles, not direct user grants. Grant privileges to readonly / readwrite roles, then assign users to roles.

4

Set connection limits. CREATE USER app_service CONNECTION LIMIT 50 — prevent runaway connections from crashing your DB.

5

Use RLS for any multi-tenant table. Don't rely on app code alone. Defense in depth: database enforces tenant isolation.

6

Use FORCE ROW LEVEL SECURITY. Without this, the table owner bypasses your RLS policies — a common mistake.

7

Use column-level privileges for sensitive data. Don't expose salary, SSN, or tokens. GRANT SELECT (id, name) ON users TO analytics;

8

Use set_config(..., true) (local to transaction) when setting RLS context variables. This resets automatically when the transaction ends — preventing context leakage between requests.

9

Audit with pg_audit extension. Log all DDL, role changes, and sensitive queries for compliance (SOC2, HIPAA, GDPR).

10

Regularly audit privileges. Query information_schema.role_table_grants to catch over-permissioned users.

Full Security Summary

Concept	What It Does	Key Command
User	A role with LOGIN — can connect to the DB	`CREATE USER name WITH PASSWORD '...'`
Role	A named bundle of privileges (no LOGIN)	`CREATE ROLE name`
Privilege	A specific permission (SELECT, INSERT, etc.)	—
GRANT	Give a privilege or role to a user/role	`GRANT SELECT ON table TO user`
REVOKE	Remove a privilege or role	`REVOKE SELECT ON table FROM user`
RLS	Auto-filter rows based on who is querying	`ALTER TABLE t ENABLE ROW LEVEL SECURITY`
Policy	The WHERE clause that RLS attaches	`CREATE POLICY name ON t USING (expr)`
set_config	Set a session variable for RLS context	`set_config('app.org_id', '42', true)`

Security & Row Level Security

Users

Users vs Roles — The Secret

Creating Users

Viewing Users

Roles

What is a Role?

Creating and Using Roles

Role Inheritance

Privileges

Privilege Levels

Table Privileges Reference

Default Privileges Problem

Grants

Full Grant Examples

Complete Setup for an App User

Revokes

REVOKE Syntax

The Public Schema Security Trap

Audit Who Has What Privilege

Row Level Security (RLS)

What is RLS?

Step 1 — Enable RLS on a Table

Step 2 — Create Policies

USING vs WITH CHECK

Managing Policies

RLS — Multi-Tenant SaaS Pattern

The Architecture

Complete Multi-Tenant Setup

Step 5 — App Code Sets the Context

Python / FastAPI Integration Pattern

Advanced: User-Level Policies Within a Tenant

Security Best Practices

Production Security Checklist

Full Security Summary