Lars Nolden
2ce68cb098
- Move db/schema.sql to supabase/migrations/ as the first supabase CLI migration (manual `db push` only, no automated runner); re-add the gebos_ingest role + grants there since init.sql never re-runs - Add gebos-postgres-passwords oneshot on db-host: syncs role passwords from sops (LoadCredential, journal-safe), makes supabase_admin SUPERUSER and hands the auth schema to supabase_auth_admin to match the upstream supabase/postgres image; add pg_hba rule for 10.0.0.0/8 - Vendor the official docker-compose (studio/kong/auth/rest/meta only, external Postgres, loopback Studio with no Kong dashboard route) plus kong.yml (trimmed) and kong-entrypoint.sh (verbatim); tested: compose config, Kong config parse, migration applied on TimescaleDB pg17 - Document decisions as ADRs 0001-0004 (migrations, passwords, vendored stack, JWT API keys incl. verify/mint procedure) Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
50 lines
1.7 KiB
SQL
50 lines
1.7 KiB
SQL
-- Gebos Postgres bootstrap.
|
|
-- Runs once via services.postgresql.initialScript on db-host.
|
|
--
|
|
-- Sets up:
|
|
-- * Extensions Supabase + TimescaleDB need
|
|
-- * Supabase's expected schemas and admin roles
|
|
-- * The gebos_ingest role (BYPASSRLS) used by the MQTT ingester
|
|
-- * A telemetry hypertable with tenant_id column for RLS
|
|
--
|
|
-- TODO: split into ordered files once it grows. For the skeleton, one file is fine.
|
|
|
|
create extension if not exists timescaledb;
|
|
create extension if not exists pgcrypto;
|
|
create extension if not exists pgjwt;
|
|
create extension if not exists pg_graphql;
|
|
create extension if not exists pgsodium;
|
|
-- create extension if not exists supabase_vault; -- TODO: vendor or package
|
|
|
|
-- Supabase schemas
|
|
create schema if not exists auth;
|
|
create schema if not exists storage;
|
|
create schema if not exists _analytics;
|
|
create schema if not exists _supavisor;
|
|
|
|
-- Roles Supabase services connect as. Passwords come from secrets.env, not here.
|
|
do $$ begin
|
|
create role anon nologin noinherit;
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
do $$ begin
|
|
create role authenticated nologin noinherit;
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
do $$ begin
|
|
create role service_role nologin noinherit bypassrls;
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
do $$ begin
|
|
create role authenticator noinherit login;
|
|
grant anon, authenticated, service_role to authenticator;
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
do $$ begin
|
|
create role supabase_admin login createrole createdb replication bypassrls;
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
do $$ begin
|
|
create role supabase_auth_admin login createrole;
|
|
grant usage on schema auth to supabase_auth_admin;
|
|
exception when duplicate_object then null; end $$; |