Dev DB Setup for Enforced RLS

A one-time-per-developer setup to get your local dev DB into the same state as staging and production: app role is non-superuser, non-bypassrls, but still owns its tables so migrations work. This is what catches RLS bugs in dev instead of staging — see row-level-security.md §7.4 for why we care.

Two paths depending on whether you can afford to recreate your local DB. Pick whichever matches your situation.

Companion reading: row-level-security.md for the full RLS model. The classification rule (§2.4), migration pattern (§2.5), and audit query (§6.5) are the load-bearing references.

Decide: which path are you on?Copy Anchor Link

psql -d kulcare_development -c "SELECT current_user;"

• Output is your own username (e.g. shantanu, madhav) — you've been connecting as a personal superuser. The dev DB's tables are likely owned by you and RLS has been silently bypassed for as long as you've worked here. → Path A or Path B.

• Output is kulcare_dev with non-superuser attributes — you're already on the target setup. Check with SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname='kulcare_dev'; — if both are false, you're done.

# If kulcare_dev doesn't exist yet:
psql -d kulcare_development -c "SELECT 1 FROM pg_roles WHERE rolname='kulcare_dev';" | grep -q 1 || echo "kulcare_dev role missing — create it via Path A step 1 or Path B"

Path A — Migrate in place (keep your data)Copy Anchor Link

For devs who have dev data they want to preserve and don't want to re-run seeds. ~5-10 minutes, mostly waiting for ownership transfers.

A1. Create the kulcare_dev role if it doesn't existCopy Anchor Link

-- As a superuser (your personal username, or postgres):
\c postgres

CREATE ROLE kulcare_dev LOGIN PASSWORD 'kulcare_dev' NOSUPERUSER NOBYPASSRLS INHERIT;

The password matches backend/config/database.yml's development section (username: kulcare_dev, password: kulcare_dev).

A2. Switch to the dev DB and transfer ownershipCopy Anchor Link

\c kulcare_development

-- Schema ownership
DO $$
DECLARE s record;
BEGIN
  FOR s IN
    SELECT nspname FROM pg_namespace
    WHERE nspname NOT IN ('pg_catalog','information_schema','pg_toast')
      AND nspname NOT LIKE 'pg_temp_%'
      AND nspname NOT LIKE 'pg_toast_temp_%'
  LOOP
    EXECUTE format('ALTER SCHEMA %I OWNER TO kulcare_dev', s.nspname);
  END LOOP;
END $$;

-- Object ownership (tables, sequences, indexes, views, materialized views, partitioned tables)
-- Find current owners first:
SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner, COUNT(*) AS objects
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','S','i','v','m','p')
  AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
GROUP BY pg_get_userbyid(c.relowner)
ORDER BY owner;

For each row that's NOT kulcare_dev, run:

REASSIGN OWNED BY <owner> TO kulcare_dev;
-- example: REASSIGN OWNED BY shantanu TO kulcare_dev;

REASSIGN OWNED cascades through tables, sequences, indexes, views, materialized views, partitioned tables, and the policies/triggers attached to them. Run it once per former owner.

Hitch: REASSIGN OWNED BY <yourself> errors if you're connected as that user. Connect as postgres (or another superuser) and run it there. If you're the only superuser, ALTER ROLE <you> NOSUPERUSER would lock you out before you can REASSIGN — temporarily create a second super:

Copy Text

CREATE ROLE temp_admin SUPERUSER LOGIN PASSWORD '<random>';
-- Reconnect as temp_admin, run REASSIGN, then DROP ROLE temp_admin when done

A3. Grant CREATE on the database (so migrations can CREATE SCHEMA)Copy Anchor Link

GRANT CREATE ON DATABASE kulcare_development TO kulcare_dev;

A4. Confirm FORCE ROW LEVEL SECURITY is set on every RLS'd tableCopy Anchor Link

The owner bypasses RLS by default; only FORCE makes RLS apply to the owner too. Find any tables with RLS enabled but not forced — they're the bypass holes:

