Running without a Postgres superuser

Managed Postgres providers (RDS, Cloud SQL, Azure Database, Neon, Supabase) do not hand out a true SUPERUSER role. Orvanta’s shipped defaults — the docker-compose.yml, the .env example, and the Helm chart’s databaseUrl — all connect as the postgres cluster superuser, which hides the question of what is actually required.

This page separates the two:

• a one-time setup step that genuinely requires elevated rights, and
• the day-to-day connection, which does not need a superuser at all.

The short answer

Orvanta cannot bootstrap a brand-new database entirely without a superuser. Two Postgres roles must exist before the first migration runs, and creating one of them requires a superuser. Once that one-time step is done, Orvanta runs perfectly well as an ordinary, non-superuser login role.

If your provider gives you a “near-superuser” administrative role (rds_superuser, cloudsqlsuperuser, azure_pg_admin), that role can usually perform the setup step — see Managed Postgres providers below.

Why two roles exist

Orvanta uses PostgreSQL row-level security to enforce the per-path permissions behind u/<user>/…, g/<group>/… and folder grants. Around forty tables have RLS enabled, and every authenticated transaction switches into one of two roles before touching them:

Role	Used for
orvanta_user	Ordinary authenticated requests. RLS policies apply.
orvanta_admin	Requests from workspace admins and superadmins. Covered by an explicit permissive policy on every RLS table.

The switch happens inside a set_session_context(...) function, which issues SET LOCAL ROLE orvanta_admin or SET LOCAL ROLE orvanta_user at the start of each user-scoped transaction.

This is a real boundary, and it is why the roles have to exist: orvanta_user is neither a superuser nor BYPASSRLS, so inside such a transaction the database itself withholds rows the caller has no permission on.

What row-level security does not protect

Two limits are worth knowing before you plan around them, because both are easy to over-read from the setup above.

It does not contain your DATABASE_URL credential. The login role must own the schema — that is what lets it run migrations, and the setup SQL makes it the owner deliberately. In PostgreSQL a table owner bypasses RLS unless the table sets FORCE ROW LEVEL SECURITY, and no Orvanta table does. That is by design rather than an oversight: Orvanta’s background work (the job queue, schedulers, trigger listeners) connects on an ordinary pool that never calls set_session_context, and the policies read current_setting('session.user'). Switching FORCE on would make every one of those reads and writes fail outright with unrecognized configuration parameter "session.user" — not deny quietly, but stop the instance. Do not add it.

So treat RLS as defence in depth with respect to that credential, never as containment of it. Protect the credential on its own terms: restrict which hosts may reach the database, keep the password in a secret store, and rotate it.

It does not separate workspaces. No policy compares a row’s workspace to the caller’s — the policies express per-path permissions, not tenancy. Workspace separation is enforced by the application, on every query, on both connection paths. Running as a non-superuser changes neither of these things; it narrows the blast radius of the credential (no COPY … PROGRAM, no arbitrary role changes, no reaching other databases on the same server), which is worth having on its own merits.

This has a consequence that is easy to miss: the login role in your DATABASE_URL must be a member of both roles. A superuser can SET ROLE to anything, so this requirement is invisible on a default install. A non-superuser login role that is not a member of them will fail with permission denied to set role on every authenticated request, even though migrations completed and the service started.

What genuinely requires a superuser

1. Creating orvanta_admin

orvanta_admin is created with the BYPASSRLS attribute:

CREATE ROLE orvanta_admin WITH BYPASSRLS;

Only a superuser may set BYPASSRLS. CREATEROLE is not sufficient — this is a PostgreSQL restriction, not an Orvanta one, and there is no way around it from inside the application.

2. Granting role membership to your login role

Making your application’s login role a member of orvanta_user and orvanta_admin requires either a superuser or ADMIN OPTION on those roles. Since the roles are being created in the same step, this is part of the same one-time superuser task.

Why the migrations do not do this for you

Orvanta’s early migrations do contain CREATE ROLE statements, but they are wrapped in EXCEPTION WHEN OTHERS THEN RAISE NOTICE blocks, so they never abort a migration run. On a non-superuser connection they fail silently. Some of those blocks also begin with LOCK TABLE pg_catalog.pg_roles, which a non-superuser cannot execute — so the whole block, including the GRANT statements that follow it, is skipped.

