P-6 — Authentication, Row-Level Security, and Access Control

Most tutorials treat PostgreSQL authentication as a one-liner: create a superuser, give it a password, connect. That works in development. In production it is a security incident waiting to happen.

This module covers the PostgreSQL access model from first principles — roles, privileges, and the Row-Level Security system that lets the database enforce data boundaries between tenants without any application-layer code. By the end you will have the mental model to design a proper production access architecture, and a working multi-tenant RLS setup you can adapt for real projects.

Why the Default Setup Is Dangerous

When you install PostgreSQL locally, the postgres superuser is created automatically. When you deploy to Heroku, Railway, or Supabase, you get a user with broad privileges. Most tutorials say "connect as this user from your app" and move on.

Here is what that means in practice:

• Your application can DROP TABLE its own data
• A SQL injection vulnerability has DELETE access to every table
• One compromised connection can exfiltrate the entire database
• There is no way to give a read-only analytics tool limited access

The fix is a proper role hierarchy with the principle of least privilege. PostgreSQL has a first-class system for this.

Roles Are Everything

PostgreSQL does not have "users" and "groups" as separate concepts. It has roles. A role can:

• Log in (it becomes a "user")
• Own objects (tables, sequences, schemas)
• Have other roles granted to it (role inheritance)
• Grant its privileges to other roles

Everything is a role. CREATE USER is just syntax sugar for CREATE ROLE ... WITH LOGIN.

Creating Roles

sql
-- A role that cannot log in — used as a permission container
CREATE ROLE readonly_role;

-- A role that can log in (a "user")
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';

-- Grant readonly_role's privileges to app_user
GRANT readonly_role TO app_user;

When app_user logs in, it has all privileges that readonly_role has. This is role inheritance.

Role Attributes

Key attributes when creating a role:

sql
-- Read-only service account for a BI tool
CREATE ROLE bi_reader WITH LOGIN PASSWORD 'bi_password_here';

-- Application role — no superuser, no DDL powers
CREATE ROLE app_service WITH LOGIN PASSWORD 'app_password_here';

Granting and Revoking Privileges

PostgreSQL privileges are granted on specific objects: tables, sequences, schemas, databases, functions.

The GRANT Syntax

sql
-- Grant SELECT on a specific table
GRANT SELECT ON TABLE orders TO readonly_role;

-- Grant full DML (no DDL) on multiple tables
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders, customers, products TO app_service;

-- Grant usage on a schema (required to see objects inside it)
GRANT USAGE ON SCHEMA public TO app_service;

-- Grant all privileges on all current tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- Grant privileges on future tables automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_role;

ALTER DEFAULT PRIVILEGES is the one most people miss. Without it, every new table you create needs a separate GRANT. With it, new tables automatically get the right privileges.

Revoking Privileges

sql
REVOKE DELETE ON TABLE orders FROM app_service;
REVOKE ALL ON TABLE internal_audit_log FROM app_service;

Checking What a Role Can Do

sql
-- All privileges in the database
\dp orders          -- in psql: show privileges for the orders table

-- Via system catalog
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_name = 'orders';

A Production Role Architecture

Here is a pattern that works well for production applications:

sql
-- 1. Owner role: owns the schema and all objects
--    Never used by the application at runtime
CREATE ROLE myapp_owner WITH LOGIN PASSWORD 'migration_password';

-- 2. Application role: DML only, no DDL
CREATE ROLE myapp_app WITH LOGIN PASSWORD 'app_password';

-- 3. Read-only role: for BI, analytics, debugging
CREATE ROLE myapp_reader WITH LOGIN PASSWORD 'reader_password';

-- Grant schema access
GRANT USAGE ON SCHEMA public TO myapp_app, myapp_reader;

-- Application gets DML
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myapp_app;

-- Reader gets SELECT only
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myapp_reader;

-- Future tables: set defaults for both roles
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp_app;

ALTER DEFAULT PRIVILEGES FOR ROLE myapp_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO myapp_reader;

Your migration tool connects as myapp_owner. Your application connects as myapp_app. Your BI tool connects as myapp_reader. A SQL injection in the app cannot drop tables or read tables the app role has no access to.

Row-Level Security (RLS)

Role-based access control gets you table-level isolation. Row-Level Security gets you row-level isolation. This is the mechanism behind Supabase's per-user data access, multi-tenant SaaS isolation, and any system where different users should see different subsets of the same table.

How RLS Works

When RLS is enabled on a table, every query against that table automatically gets an invisible WHERE clause appended — the clause defined by the policy. No application code required.

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