SELECT n.nspname || '.' || c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relrowsecurity = true
  AND c.relforcerowsecurity = false
ORDER BY n.nspname, c.relname;

For each row returned, fix it:

ALTER TABLE <schema>.<table> FORCE ROW LEVEL SECURITY;

If this list is empty, you're aligned with PR #1480's audit.

A5. Verify the four invariantsCopy Anchor Link

-- Role attributes
SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname='kulcare_dev';
-- expected: (kulcare_dev, f, f)

-- Database-level CREATE
SELECT has_database_privilege('kulcare_dev', 'kulcare_development', 'CREATE') AS can_create;
-- expected: t

-- Schema ownership
SELECT COUNT(*) FILTER (WHERE pg_get_userbyid(nspowner) <> 'kulcare_dev') AS non_kulcare_dev_owned
FROM pg_namespace
WHERE nspname NOT IN ('pg_catalog','information_schema','pg_toast')
  AND nspname NOT LIKE 'pg_temp_%'
  AND nspname NOT LIKE 'pg_toast_temp_%';
-- expected: 0

-- RLS still filters under the new owner
SET ROLE kulcare_dev;
RESET app.current_tenant_id;
SELECT COUNT(*) FROM identity.facilities;
-- expected: 0 (RLS filtering with no GUC)
RESET ROLE;

If all four pass → run bin/rails db:migrate and verify it succeeds. Then start the Rails server and exercise the login flow. You should see the same behavior staging now exhibits.

Path B — Recreate the dev DBCopy Anchor Link

For devs who don't have data worth preserving, or whose dev DB is messy enough that a fresh setup is faster. ~2-5 minutes.

B1. Create kulcare_dev role (skip if it exists)Copy Anchor Link

psql -d postgres -c "CREATE ROLE kulcare_dev LOGIN PASSWORD 'kulcare_dev' NOSUPERUSER NOBYPASSRLS INHERIT;" 2>/dev/null || echo "kulcare_dev role already exists"

B2. Drop + recreate the DB owned by kulcare_devCopy Anchor Link

# Drop existing dev DB (DESTRUCTIVE — confirms you don't need the data)
psql -d postgres -c "DROP DATABASE IF EXISTS kulcare_development;"

# Create with kulcare_dev as the owner
psql -d postgres -c "CREATE DATABASE kulcare_development OWNER kulcare_dev;"

# Grant CREATE on database (REASSIGN won't fire because the DB starts empty)
psql -d postgres -c "GRANT CREATE ON DATABASE kulcare_development TO kulcare_dev;"

B3. Run migrationsCopy Anchor Link

cd backend
bin/rails db:migrate db:seed

Every schema and table created by migrations will be owned by kulcare_dev (since db:migrate runs as kulcare_dev per database.yml). RLS-related migrations (20260520160001 and any future ones following §2.5 of the RLS guide) include FORCE ROW LEVEL SECURITY, so the owner-bypass hole stays closed by default.

B4. Verify (same as Path A step 5)Copy Anchor Link

\c kulcare_development

SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname='kulcare_dev';
-- expected: (kulcare_dev, f, f)

SET ROLE kulcare_dev;
RESET app.current_tenant_id;
SELECT COUNT(*) FROM identity.facilities;
-- expected: 0 (the seed data exists; RLS filters because no GUC)
RESET ROLE;

If both pass, you're aligned with staging.

What to expect once you're on the new setupCopy Anchor Link

Symptoms you'll see if you have an old code path that hits RLS'd tables without an active tenant GUC:

• PG::InsufficientPrivilege — you tried to CREATE TABLE or ALTER TABLE something kulcare_dev doesn't own. Either the table needs to be reassigned to kulcare_dev (Path A step 2 missed it) or the migration is touching a table outside the app surface.
• Zero-row reads from tenant-scoped tables when you expected data — the GUC isn't set. Pre-tenant-context reads need RlsContext.with(tenant_id) { ... } wraps. See §5.7.
• new row violates row-level security policy for table "X" — a write went through with a tenant_id that doesn't match the GUC. See §4.2 and §5.6.