The failure therefore does not surface where you would expect. A later migration grants privileges to those roles without an exception guard:

GRANT ALL PRIVILEGES ON TABLE usage TO orvanta_admin;
GRANT ALL PRIVILEGES ON TABLE usage TO orvanta_user;

If the roles do not exist, this raises 42704 role "orvanta_admin" does not exist and boot aborts. Roughly eighty-five migration files reference these role names, so there is no partial-success path.

Caution

If you see role "orvanta_admin" does not exist during startup, the cause is that the one-time superuser step below was never run — not a corrupt migration state. Run the setup SQL and restart; the migration chain resumes from where it stopped.

One-time setup (run as superuser)

Run this once, as a superuser or a provider-supplied administrative role, before starting Orvanta for the first time.

-- 1. The login role Orvanta will connect as, and its database.
--    Orvanta never issues CREATE DATABASE for its own database.
CREATE ROLE orvanta LOGIN PASSWORD 'a-strong-password';
CREATE DATABASE orvanta OWNER orvanta;

-- 2. The two RLS roles. BYPASSRLS is the superuser-only part.
CREATE ROLE orvanta_user;
CREATE ROLE orvanta_admin WITH BYPASSRLS;
GRANT orvanta_user TO orvanta_admin;

-- 3. Membership. Without this, every authenticated request fails
--    with "permission denied to set role".
GRANT orvanta_user, orvanta_admin TO orvanta WITH ADMIN OPTION;

-- 4. Schema rights. PostgreSQL 15 revoked CREATE on the public schema
--    from PUBLIC, so this is required on 15.x and later.
\c orvanta
ALTER SCHEMA public OWNER TO orvanta;
GRANT USAGE ON SCHEMA public TO orvanta_user, orvanta_admin;

Then point Orvanta at it as an ordinary role:

DATABASE_URL=postgres://orvanta:a-strong-password@your-host:5432/orvanta?sslmode=require

WITH ADMIN OPTION in step 3 is worth including: a later migration attempts GRANT orvanta_user TO CURRENT_USER as a self-heal, which only succeeds if the login role holds admin option on those roles.

Note

The repository contains an init-db-as-superuser.sql file. It is a partial version of the above — it creates the two roles but does not grant them to your login role, which is the step that non-superuser deployments actually need. Use the SQL on this page instead.

What a non-superuser can do

Everything else in the install and boot path works as an ordinary role that owns its schema objects. Ownership, not superuser, is the axis that matters for almost all of Orvanta’s DDL:

• All CREATE TABLE, CREATE INDEX, CREATE FUNCTION, CREATE TRIGGER
• ALTER TABLE ... ENABLE ROW LEVEL SECURITY and CREATE POLICY — these require table ownership, not superuser
• SECURITY DEFINER functions — these require function ownership only
• ALTER DEFAULT PRIVILEGES
• pg_advisory_lock / pg_try_advisory_lock, used for the migration lock
• Per-connection SET of statement_timeout, enable_seqscan, idle_in_transaction_session_timeout and TCP keepalives — all user-settable parameters
• Runtime VACUUM of the job tables, and creation and dropping of daily audit-log partitions by the background monitor

Those last two matter for a long-running instance: the monitor creates audit_YYYYMMDD partitions ahead of time and vacuums the job tables. Both need ownership of the relevant tables, which the migrating role has because it created them. If you restore a database dump under a different owner, re-check ownership.

CREATEDB is not required

Orvanta is given a database; it does not create its own. CREATE DATABASE appears only in optional features (see Limitations).

Extensions

Extension	Required?	Notes
uuid-ossp	No	Present in the oldest migration, but the application binary strips the CREATE EXTENSION statement before applying it. Nothing calls uuid_generate_*; all UUIDs use the built-in gen_random_uuid().
pgcrypto	No	Not used anywhere.
vector (pgvector)	No	Optional. Not a trusted extension, so it does need a superuser — but its migration is deliberately exception-wrapped and will not fail your boot.
pg_stat_statements	No	Probed by the health endpoint if present; never created.

So on a managed instance where you cannot CREATE EXTENSION, the migration chain still completes. pgvector simply stays unavailable.

Do not run sqlx migrate run directly