-- Without any policies, the table owner can still access rows.
-- All other users see zero rows (default-deny).

Once RLS is enabled and no policies exist, non-owner roles see an empty table. You then define policies to grant selective access.

Creating Policies

sql
CREATE POLICY policy_name
ON table_name
[AS PERMISSIVE | RESTRICTIVE]
[FOR command]        -- ALL, SELECT, INSERT, UPDATE, DELETE
[TO role]            -- which role this policy applies to
USING (condition)    -- applied to SELECT, UPDATE, DELETE rows
[WITH CHECK (condition)];  -- applied to INSERT, UPDATE rows

USING is the filter on rows being read or modified — "which rows can this role see/touch?"

WITH CHECK is the filter on new/updated data — "what values can this role write?"

Simple Per-User Policy

The classic example: users can only see their own rows.

sql
CREATE TABLE posts (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Users can read their own posts
CREATE POLICY posts_select_own
ON posts
FOR SELECT
TO app_service
USING (user_id = current_setting('app.current_user_id')::BIGINT);

-- Users can only insert posts with their own user_id
CREATE POLICY posts_insert_own
ON posts
FOR INSERT
TO app_service
WITH CHECK (user_id = current_setting('app.current_user_id')::BIGINT);

-- Users can only update their own posts
CREATE POLICY posts_update_own
ON posts
FOR UPDATE
TO app_service
USING (user_id = current_setting('app.current_user_id')::BIGINT)
WITH CHECK (user_id = current_setting('app.current_user_id')::BIGINT);

In your application, before executing queries:

sql
-- Set the current user context at the start of each session/transaction
SET app.current_user_id = '42';

Or in a transaction:

sql
BEGIN;
SET LOCAL app.current_user_id = '42';
-- All queries in this transaction see only user 42's rows
SELECT * FROM posts;  -- automatically filtered
COMMIT;

current_setting('app.current_user_id') reads this session variable. The database filters rows automatically — the application never has to add WHERE user_id = ? to every query.

Multi-Tenant RLS: Isolating Organizations

The most powerful use of RLS in production is multi-tenant data isolation: a single database serves many customers (tenants), each seeing only their own data.

Schema Setup

sql
CREATE TABLE tenants (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE projects (
  id         BIGSERIAL PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id),
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE tasks (
  id         BIGSERIAL PRIMARY KEY,
  project_id BIGINT NOT NULL REFERENCES projects(id),
  tenant_id  BIGINT NOT NULL,  -- denormalized for simpler RLS
  title      TEXT NOT NULL,
  done       BOOLEAN DEFAULT false
);

-- Enable RLS on tenant-scoped tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks    ENABLE ROW LEVEL SECURITY;

Tenant Isolation Policies

sql
-- Projects: each tenant sees only their projects
CREATE POLICY tenant_isolation_projects
ON projects
FOR ALL
TO app_service
USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

-- Tasks: same pattern
CREATE POLICY tenant_isolation_tasks
ON tasks
FOR ALL
TO app_service
USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

Now in your application:

sql
-- After authenticating the request and identifying the tenant:
SET LOCAL app.tenant_id = '7';

-- This query automatically returns only tenant 7's projects
SELECT * FROM projects;

-- This INSERT automatically fails if it tries to insert into another tenant
INSERT INTO projects (tenant_id, name) VALUES (7, 'New Project');   -- OK
INSERT INTO projects (tenant_id, name) VALUES (99, 'Hack attempt'); -- ERROR: policy violation

The database enforces isolation. Even if a bug in application code constructs a query that could read another tenant's data, the RLS policy blocks it at the database level.

PERMISSIVE vs RESTRICTIVE Policies

By default all policies are PERMISSIVE: a row is accessible if any policy allows it.

sql
-- PERMISSIVE (default): OR logic
-- A row is visible if policy_1 allows it OR policy_2 allows it
CREATE POLICY policy_1 ON posts USING (...);
CREATE POLICY policy_2 ON posts USING (...);

RESTRICTIVE policies use AND logic — a row is accessible only if it passes all restrictive policies (plus at least one permissive policy if any permissive policies exist).

sql
-- RESTRICTIVE: AND logic
-- A row is visible only if it passes this AND all other policies
CREATE POLICY soft_delete_filter
ON posts
AS RESTRICTIVE
FOR SELECT
USING (deleted_at IS NULL);

This is the right pattern for soft deletes. You want users to see their own rows (permissive policy), but never deleted rows regardless of other policies (restrictive policy).

The BYPASSRLS Attribute

Table owners bypass RLS by default. Superusers bypass it. You can also grant bypass explicitly:

sql
-- Admin role that sees all rows regardless of policies
CREATE ROLE admin_service WITH LOGIN BYPASSRLS PASSWORD '...';

Use this for:

• Migration scripts that need to operate on all rows
• Admin dashboards that serve internal users seeing cross-tenant data
• Backup/restore operations

Be careful: BYPASSRLS is a powerful attribute. Keep it to dedicated admin roles, not your main application role.

Viewing and Managing Policies

sql
-- List all policies in the database
SELECT schemaname, tablename, policyname, cmd, roles, qual, with_check
FROM pg_policies;

-- psql shorthand for a specific table
\d+ posts

-- Drop a policy
DROP POLICY posts_select_own ON posts;

-- Disable RLS (keeps policies defined, but stops enforcing them)
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

-- Force RLS even for the table owner
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY is useful in testing — it makes the table owner subject to policies too, so you can verify your policies behave correctly without switching roles.

Common Mistakes

Forgetting WITH CHECK If you define a USING clause without a WITH CHECK, PostgreSQL uses the USING expression for both reads and writes. For SELECT-only policies this is fine. For INSERT/UPDATE policies, always think about what the WITH CHECK should be separately.

Enabling RLS without any policies The default-deny behaviour (no rows visible) surprises people. If you enable RLS and forget to add a policy, the table appears empty to all non-owner roles. This can cause silent failures.

Using session variables without SET LOCAL SET app.tenant_id = '7' persists for the entire connection. In a connection pool, the next request on the same connection will see the previous tenant's context. Always use SET LOCAL inside a transaction, or set_config('app.tenant_id', '7', true) (the third argument true means "local to this transaction").

sql
-- Safe for connection pools: reverts at COMMIT/ROLLBACK
SELECT set_config('app.tenant_id', '7', true);

Not enabling RLS on every table in the schema A multi-tenant schema where only some tables have RLS is a leaking tenant boundary. Audit every table.

Practical Exercise

Build the multi-tenant RLS setup from this module:

sql
-- 1. Create the roles
CREATE ROLE myapp_owner WITH LOGIN PASSWORD 'owner_pw';
CREATE ROLE myapp_app   WITH LOGIN NOINHERIT PASSWORD 'app_pw';

-- 2. Schema and tables
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION myapp_owner;

SET search_path = app;

CREATE TABLE tenants (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE notes (
  id        BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT NOT NULL REFERENCES tenants(id),
  content   TEXT NOT NULL
);

-- 3. Privileges
GRANT USAGE ON SCHEMA app TO myapp_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.notes TO myapp_app;
GRANT SELECT ON app.tenants TO myapp_app;

-- 4. Enable RLS
ALTER TABLE app.notes ENABLE ROW LEVEL SECURITY;

-- 5. Policy
CREATE POLICY tenant_isolation
ON app.notes
FOR ALL
TO myapp_app
USING      (tenant_id = current_setting('app.tenant_id')::BIGINT)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

-- 6. Seed data
INSERT INTO app.tenants (name) VALUES ('Acme Corp'), ('Globex Inc');
INSERT INTO app.notes (tenant_id, content) VALUES
  (1, 'Acme private note'),
  (2, 'Globex private note');

-- 7. Test as myapp_app
SET ROLE myapp_app;
SELECT set_config('app.tenant_id', '1', true);

SELECT * FROM app.notes;
-- Only returns: (1, 1, 'Acme private note')

SELECT set_config('app.tenant_id', '2', true);
SELECT * FROM app.notes;
-- Only returns: (2, 2, 'Globex private note')

RESET ROLE;

The database enforces the tenant boundary. The application never needs to add WHERE tenant_id = ? to individual queries — and even if it forgets to, the RLS policy ensures no data leaks.

Summary

PostgreSQL's access control system has two layers:

Roles and privileges control which roles can perform which operations on which objects — table-level granularity. The right production pattern is a role hierarchy: owner for DDL, app role for DML, reader role for SELECT, and no superuser connections from application code.

Row-Level Security adds row-level granularity using policies that are automatically applied to every query. The database enforces data isolation rules that would otherwise need careful application-layer implementation in every query. For multi-tenant systems, RLS is the cleanest way to prevent data leaks between tenants.

Next up: P-7 — Full-Text Search — where we replace Elasticsearch with native PostgreSQL text search using tsvector, tsquery, GIN indexes, and relevance ranking.