These are not bugs in your environment — they're the bugs PR #1480 surfaced. Same playbook as staging if you find new ones: trace the call site, wrap in RlsContext.with(target_tenant_id), or reclassify the table (rare, see §2.4).

When you add a new tenant-scoped tableCopy Anchor Link

Every migration creating a tenant-scoped table must include the full RLS pattern, not just ENABLE:

execute "ALTER TABLE <schema>.<table> ENABLE ROW LEVEL SECURITY;"
execute "ALTER TABLE <schema>.<table> FORCE ROW LEVEL SECURITY;"  # ← critical
execute <<~SQL
  CREATE POLICY tenant_isolation ON <schema>.<table>
    FOR ALL
    USING       (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    WITH CHECK  (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);
SQL

Without FORCE, kulcare_dev (the table owner in the new setup) silently bypasses the policy in dev — bug shows up only in staging/prod. See §2.5 for the full rationale.

Writing RLS-faithful specs (the :rls_enforced helper)Copy Anchor Link

The dev / test DB user (shantanu on most laptops, kulcare_dev on a freshly-provisioned machine) typically has either rolsuper = t or rolbypassrls = t. RSpec specs running under that user silently DO NOT enforce RLS — a spec can pass while the production code path is broken under the non-bypass kulcare_staging role. Exactly the bug class that snuck PR #1384's original raw-set_config implementation past local testing.

To write a spec that exercises FORCE ROW LEVEL SECURITY the way production does, use the block-scoped helper added in #1500:

# Fixture setup runs as the test owner (RLS bypassed) — much easier
# to seed cross-tenant data without wrapping each insert in RlsContext.
let!(:tenant_a) { Identity::Tenant.find_or_create_by!(slug: 'a') { |t| ... } }
let!(:tenant_b) { Identity::Tenant.find_or_create_by!(slug: 'b') { |t| ... } }

it 'exercises the controller under enforced RLS' do
  with_rls_enforced_role do
    get '/some/endpoint'
  end
  expect(response).to ...
end

For specs whose entire purpose is to assert RLS enforcement on writes (no cross-tenant fixture setup), use the whole-example mode:

it 'rejects inserts without a tenant GUC', :rls_enforced do
  expect {
    Identity::Facility.create!(name: 'X', tenant: t, ...)
  }.to raise_error(/row-level security/)
end

One-time provisioningCopy Anchor Link

The helper relies on a kulcare_rls_test role with DML grants on every app schema. Provision once on dev:

bundle exec rake db:rls_test_role:provision

And on the test DB:

RAILS_ENV=test bundle exec rake db:rls_test_role:provision

The task is idempotent (safe to re-run). It mirrors staging's posture: the role has NOSUPERUSER + NOBYPASSRLS + DML grants on all app schemas (identity, clinical, shared, billing, etc.), so opting into the helper exercises the exact RLS path production uses.

If you haven't provisioned the role, specs using the helper skip with a clear message rather than fail. Safe to import the helper everywhere; only spec authors who opt in via metadata or with_rls_enforced_role { } see any effect.

The helper module + self-tests live at:

• backend/domains/shared/spec/support/rls_enforced.rb
• backend/domains/shared/spec/rls_enforced_spec.rb

Help and rollbackCopy Anchor Link

If anything breaks irreversibly during Path A, restore the prior state:

-- As another superuser:
ALTER ROLE kulcare_dev SUPERUSER;
-- Or restore your personal user as the table owner:
REASSIGN OWNED BY kulcare_dev TO <your_username>;

Then bin/rails db:migrate should work again, RLS is bypassed as before, and you can retry once you've understood what went wrong.

Path B is itself a rollback — if it fails, drop the DB again and try Path A on a fresh clone of your colleague's working dev DB.