The uuid-ossp statement is removed by the application’s own migrator. Running sqlx migrate run by hand — as the platform’s developer README describes — bypasses that rewriting and will issue the raw CREATE SCHEMA extensions and CREATE EXTENSION "uuid-ossp", both of which a restricted role may be unable to execute. On a non-superuser deployment, always let the Orvanta binary run its own migrations on startup.

Limitations on a non-superuser instance

These optional features perform privileged operations at runtime and will not work if the login role lacks the rights. None of them affect boot.

Feature	What it needs	Effect if unavailable
Data Tables / DuckLake custom instance databases	CREATE DATABASE, CREATE USER, and ALTER ROLE ... CREATEROLE / ... REPLICATION on a custom_instance_user role — the latter two are superuser-only	Creating a custom instance database from instance settings fails with a permission error. The rest of Orvanta is unaffected.
Workspace forking to a new database	CREATE DATABASE on the target Postgres	Fork fails. Note this runs against a user-supplied Postgres resource, not Orvanta’s own database.
pgvector-backed features	CREATE EXTENSION vector	Vector features report unavailable; migrations still succeed.

Postgres triggers are a different database

Do not confuse this page with the Postgres triggers feature. That feature connects to your own application database — a database Orvanta does not own — and uses logical replication. Its requirements are much heavier and are genuinely superuser-territory on the target database:

• wal_level = logical, which needs ALTER SYSTEM and a server restart
• the REPLICATION role attribute (or the provider equivalent, such as rds_replication)
• permission to create a logical replication slot
• CREATE PUBLICATION; a FOR ALL TABLES publication requires a superuser, while a table-scoped publication requires ownership of those tables

Nothing on this page changes those requirements, and running Orvanta’s own database as a restricted role neither helps nor hinders them.

Managed Postgres providers

The one-time setup needs an attribute (BYPASSRLS) that PostgreSQL reserves for superusers. Managed providers give you an administrative role — rds_superuser on AWS, cloudsqlsuperuser on Google Cloud SQL, azure_pg_admin on Azure — that is not a true superuser, and whether any given one may set BYPASSRLS depends on the provider and the engine version.

We have not tested Orvanta’s setup against each provider, so this page does not claim which ones work. Check yours directly, as your administrative user, in about ten seconds:

CREATE ROLE orvanta_bypassrls_probe WITH BYPASSRLS;
DROP ROLE orvanta_bypassrls_probe;

• Both statements succeed: your administrative role is sufficient. Run the setup SQL above as that user and carry on.
• The first fails with permission denied: your role cannot set BYPASSRLS. Ask your provider’s support to create orvanta_admin as a one-time action, run Orvanta against a PostgreSQL instance you control, or evaluate the NOBYPASSRLS variation described below — bearing in mind that it is unverified.

If you establish the answer for a provider, please tell us so this page can name it.

An untested variation

Every RLS-enabled table in Orvanta also carries an explicit permissive policy for orvanta_admin (FOR ALL TO orvanta_admin USING (true)), and there is a migration written specifically to remove reliance on BYPASSRLS. This strongly suggests orvanta_admin could be created NOBYPASSRLS — which needs no superuser — but we have not verified this end-to-end, and no automated test exercises that configuration. Treat it as an experiment to validate on a non-production instance, not a supported path. If you try it and it works for your workload, we would like to hear about it.

Verifying the setup

After the one-time SQL, before starting Orvanta:

-- Both roles exist, and orvanta_admin has the expected attribute.
SELECT rolname, rolbypassrls, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles
WHERE rolname IN ('orvanta', 'orvanta_user', 'orvanta_admin');

-- Your login role is a member of both.
SELECT r.rolname AS granted_role, m.admin_option
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
JOIN pg_roles g ON g.oid = m.member
WHERE g.rolname = 'orvanta';

Then, connected as the orvanta login role, confirm the role switch works:

SET ROLE orvanta_admin;
RESET ROLE;
SET ROLE orvanta_user;
RESET ROLE;

If either SET ROLE raises permission denied to set role, step 3 of the setup was missed.

Related

• Requirements: supported PostgreSQL versions and sizing.
• Configuration: DATABASE_URL and other environment variables.
• Postgres triggers: the separate, replication-based feature.