Module P-6·24 min read

Roles, privileges, Row-Level Security policies, and multi-tenant data isolation — the production access model.

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

Q: A multi-tenant SaaS application uses PostgreSQL Row-Level Security (RLS) to isolate tenant data. The application uses a connection pool (like PgBouncer). A developer sets the tenant context using `SET app.tenant_id = '42';` right before executing a query. Why is this a critical security vulnerability?

`SET` modifies the session state persistently. In a connection pool, the connection is returned to the pool without resetting, causing the next random user who checks out that connection to see tenant 42's data. — Using `SET` modifies the session-level configuration. When using a connection pool, connections are reused across different requests and different users. If you use `SET` and do not manually reset the value before returning the connection to the pool, the next request will inherit that tenant ID, leading to massive cross-tenant data leaks. The correct and secure pattern is to use `SET LOCAL app.tenant_id = '42';` inside a transaction block (`BEGIN; ... COMMIT;`), or `SELECT set_config('app.tenant_id', '42', true);`, which automatically resets the parameter when the transaction ends. Options A, B, and D are factually incorrect.

Q: You have enabled RLS on a `posts` table (`ALTER TABLE posts ENABLE ROW LEVEL SECURITY;`) but you have not yet created any policies for it. The table is owned by the `admin_role`. Your application connects using the `app_service` role, which has been granted `SELECT, INSERT, UPDATE, DELETE` privileges on the table. What happens when the `app_service` role runs `SELECT * FROM posts;`?

It returns zero rows (an empty result set) because enabling RLS applies a "default-deny" rule for all non-owner roles until a permissive policy is created. — This is a fundamental concept of RLS: the moment you enable it on a table, PostgreSQL applies a default-deny stance for all roles *except* the table owner (and superusers/roles with `BYPASSRLS`). Even if a role has standard table-level `SELECT` privileges, if there are no RLS policies granting them access to specific rows, the database will silently filter out everything, returning an empty result set. Option A is incorrect because RLS doesn't throw errors for missing policies; it just filters the rows. Option B is how standard table privileges work without RLS. Option D is a made-up attribute.

Q: When designing a robust role architecture for a production PostgreSQL database, what is the primary purpose of the `ALTER DEFAULT PRIVILEGES` command?

To ensure that when the migration user (schema owner) creates new tables in the future, the application and reader roles automatically receive the correct table-level privileges (like `SELECT` or `INSERT`) without requiring manual `GRANT` statements. — `ALTER DEFAULT PRIVILEGES` solves a very specific operational headache. A standard `GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_role;` only applies to tables that *currently* exist at the moment the command is run. Without `ALTER DEFAULT PRIVILEGES`, the next time your CI/CD pipeline runs a migration to create a new table, your application will crash with a "permission denied" error until someone manually runs another `GRANT` command. `ALTER DEFAULT PRIVILEGES` tells PostgreSQL: "Whenever *this specific role* creates a new table in the future, automatically grant these privileges to *these other roles*."

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:

Attribute	Meaning
`LOGIN`	Can authenticate and open a session
`SUPERUSER`	Bypasses all access checks — avoid in production
`CREATEDB`	Can create new databases
`CREATEROLE`	Can create other roles
`REPLICATION`	Used for streaming replication connections
`BYPASSRLS`	Bypasses Row-Level Security policies
`PASSWORD 'x'`	Sets the login password

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.

Knowledge Check

A multi-tenant SaaS application uses PostgreSQL Row-Level Security (RLS) to isolate tenant data. The application uses a connection pool (like PgBouncer). A developer sets the tenant context using SET app.tenant_id = '42'; right before executing a query. Why is this a critical security vulnerability?

You have enabled RLS on a posts table (ALTER TABLE posts ENABLE ROW LEVEL SECURITY;) but you have not yet created any policies for it. The table is owned by the admin_role. Your application connects using the app_service role, which has been granted SELECT, INSERT, UPDATE, DELETE privileges on the table. What happens when the app_service role runs SELECT * FROM posts;?

When designing a robust role architecture for a production PostgreSQL database, what is the primary purpose of the ALTER DEFAULT PRIVILEGES command?

Test your knowledge with more question sets

PreviousModule P-5: JSON and JSONB — Working With Semi-Structured Data Next Module P-7: Full-Text Search

Discussion

Join the discussion

Loading comments...