PushizoDocs
OperationsRunbooks

Postgres role separation (keeping RLS effective in production)

The second line of defense for tenant isolation is Postgres Row Level Security (db/migrations/0005_rls.up.sql, FORCE ROW LEVEL SECURITY). Superusers and BYPASSRLS roles are never subject to RLS; if the application…

The second line of defense for tenant isolation is Postgres Row Level Security (db/migrations/0005_rls.up.sql, FORCE ROW LEVEL SECURITY). Superusers and BYPASSRLS roles are never subject to RLS under any circumstances; if the application connects as a superuser, the policies are silently disabled. That is why production uses three roles:

RolePrivilegesUsed by
postgressuperuseradministration only: backup.sh, restore.sh, extension installation
pushizoNOSUPERUSER NOBYPASSRLS NOCREATEDB NOCREATEROLE, owner of the database and the public schemaAPI, worker, migrations (DATABASE_URL)
pushizo_exporterpg_monitor + SELECT on tables; app.rls_bypass=on at the role levelpostgres-exporter (infra/monitoring/postgres-exporter/queries.yaml only counts totals)

Application connections open with app.rls_bypass=on by default (migrations, worker, guard queries); for tenant requests the API enables the policies with SET LOCAL app.rls_bypass = off (TenantRlsInterceptor). This setting is a second line of defense against application bugs, not against malicious SQL.

New installation (compose)

infra/compose/postgres/initdb/10-roles.sh runs automatically on an empty data directory. Three passwords are required in .env.prod: POSTGRES_PASSWORD (application role), POSTGRES_SUPERUSER_PASSWORD, POSTGRES_EXPORTER_PASSWORD. Then run deploy.sh as usual (migrations run as the pushizo role, which owns the tables).

Converting an existing installation (data directory already exists)

The old compose setup created a superuser with POSTGRES_USER=pushizo. During a maintenance window:

docker compose --env-file .env.prod -f infra/compose/docker-compose.prod.yml exec -T postgres psql -U pushizo -d pushizo -v ON_ERROR_STOP=1 \
  -v su_password="$POSTGRES_SUPERUSER_PASSWORD" -v exporter_password="$POSTGRES_EXPORTER_PASSWORD" <<'SQL'
create role postgres login superuser password :'su_password';
create extension if not exists pg_stat_statements;
create role pushizo_exporter login nosuperuser nobypassrls password :'exporter_password';
grant pg_monitor to pushizo_exporter;
grant connect on database pushizo to pushizo_exporter;
grant usage on schema public to pushizo_exporter;
grant select on all tables in schema public to pushizo_exporter;
alter default privileges for role pushizo in schema public grant select on tables to pushizo_exporter;
alter role pushizo_exporter set app.rls_bypass = 'on';
SQL
# Superuser privileges are revoked using the new `postgres` role:
docker compose --env-file .env.prod -f infra/compose/docker-compose.prod.yml exec -T postgres psql -U postgres -d pushizo \
  -c "alter role pushizo nosuperuser nobypassrls nocreatedb nocreaterole"

Then run deploy.sh prod with the new compose file (postgres-exporter starts with the new role).

Verification

docker compose --env-file .env.prod -f infra/compose/docker-compose.prod.yml exec -T postgres psql -U postgres -d pushizo -tAc \
  "select rolname, rolsuper, rolbypassrls from pg_roles where rolname like 'pushizo%'"

Expected: pushizo|f|f and pushizo_exporter|f|f. At the table level, select relname from pg_class where relkind in ('r','p') and relrowsecurity and not relforcerowsecurity must return no rows.

Kubernetes / managed Postgres

The Helm chart does not include the database (the DATABASE_URL secret). On managed services too, the application user must not be a superuser or have BYPASSRLS (e.g. on RDS, do not grant rds_superuser membership); the application user owns the database and the public schema. For the exporter, apply the pushizo_exporter definition above as is.

On this page