Roles, permissions, schemas and row-level security

GRANT and REVOKE, role memberships, default privileges, search_path safety, row-level security policies and extension management.

Roles and grants

create role app_read noinherit login password '...';
create role app_write noinherit login password '...';
create role app_owner noinherit login password '...';

grant connect on database appdb to app_read, app_write;
grant usage on schema public to app_read, app_write;

-- least privilege: readers only read, writers only modify
grant select on all tables in schema public to app_read;
grant select, insert, update, delete on all tables in schema public to app_write;
grant usage on all sequences in schema public to app_write;

-- but new tables do not inherit those grants: set the default
alter default privileges for role app_owner in schema public
  grant select on tables to app_read;
alter default privileges for role app_owner in schema public
  grant select, insert, update, delete on tables to app_write;

-- the application should not own its own schema
alter table invoice owner to app_owner;

• noinherit means a member does not automatically inherit the group's privileges - useful when you want an explicit set role switch.
• Grants on existing objects do not apply to future ones. alter default privileges is the fix, and it must be run by the role that will create the objects.
• Revoke create on the public schema from public: in PostgreSQL 15+ it is revoked by default, but managed services and older clusters may still allow it.
• Never let the application connect as the superuser or as the schema owner: a SQL injection then owns the database.

Row-level security

alter table document enable row level security;
-- the owner and superusers bypass RLS unless forced
alter table document force row level security;

create policy document_select on document
  for select
  using (tenant_id = current_setting('app.tenant_id', true)::bigint);

create policy document_insert on document
  for insert
  with check (tenant_id = current_setting('app.tenant_id', true)::bigint);

create policy document_update on document
  for update
  using (tenant_id = current_setting('app.tenant_id', true)::bigint)
  with check (tenant_id = current_setting('app.tenant_id', true)::bigint);

-- the application sets the tenant per connection or per transaction
-- set local app.tenant_id = '42';

-- use a SECURITY DEFINER function to grant a narrow bypass
create or replace function admin_document_count()
returns bigint
language sql
security definer
set search_path = app, pg_catalog
as $$
  select count(*) from document;
$$;

revoke all on function admin_document_count() from public;
grant execute on function admin_document_count() to app_read;

RLS is a strong second line of defence against a forgotten where tenant_id = ..., but it is not free: every query carries the predicate, and a poorly indexed policy column turns every read into a sequential scan. Index tenant_id on every protected table.

Extensions and schemas

create schema if not exists app;
create schema if not exists extensions;
alter database appdb set search_path = app, public;

create extension if not exists pgcrypto with schema extensions;
create extension if not exists "uuid-ossp" with schema extensions;

select e.extname, n.nspname, e.extversion
from pg_extension e join pg_namespace n on n.oid = e.extnamespace
order by 1;

-- usually only a superuser or a granted role may install extensions
grant create on database appdb to migration_role;

• Install extensions into a dedicated schema so they do not pollute public and can be upgraded independently.
• Creating an extension usually requires superuser; on managed services only an allowlisted set is available.
• Enabling an extension can change how existing expressions behave - test a major version upgrade on a copy of production.
• pg_stat_statements is the one extension almost every production database should have enabled before tuning anything.

FAQ

Related

Partitioning, extensions and advanced deployment Functions, procedures and PL/pgSQL triggers

Last refreshed 2026-09-18.