konnosDashboard
sandbox.constants.ts162 lines · main
1// ALTER ROLE postgres SUPERUSER succeeds because the bootstrap connection owns the cluster.
2// Each statement is individual so a single failure cannot abort the rest.
3export const SANDBOX_SETUP_STATEMENTS = [
4 `ALTER ROLE postgres SUPERUSER`,
5 `DO $$ BEGIN
6 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'anon') THEN
7 CREATE ROLE anon NOLOGIN;
8 END IF;
9 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'authenticated') THEN
10 CREATE ROLE authenticated NOLOGIN;
11 END IF;
12 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'service_role') THEN
13 CREATE ROLE service_role NOLOGIN;
14 END IF;
15 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'authenticator') THEN
16 CREATE ROLE authenticator NOLOGIN;
17 END IF;
18 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'dashboard_user') THEN
19 CREATE ROLE dashboard_user NOLOGIN;
20 END IF;
21 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'pgbouncer') THEN
22 CREATE ROLE pgbouncer NOLOGIN;
23 END IF;
24 IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'briven_admin') THEN
25 CREATE ROLE briven_admin NOLOGIN;
26 END IF;
27 END $$`,
28 `ALTER ROLE service_role BYPASSRLS`,
29 `GRANT anon TO postgres WITH ADMIN OPTION`,
30 `GRANT authenticated TO postgres WITH ADMIN OPTION`,
31 `GRANT service_role TO postgres WITH ADMIN OPTION`,
32 `GRANT CONNECT ON DATABASE postgres TO anon, authenticated, service_role`,
33 `CREATE SCHEMA IF NOT EXISTS auth`,
34 `GRANT USAGE ON SCHEMA auth TO anon, authenticated, service_role`,
35 `GRANT USAGE ON SCHEMA public TO anon, authenticated, service_role`,
36 // Read both the per-claim setting (request.jwt.claim.<name>) and the JSON blob
37 // (request.jwt.claims) so these work whether the caller uses Studio's role
38 // impersonation (sets the JSON blob) or PostgREST-style per-claim settings.
39 // Mirrors how the real Briven auth.* helpers are defined.
40 `CREATE OR REPLACE FUNCTION auth.uid() RETURNS uuid LANGUAGE sql STABLE AS
41 $fn$ SELECT COALESCE(
42 NULLIF(current_setting('request.jwt.claim.sub', true), ''),
43 (NULLIF(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
44 )::uuid $fn$`,
45 `CREATE OR REPLACE FUNCTION auth.role() RETURNS text LANGUAGE sql STABLE AS
46 $fn$ SELECT COALESCE(
47 NULLIF(current_setting('request.jwt.claim.role', true), ''),
48 (NULLIF(current_setting('request.jwt.claims', true), '')::jsonb ->> 'role'),
49 'anon'
50 ) $fn$`,
51 `CREATE OR REPLACE FUNCTION auth.email() RETURNS text LANGUAGE sql STABLE AS
52 $fn$ SELECT COALESCE(
53 NULLIF(current_setting('request.jwt.claim.email', true), ''),
54 (NULLIF(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
55 ) $fn$`,
56 `GRANT EXECUTE ON FUNCTION auth.uid() TO anon, authenticated, service_role`,
57 `GRANT EXECUTE ON FUNCTION auth.role() TO anon, authenticated, service_role`,
58 `GRANT EXECUTE ON FUNCTION auth.email() TO anon, authenticated, service_role`,
59 // Minimal auth table stubs — enough for FK references and policy expressions.
60 // Projects commonly have FKs to auth.users from public schema tables (e.g. profiles),
61 // so without this stub those tables fail to create and their policies can't be tested.
62 `CREATE TABLE IF NOT EXISTS auth.users (
63 instance_id uuid,
64 id uuid NOT NULL PRIMARY KEY,
65 aud varchar(255),
66 role varchar(255),
67 email varchar(255),
68 encrypted_password varchar(255),
69 email_confirmed_at timestamptz,
70 invited_at timestamptz,
71 confirmation_token varchar(255),
72 confirmation_sent_at timestamptz,
73 recovery_token varchar(255),
74 recovery_sent_at timestamptz,
75 email_change_token_new varchar(255),
76 email_change varchar(255),
77 email_change_sent_at timestamptz,
78 last_sign_in_at timestamptz,
79 raw_app_meta_data jsonb,
80 raw_user_meta_data jsonb,
81 is_super_admin boolean,
82 created_at timestamptz,
83 updated_at timestamptz,
84 phone text DEFAULT NULL,
85 phone_confirmed_at timestamptz,
86 phone_change text DEFAULT '',
87 phone_change_token varchar(255) DEFAULT '',
88 phone_change_sent_at timestamptz,
89 confirmed_at timestamptz,
90 email_change_token_current varchar(255) DEFAULT '',
91 email_change_confirm_status smallint DEFAULT 0,
92 banned_until timestamptz,
93 reauthentication_token varchar(255) DEFAULT '',
94 reauthentication_sent_at timestamptz,
95 is_sso_user boolean NOT NULL DEFAULT false,
96 deleted_at timestamptz,
97 is_anonymous boolean NOT NULL DEFAULT false
98 )`,
99 `CREATE TABLE IF NOT EXISTS auth.sessions (
100 id uuid NOT NULL PRIMARY KEY,
101 user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
102 created_at timestamptz,
103 updated_at timestamptz,
104 factor_id uuid,
105 aal text,
106 not_after timestamptz,
107 refreshed_at timestamp,
108 user_agent text,
109 ip inet,
110 tag text
111 )`,
112 `CREATE TABLE IF NOT EXISTS auth.mfa_factors (
113 id uuid NOT NULL PRIMARY KEY,
114 user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
115 friendly_name text,
116 factor_type text NOT NULL,
117 status text NOT NULL,
118 created_at timestamptz NOT NULL,
119 updated_at timestamptz NOT NULL,
120 secret text,
121 phone text,
122 last_challenged_at timestamptz,
123 web_authn_credential jsonb,
124 web_authn_aaguid uuid
125 )`,
126 `GRANT SELECT, INSERT, UPDATE, DELETE ON auth.users, auth.sessions, auth.mfa_factors TO anon, authenticated, service_role`,
127]
128
129// Seeded alongside public tables so FK references from public → auth.users
130// resolve to real rows. rls flags are ignored here — auth.users is set up by
131// SANDBOX_SETUP_STATEMENTS, this entry is only used by the seed step.
132//
133// Columns are an explicit allow-list: enough to evaluate realistic RLS
134// policies (id for FK matching, role/email/metadata for claim-style checks)
135// while keeping secrets out of the browser-side PGlite instance — no
136// encrypted_password, no *_token columns.
137export const AUTH_USERS_SEED_TABLE = {
138 schema: 'auth',
139 table: 'users',
140 rls_enabled: false,
141 rls_forced: false,
142 columns: [
143 'id',
144 'aud',
145 'role',
146 'email',
147 'phone',
148 'email_confirmed_at',
149 'phone_confirmed_at',
150 'last_sign_in_at',
151 'confirmed_at',
152 'raw_app_meta_data',
153 'raw_user_meta_data',
154 'is_super_admin',
155 'is_sso_user',
156 'is_anonymous',
157 'banned_until',
158 'deleted_at',
159 'created_at',
160 'updated_at',
161 ],
162} as const