Current usability

[pk992]

Hi, I saw the related issue here: https://github.com/cloudnative-pg/cloudnative-pg/issues/2832

I'm trying to get Supabase running with cloudnative-pg too.

Did you find out if the pg_ident.conf and pg_hba.conf are actually needed? Maybe a postgres base instead of supabase/postgres would work just fine if the extensions are included, maybe based on this work: https://github.com/tripsolutions/postgres-containers

If you made it work since starting the repo, I would appreciate any info you got! Thank you.

[jniclas]

Hey, yes I made it work this way:

This repo here is actually not needed anymore^^

No pg_ident.conf and pg_hba.conf, but this settings for the cloud native pg Cluster resource:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: {{ include "supabase.db.fullname" . }}
  labels:
    {{- include "supabase.labels" . | nindent 4 }}
spec:
  instances: 2
  primaryUpdateStrategy: unsupervised
  imageName: "{{ .Values.db.image.repository }}:{{ .Values.db.image.tag | default .Chart.AppVersion }}"
  imagePullPolicy: {{ .Values.db.image.pullPolicy }}
  postgresUID: 101
  postgresGID: 102
  # I tried a bit with pg_hba and pg_init, but not sure if it actually worked, but now its not needed, but I keep it commented
  # postgresql:
    # parameters:
    #   ident_file: /postgresconf/pg_ident.conf
    # pg_hba:
    #   - local all  supabase_admin      scram-sha-256
    #   - local all  all                 peer map=supabase_map
    #   - host  all  all  127.0.0.1/32   trust
    #   - host  all  all  ::1/128        trust
    #   - host  all  all  10.0.0.0/8     scram-sha-256
    #   - host  all  all  172.16.0.0/12  scram-sha-256
    #   - host  all  all  192.168.0.0/16 scram-sha-256
    #   - host  all  all  0.0.0.0/0      scram-sha-256

    # pg_ident:
    #   # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
    #   - supabase_map  postgres   postgres
    #   - supabase_map  root       postgres
    #   - supabase_map  ubuntu     postgres

    #   # supabase-specific users
    #   - supabase_map  gotrue     supabase_auth_admin
    #   - supabase_map  postgrest  authenticator
    #   - supabase_map  adminapi   postgres
  resources: 
    limits:
      memory: 1Gi
      cpu: 1
    requests:
      memory: 1Gi
      cpu: 1
  storage:
    size: 2Gi
    storageClass: {{ .Values.db.storage.storageClassName }}
  monitoring:
    enablePodMonitor: false
  enableSuperuserAccess: true
  bootstrap:
    initdb:
      database: app
      owner: app
      dataChecksums: true
      encoding: 'UTF8'
      postInitApplicationSQLRefs:
        configMapRefs:
        - name: {{ include "supabase.db.fullname" . }}-init
          key: configmap.sql

with values.yaml

db:
 # we disable the supabase db, but use the values in cloud native pq
  enabled: false
  secretName: "supabase-db"
  userName: postgres
  image:
    repository: supabase/postgres
    pullPolicy: IfNotPresent
    tag: "15.1.1.26"
  imagePullSecrets: []
  replicaCount: 2
  nameOverride: ""
  fullnameOverride: ""
  serviceAccount:
    create: true
    annotations: {}
    name: ""
  podAnnotations: {}
  podSecurityContext: {}
  securityContext: {}
  service:
    type: ClusterIP
    port: 5432
  persistence:
    enabled: false
  storage:
    mountPath: /var/lib/postgresql/data
    storageClassName: "openebs-jiva-csi-default"
    annotations: 
      helm.sh/resource-policy: "keep"
    size: 5Gi
    accessModes:
      - ReadWriteOnce
    class: ""
  autoscaling:
    enabled: false
    minReplicas: 1
    maxReplicas: 100
    targetCPUUtilizationPercentage: 80
  nodeSelector: {}
  tolerations: []
  affinity: {}

It was important to change the default Postgres user id and group, cause they are not set to 26 in the sup abase image. I got them by running docker run --rm -it supabase/postgres:15.1.1.26 getent passwd and at the point of creation oct 2023 I copied all migrations from https://github.com/supabase/postgres/tree/develop/migrations/db to my init.yaml: (I think I should change that to a better way that keeps it up to date...)

apiVersion: v1
kind: ConfigMap
metadata:
  name: {{ include "supabase.db.fullname" . }}-init
data:
 # drop role authenticator;
  configmap.sql: |
    -- migrate:up migrations/db/init-scripts/00000000000000-initial-schema.sql

    -- Set up realtime
    -- defaults to empty publication
    create publication supabase_realtime;

    -- Supabase super admin
    create user supabase_admin;
    alter user  supabase_admin with superuser createdb createrole replication bypassrls;

    -- Supabase replication user
    create user supabase_replication_admin with login replication;

    -- Supabase read-only user
    create role supabase_read_only_user with login bypassrls;
    grant pg_read_all_data to supabase_read_only_user;

    -- Extension namespacing
    create schema if not exists extensions;
    create extension if not exists "uuid-ossp"      with schema extensions;
    create extension if not exists pgcrypto         with schema extensions;
    create extension if not exists pgjwt            with schema extensions;

    -- Set up auth roles for the developer
    create role anon                nologin noinherit;
    create role authenticated       nologin noinherit; -- "logged in" user: web_user, app_user, etc
    create role service_role        nologin noinherit bypassrls; -- allow developers to create JWT's that bypass their policies

    create user authenticator noinherit;
    grant anon              to authenticator;
    grant authenticated     to authenticator;
    grant service_role      to authenticator;
    grant supabase_admin    to authenticator;

    grant usage                     on schema public to postgres, anon, authenticated, service_role;
    alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
    alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
    alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

    -- Allow Extensions to be used in the API
    grant usage                     on schema extensions to postgres, anon, authenticated, service_role;

    -- Set up namespacing
    alter user supabase_admin SET search_path TO public, extensions; -- don't include the "auth" schema

    -- These are required so that the users receive grants whenever "supabase_admin" creates tables/function
    alter default privileges for user supabase_admin in schema public grant all
        on sequences to postgres, anon, authenticated, service_role;
    alter default privileges for user supabase_admin in schema public grant all
        on tables to postgres, anon, authenticated, service_role;
    alter default privileges for user supabase_admin in schema public grant all
        on functions to postgres, anon, authenticated, service_role;

    -- Set short statement/query timeouts for API roles
    alter role anon set statement_timeout = '3s';
    alter role authenticated set statement_timeout = '8s';

    -- migrate:down

    -- migrate:up migrations/db/init-scripts/00000000000001-auth-schema.sql

    CREATE SCHEMA IF NOT EXISTS auth AUTHORIZATION supabase_admin;

    -- auth.users definition

    CREATE TABLE auth.users (
        instance_id uuid NULL,
        id uuid NOT NULL UNIQUE,
        aud varchar(255) NULL,
        "role" varchar(255) NULL,
        email varchar(255) NULL UNIQUE,
        encrypted_password varchar(255) NULL,
        confirmed_at timestamptz NULL,
        invited_at timestamptz NULL,
        confirmation_token varchar(255) NULL,
        confirmation_sent_at timestamptz NULL,
        recovery_token varchar(255) NULL,
        recovery_sent_at timestamptz NULL,
        email_change_token varchar(255) NULL,
        email_change varchar(255) NULL,
        email_change_sent_at timestamptz NULL,
        last_sign_in_at timestamptz NULL,
        raw_app_meta_data jsonb NULL,
        raw_user_meta_data jsonb NULL,
        is_super_admin bool NULL,
        created_at timestamptz NULL,
        updated_at timestamptz NULL,
        CONSTRAINT users_pkey PRIMARY KEY (id)
    );
    CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id, email);
    CREATE INDEX users_instance_id_idx ON auth.users USING btree (instance_id);
    comment on table auth.users is 'Auth: Stores user login data within a secure schema.';

    -- auth.refresh_tokens definition

    CREATE TABLE auth.refresh_tokens (
        instance_id uuid NULL,
        id bigserial NOT NULL,
        "token" varchar(255) NULL,
        user_id varchar(255) NULL,
        revoked bool NULL,
        created_at timestamptz NULL,
        updated_at timestamptz NULL,
        CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id)
    );
    CREATE INDEX refresh_tokens_instance_id_idx ON auth.refresh_tokens USING btree (instance_id);
    CREATE INDEX refresh_tokens_instance_id_user_id_idx ON auth.refresh_tokens USING btree (instance_id, user_id);
    CREATE INDEX refresh_tokens_token_idx ON auth.refresh_tokens USING btree (token);
    comment on table auth.refresh_tokens is 'Auth: Store of tokens used to refresh JWT tokens once they expire.';

    -- auth.instances definition

    CREATE TABLE auth.instances (
        id uuid NOT NULL,
        uuid uuid NULL,
        raw_base_config text NULL,
        created_at timestamptz NULL,
        updated_at timestamptz NULL,
        CONSTRAINT instances_pkey PRIMARY KEY (id)
    );
    comment on table auth.instances is 'Auth: Manages users across multiple sites.';

    -- auth.audit_log_entries definition

    CREATE TABLE auth.audit_log_entries (
        instance_id uuid NULL,
        id uuid NOT NULL,
        payload json NULL,
        created_at timestamptz NULL,
        CONSTRAINT audit_log_entries_pkey PRIMARY KEY (id)
    );
    CREATE INDEX audit_logs_instance_id_idx ON auth.audit_log_entries USING btree (instance_id);
    comment on table auth.audit_log_entries is 'Auth: Audit trail for user actions.';

    -- auth.schema_migrations definition

    CREATE TABLE auth.schema_migrations (
        "version" varchar(255) NOT NULL,
        CONSTRAINT schema_migrations_pkey PRIMARY KEY ("version")
    );
    comment on table auth.schema_migrations is 'Auth: Manages updates to the auth system.';

    INSERT INTO auth.schema_migrations (version)
    VALUES  ('20171026211738'),
            ('20171026211808'),
            ('20171026211834'),
            ('20180103212743'),
            ('20180108183307'),
            ('20180119214651'),
            ('20180125194653');

    -- Gets the User ID from the request cookie
    create or replace function auth.uid() returns uuid as $$
    select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid;
    $$ language sql stable;

    -- Gets the User ID from the request cookie
    create or replace function auth.role() returns text as $$
    select nullif(current_setting('request.jwt.claim.role', true), '')::text;
    $$ language sql stable;

    -- Gets the User email
    create or replace function auth.email() returns text as $$
    select nullif(current_setting('request.jwt.claim.email', true), '')::text;
    $$ language sql stable;

    -- usage on auth functions to API roles
    GRANT USAGE ON SCHEMA auth TO anon, authenticated, service_role;

    -- Supabase super admin
    CREATE USER supabase_auth_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
    GRANT ALL PRIVILEGES ON SCHEMA auth TO supabase_auth_admin;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA auth TO supabase_auth_admin;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA auth TO supabase_auth_admin;
    ALTER USER supabase_auth_admin SET search_path = "auth";
    ALTER table "auth".users OWNER TO supabase_auth_admin;
    ALTER table "auth".refresh_tokens OWNER TO supabase_auth_admin;
    ALTER table "auth".audit_log_entries OWNER TO supabase_auth_admin;
    ALTER table "auth".instances OWNER TO supabase_auth_admin;
    ALTER table "auth".schema_migrations OWNER TO supabase_auth_admin;

    -- migrate:down

    -- migrate:up migrations/db/init-scripts/00000000000002-storage-schema.sql

    CREATE SCHEMA IF NOT EXISTS storage AUTHORIZATION supabase_admin;

    grant usage on schema storage to postgres, anon, authenticated, service_role;
    alter default privileges in schema storage grant all on tables to postgres, anon, authenticated, service_role;
    alter default privileges in schema storage grant all on functions to postgres, anon, authenticated, service_role;
    alter default privileges in schema storage grant all on sequences to postgres, anon, authenticated, service_role;

    CREATE TABLE "storage"."buckets" (
        "id" text not NULL,
        "name" text NOT NULL,
        "owner" uuid,
        "created_at" timestamptz DEFAULT now(),
        "updated_at" timestamptz DEFAULT now(),
        CONSTRAINT "buckets_owner_fkey" FOREIGN KEY ("owner") REFERENCES "auth"."users"("id"),
        PRIMARY KEY ("id")
    );
    CREATE UNIQUE INDEX "bname" ON "storage"."buckets" USING BTREE ("name");

    CREATE TABLE "storage"."objects" (
        "id" uuid NOT NULL DEFAULT extensions.uuid_generate_v4(),
        "bucket_id" text,
        "name" text,
        "owner" uuid,
        "created_at" timestamptz DEFAULT now(),
        "updated_at" timestamptz DEFAULT now(),
        "last_accessed_at" timestamptz DEFAULT now(),
        "metadata" jsonb,
        CONSTRAINT "objects_bucketId_fkey" FOREIGN KEY ("bucket_id") REFERENCES "storage"."buckets"("id"),
        CONSTRAINT "objects_owner_fkey" FOREIGN KEY ("owner") REFERENCES "auth"."users"("id"),
        PRIMARY KEY ("id")
    );
    CREATE UNIQUE INDEX "bucketid_objname" ON "storage"."objects" USING BTREE ("bucket_id","name");
    CREATE INDEX name_prefix_search ON storage.objects(name text_pattern_ops);

    ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;

    CREATE FUNCTION storage.foldername(name text)
    RETURNS text[]
    LANGUAGE plpgsql
    AS $function$
    DECLARE
    _parts text[];
    BEGIN
        select string_to_array(name, '/') into _parts;
        return _parts[1:array_length(_parts,1)-1];
    END
    $function$;

    CREATE FUNCTION storage.filename(name text)
    RETURNS text
    LANGUAGE plpgsql
    AS $function$
    DECLARE
    _parts text[];
    BEGIN
        select string_to_array(name, '/') into _parts;
        return _parts[array_length(_parts,1)];
    END
    $function$;

    CREATE FUNCTION storage.extension(name text)
    RETURNS text
    LANGUAGE plpgsql
    AS $function$
    DECLARE
    _parts text[];
    _filename text;
    BEGIN
        select string_to_array(name, '/') into _parts;
        select _parts[array_length(_parts,1)] into _filename;
        -- @todo return the last part instead of 2
        return split_part(_filename, '.', 2);
    END
    $function$;

    CREATE FUNCTION storage.search(prefix text, bucketname text, limits int DEFAULT 100, levels int DEFAULT 1, offsets int DEFAULT 0)
    RETURNS TABLE (
        name text,
        id uuid,
        updated_at TIMESTAMPTZ,
        created_at TIMESTAMPTZ,
        last_accessed_at TIMESTAMPTZ,
        metadata jsonb
    )
    LANGUAGE plpgsql
    AS $function$
    DECLARE
    _bucketId text;
    BEGIN
        -- will be replaced by migrations when server starts
        -- saving space for cloud-init
    END
    $function$;

    -- create migrations table
    -- https://github.com/ThomWright/postgres-migrations/blob/master/src/migrations/0_create-migrations-table.sql
    -- we add this table here and not let it be auto-created so that the permissions are properly applied to it
    CREATE TABLE IF NOT EXISTS storage.migrations (
    id integer PRIMARY KEY,
    name varchar(100) UNIQUE NOT NULL,
    hash varchar(40) NOT NULL, -- sha1 hex encoded hash of the file name and contents, to ensure it hasn't been altered since applying the migration
    executed_at timestamp DEFAULT current_timestamp
    );

    CREATE USER supabase_storage_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
    GRANT ALL PRIVILEGES ON SCHEMA storage TO supabase_storage_admin;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA storage TO supabase_storage_admin;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA storage TO supabase_storage_admin;
    ALTER USER supabase_storage_admin SET search_path = "storage";
    ALTER table "storage".objects owner to supabase_storage_admin;
    ALTER table "storage".buckets owner to supabase_storage_admin;
    ALTER table "storage".migrations OWNER TO supabase_storage_admin;
    ALTER function "storage".foldername(text) owner to supabase_storage_admin;
    ALTER function "storage".filename(text) owner to supabase_storage_admin;
    ALTER function "storage".extension(text) owner to supabase_storage_admin;
    ALTER function "storage".search(text,text,int,int,int) owner to supabase_storage_admin;

    -- migrate:down

    -- migrate:up migrations/db/init-scripts/00000000000003-post-setup.sql

    ALTER ROLE supabase_admin SET search_path TO "\$user",public,auth,extensions;
    ALTER ROLE postgres SET search_path TO "\$user",public,extensions;

    -- Trigger for pg_cron
    CREATE OR REPLACE FUNCTION extensions.grant_pg_cron_access()
    RETURNS event_trigger
    LANGUAGE plpgsql
    AS $$
    DECLARE
    schema_is_cron bool;
    BEGIN
    schema_is_cron = (
        SELECT n.nspname = 'cron'
        FROM pg_event_trigger_ddl_commands() AS ev
        LEFT JOIN pg_catalog.pg_namespace AS n
        ON ev.objid = n.oid
    );

    IF schema_is_cron
    THEN
        grant usage on schema cron to postgres with grant option;

        alter default privileges in schema cron grant all on tables to postgres with grant option;
        alter default privileges in schema cron grant all on functions to postgres with grant option;
        alter default privileges in schema cron grant all on sequences to postgres with grant option;

        alter default privileges for user supabase_admin in schema cron grant all
            on sequences to postgres with grant option;
        alter default privileges for user supabase_admin in schema cron grant all
            on tables to postgres with grant option;
        alter default privileges for user supabase_admin in schema cron grant all
            on functions to postgres with grant option;

        grant all privileges on all tables in schema cron to postgres with grant option;

    END IF;

    END;
    $$;
    CREATE EVENT TRIGGER issue_pg_cron_access ON ddl_command_end WHEN TAG in ('CREATE SCHEMA')
    EXECUTE PROCEDURE extensions.grant_pg_cron_access();
    COMMENT ON FUNCTION extensions.grant_pg_cron_access IS 'Grants access to pg_cron';

    -- Event trigger for pg_net
    CREATE OR REPLACE FUNCTION extensions.grant_pg_net_access()
    RETURNS event_trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_event_trigger_ddl_commands() AS ev
        JOIN pg_extension AS ext
        ON ev.objid = ext.oid
        WHERE ext.extname = 'pg_net'
    )
    THEN
        IF NOT EXISTS (
        SELECT 1
        FROM pg_roles
        WHERE rolname = 'supabase_functions_admin'
        )
        THEN
        CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
        END IF;

        GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role;

        ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
        ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;

        ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
        ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;

        REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
        REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;

        GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
        GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
    END IF;
    END;
    $$;
    COMMENT ON FUNCTION extensions.grant_pg_net_access IS 'Grants access to pg_net';

    DO
    $$
    BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_event_trigger
        WHERE evtname = 'issue_pg_net_access'
    ) THEN
        CREATE EVENT TRIGGER issue_pg_net_access
        ON ddl_command_end
        WHEN TAG IN ('CREATE EXTENSION')
        EXECUTE PROCEDURE extensions.grant_pg_net_access();
    END IF;
    END
    $$;

    -- Supabase dashboard user
    CREATE ROLE dashboard_user NOSUPERUSER CREATEDB CREATEROLE REPLICATION;
    GRANT ALL ON DATABASE postgres TO dashboard_user;
    GRANT ALL ON SCHEMA auth TO dashboard_user;
    GRANT ALL ON SCHEMA extensions TO dashboard_user;
    GRANT ALL ON SCHEMA storage TO dashboard_user;
    GRANT ALL ON ALL TABLES IN SCHEMA auth TO dashboard_user;
    GRANT ALL ON ALL TABLES IN SCHEMA extensions TO dashboard_user;
    -- GRANT ALL ON ALL TABLES IN SCHEMA storage TO dashboard_user;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA auth TO dashboard_user;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA storage TO dashboard_user;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA extensions TO dashboard_user;
    GRANT ALL ON ALL ROUTINES IN SCHEMA auth TO dashboard_user;
    GRANT ALL ON ALL ROUTINES IN SCHEMA storage TO dashboard_user;
    GRANT ALL ON ALL ROUTINES IN SCHEMA extensions TO dashboard_user;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20211115181400_update-auth-permissions.sql

    -- update auth schema permissions
    GRANT ALL PRIVILEGES ON SCHEMA auth TO supabase_auth_admin;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA auth TO supabase_auth_admin;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA auth TO supabase_auth_admin;

    ALTER table IF EXISTS "auth".users OWNER TO supabase_auth_admin;
    ALTER table IF EXISTS "auth".refresh_tokens OWNER TO supabase_auth_admin;
    ALTER table IF EXISTS "auth".audit_log_entries OWNER TO supabase_auth_admin;
    ALTER table IF EXISTS "auth".instances OWNER TO supabase_auth_admin;
    ALTER table IF EXISTS "auth".schema_migrations OWNER TO supabase_auth_admin;

    GRANT USAGE ON SCHEMA auth TO postgres;
    GRANT ALL ON ALL TABLES IN SCHEMA auth TO postgres, dashboard_user;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA auth TO postgres, dashboard_user;
    GRANT ALL ON ALL ROUTINES IN SCHEMA auth TO postgres, dashboard_user;
    ALTER DEFAULT PRIVILEGES FOR ROLE supabase_auth_admin IN SCHEMA auth GRANT ALL ON TABLES TO postgres, dashboard_user;
    ALTER DEFAULT PRIVILEGES FOR ROLE supabase_auth_admin IN SCHEMA auth GRANT ALL ON SEQUENCES TO postgres, dashboard_user;
    ALTER DEFAULT PRIVILEGES FOR ROLE supabase_auth_admin IN SCHEMA auth GRANT ALL ON ROUTINES TO postgres, dashboard_user;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20211118015519_create-realtime-schema.sql

    -- create realtime schema for Realtime RLS (WALRUS)
    CREATE SCHEMA IF NOT EXISTS _realtime;
    CREATE SCHEMA IF NOT EXISTS realtime;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20211122051245_update-realtime-permissions.sql

    -- update realtime schema permissions
    GRANT USAGE ON SCHEMA realtime TO postgres;
    GRANT ALL ON ALL TABLES IN SCHEMA realtime TO postgres, dashboard_user;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA realtime TO postgres, dashboard_user;
    GRANT ALL ON ALL ROUTINES IN SCHEMA realtime TO postgres, dashboard_user;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20211124212715_update-auth-owner.sql

    -- update owner for auth.uid, auth.role and auth.email functions
    ALTER FUNCTION auth.uid owner to supabase_auth_admin;
    ALTER FUNCTION auth.role owner to supabase_auth_admin;
    ALTER FUNCTION auth.email owner to supabase_auth_admin;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20211130151719_update-realtime-permissions.sql

    -- Update future objects' permissions
    ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA realtime GRANT ALL ON TABLES TO postgres, dashboard_user;
    ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA realtime GRANT ALL ON SEQUENCES TO postgres, dashboard_user;
    ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA realtime GRANT ALL ON ROUTINES TO postgres, dashboard_user;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220118070449_enable-safeupdate-postgrest.sql

    ALTER ROLE authenticator SET session_preload_libraries = 'safeupdate';

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220126121436_finer-postgrest-triggers.sql

    drop event trigger if exists api_restart;
    drop function if exists extensions.notify_api_restart();

    -- https://postgrest.org/en/latest/schema_cache.html#finer-grained-event-trigger
    -- watch create and alter
    CREATE OR REPLACE FUNCTION extensions.pgrst_ddl_watch() RETURNS event_trigger AS $$
    DECLARE
    cmd record;
    BEGIN
    FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF cmd.command_tag IN (
        'CREATE SCHEMA', 'ALTER SCHEMA'
        , 'CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO', 'ALTER TABLE'
        , 'CREATE FOREIGN TABLE', 'ALTER FOREIGN TABLE'
        , 'CREATE VIEW', 'ALTER VIEW'
        , 'CREATE MATERIALIZED VIEW', 'ALTER MATERIALIZED VIEW'
        , 'CREATE FUNCTION', 'ALTER FUNCTION'
        , 'CREATE TRIGGER'
        , 'CREATE TYPE'
        , 'CREATE RULE'
        , 'COMMENT'
        )
        -- don't notify in case of CREATE TEMP table or other objects created on pg_temp
        AND cmd.schema_name is distinct from 'pg_temp'
        THEN
        NOTIFY pgrst, 'reload schema';
        END IF;
    END LOOP;
    END; $$ LANGUAGE plpgsql;

    -- watch drop
    CREATE OR REPLACE FUNCTION extensions.pgrst_drop_watch() RETURNS event_trigger AS $$
    DECLARE
    obj record;
    BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF obj.object_type IN (
        'schema'
        , 'table'
        , 'foreign table'
        , 'view'
        , 'materialized view'
        , 'function'
        , 'trigger'
        , 'type'
        , 'rule'
        )
        AND obj.is_temporary IS false -- no pg_temp objects
        THEN
        NOTIFY pgrst, 'reload schema';
        END IF;
    END LOOP;
    END; $$ LANGUAGE plpgsql;

    DROP EVENT TRIGGER IF EXISTS pgrst_ddl_watch;
    CREATE EVENT TRIGGER pgrst_ddl_watch
    ON ddl_command_end
    EXECUTE PROCEDURE extensions.pgrst_ddl_watch();

    DROP EVENT TRIGGER IF EXISTS pgrst_drop_watch;
    CREATE EVENT TRIGGER pgrst_drop_watch
    ON sql_drop
    EXECUTE PROCEDURE extensions.pgrst_drop_watch();

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220224211803_fix-postgrest-supautils.sql

    -- Note: supatils extension is not installed in docker image.

    DO $$
    DECLARE
    supautils_exists boolean;
    BEGIN
    supautils_exists = (
        select count(*) = 1
        from pg_available_extensions
        where name = 'supautils'
    );

    IF supautils_exists
    THEN
    ALTER ROLE authenticator SET session_preload_libraries = supautils, safeupdate;
    END IF;
    END $$;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220317095840_pg_graphql.sql

    create schema if not exists graphql_public;

    -- GraphQL Placeholder Entrypoint
    create or replace function graphql_public.graphql(
        "operationName" text default null,
        query text default null,
        variables jsonb default null,
        extensions jsonb default null
    )
        returns jsonb
        language plpgsql
    as $$
        DECLARE
            server_version float;
        BEGIN
            server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);

            IF server_version >= 14 THEN
                RETURN jsonb_build_object(
                    'data', null::jsonb,
                    'errors', array['pg_graphql extension is not enabled.']
                );
            ELSE
                RETURN jsonb_build_object(
                    'data', null::jsonb,
                    'errors', array['pg_graphql is only available on projects running Postgres 14 onwards.']
                );
            END IF;
        END;
    $$;

    grant usage on schema graphql_public to postgres, anon, authenticated, service_role;
    alter default privileges in schema graphql_public grant all on tables to postgres, anon, authenticated, service_role;
    alter default privileges in schema graphql_public grant all on functions to postgres, anon, authenticated, service_role;
    alter default privileges in schema graphql_public grant all on sequences to postgres, anon, authenticated, service_role;

    alter default privileges for user supabase_admin in schema graphql_public grant all
        on sequences to postgres, anon, authenticated, service_role;
    alter default privileges for user supabase_admin in schema graphql_public grant all
        on tables to postgres, anon, authenticated, service_role;
    alter default privileges for user supabase_admin in schema graphql_public grant all
        on functions to postgres, anon, authenticated, service_role;

    -- Trigger upon enabling pg_graphql
    CREATE OR REPLACE FUNCTION extensions.grant_pg_graphql_access()
    RETURNS event_trigger
    LANGUAGE plpgsql
    AS $func$
        DECLARE
        func_is_graphql_resolve bool;
        BEGIN
        func_is_graphql_resolve = (
            SELECT n.proname = 'resolve'
            FROM pg_event_trigger_ddl_commands() AS ev
            LEFT JOIN pg_catalog.pg_proc AS n
            ON ev.objid = n.oid
        );

        IF func_is_graphql_resolve
        THEN
            grant usage on schema graphql to postgres, anon, authenticated, service_role;
            grant all on function graphql.resolve to postgres, anon, authenticated, service_role;

            alter default privileges in schema graphql grant all on tables to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on functions to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on sequences to postgres, anon, authenticated, service_role;

            DROP FUNCTION IF EXISTS graphql_public.graphql;
            create or replace function graphql_public.graphql(
                "operationName" text default null,
                query text default null,
                variables jsonb default null,
                extensions jsonb default null
            )
                returns jsonb
                language sql
            as $$
                SELECT graphql.resolve(query, coalesce(variables, '{}'));
            $$;

            grant execute on function graphql.resolve to postgres, anon, authenticated, service_role;
        END IF;

        END;
    $func$;

    DROP EVENT TRIGGER IF EXISTS issue_pg_graphql_access;
    CREATE EVENT TRIGGER issue_pg_graphql_access ON ddl_command_end WHEN TAG in ('CREATE FUNCTION')
    EXECUTE PROCEDURE extensions.grant_pg_graphql_access();
    COMMENT ON FUNCTION extensions.grant_pg_graphql_access IS 'Grants access to pg_graphql';

    -- Trigger upon dropping the pg_graphql extension
    CREATE OR REPLACE FUNCTION extensions.set_graphql_placeholder()
    RETURNS event_trigger
    LANGUAGE plpgsql
    AS $func$
        DECLARE
        graphql_is_dropped bool;
        BEGIN
        graphql_is_dropped = (
            SELECT ev.schema_name = 'graphql_public'
            FROM pg_event_trigger_dropped_objects() AS ev
            WHERE ev.schema_name = 'graphql_public'
        );

        IF graphql_is_dropped
        THEN
            create or replace function graphql_public.graphql(
                "operationName" text default null,
                query text default null,
                variables jsonb default null,
                extensions jsonb default null
            )
                returns jsonb
                language plpgsql
            as $$
                DECLARE
                    server_version float;
                BEGIN
                    server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);

                    IF server_version >= 14 THEN
                        RETURN jsonb_build_object(
                            'data', null::jsonb,
                            'errors', array['pg_graphql extension is not enabled.']
                        );
                    ELSE
                        RETURN jsonb_build_object(
                            'data', null::jsonb,
                            'errors', array['pg_graphql is only available on projects running Postgres 14 onwards.']
                        );
                    END IF;
                END;
            $$;
        END IF;

        END;
    $func$;

    DROP EVENT TRIGGER IF EXISTS issue_graphql_placeholder;
    CREATE EVENT TRIGGER issue_graphql_placeholder ON sql_drop WHEN TAG in ('DROP EXTENSION')
    EXECUTE PROCEDURE extensions.set_graphql_placeholder();
    COMMENT ON FUNCTION extensions.set_graphql_placeholder IS 'Reintroduces placeholder function for graphql_public.graphql';

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220321174452_fix-postgrest-alter-type-event-trigger.sql

    drop event trigger if exists api_restart;
    drop function if exists extensions.notify_api_restart();

    -- https://postgrest.org/en/latest/schema_cache.html#finer-grained-event-trigger
    -- watch create and alter
    CREATE OR REPLACE FUNCTION extensions.pgrst_ddl_watch() RETURNS event_trigger AS $$
    DECLARE
    cmd record;
    BEGIN
    FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF cmd.command_tag IN (
        'CREATE SCHEMA', 'ALTER SCHEMA'
        , 'CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO', 'ALTER TABLE'
        , 'CREATE FOREIGN TABLE', 'ALTER FOREIGN TABLE'
        , 'CREATE VIEW', 'ALTER VIEW'
        , 'CREATE MATERIALIZED VIEW', 'ALTER MATERIALIZED VIEW'
        , 'CREATE FUNCTION', 'ALTER FUNCTION'
        , 'CREATE TRIGGER'
        , 'CREATE TYPE', 'ALTER TYPE'
        , 'CREATE RULE'
        , 'COMMENT'
        )
        -- don't notify in case of CREATE TEMP table or other objects created on pg_temp
        AND cmd.schema_name is distinct from 'pg_temp'
        THEN
        NOTIFY pgrst, 'reload schema';
        END IF;
    END LOOP;
    END; $$ LANGUAGE plpgsql;

    -- watch drop
    CREATE OR REPLACE FUNCTION extensions.pgrst_drop_watch() RETURNS event_trigger AS $$
    DECLARE
    obj record;
    BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF obj.object_type IN (
        'schema'
        , 'table'
        , 'foreign table'
        , 'view'
        , 'materialized view'
        , 'function'
        , 'trigger'
        , 'type'
        , 'rule'
        )
        AND obj.is_temporary IS false -- no pg_temp objects
        THEN
        NOTIFY pgrst, 'reload schema';
        END IF;
    END LOOP;
    END; $$ LANGUAGE plpgsql;

    DROP EVENT TRIGGER IF EXISTS pgrst_ddl_watch;
    CREATE EVENT TRIGGER pgrst_ddl_watch
    ON ddl_command_end
    EXECUTE PROCEDURE extensions.pgrst_ddl_watch();

    DROP EVENT TRIGGER IF EXISTS pgrst_drop_watch;
    CREATE EVENT TRIGGER pgrst_drop_watch
    ON sql_drop
    EXECUTE PROCEDURE extensions.pgrst_drop_watch();

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220322085208_gotrue-session-limit.sql

    ALTER ROLE supabase_auth_admin SET idle_in_transaction_session_timeout TO 60000;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220404205710_pg_graphql-on-by-default.sql

    -- Update Trigger upon enabling pg_graphql
    create or replace function extensions.grant_pg_graphql_access()
        returns event_trigger
        language plpgsql
    AS $func$
    DECLARE
        func_is_graphql_resolve bool;
    BEGIN
        func_is_graphql_resolve = (
            SELECT n.proname = 'resolve'
            FROM pg_event_trigger_ddl_commands() AS ev
            LEFT JOIN pg_catalog.pg_proc AS n
            ON ev.objid = n.oid
        );

        IF func_is_graphql_resolve
        THEN
            grant usage on schema graphql to postgres, anon, authenticated, service_role;
            grant all on function graphql.resolve to postgres, anon, authenticated, service_role;

            alter default privileges in schema graphql grant all on tables to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on functions to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on sequences to postgres, anon, authenticated, service_role;

            -- Update public wrapper to pass all arguments through to the pg_graphql resolve func
            DROP FUNCTION IF EXISTS graphql_public.graphql;
            create or replace function graphql_public.graphql(
                "operationName" text default null,
                query text default null,
                variables jsonb default null,
                extensions jsonb default null
            )
                returns jsonb
                language sql
            as $$
                -- This changed
                select graphql.resolve(
                    query := query,
                    variables := coalesce(variables, '{}'),
                    "operationName" := "operationName",
                    extensions := extensions
                );
            $$;

            grant execute on function graphql.resolve to postgres, anon, authenticated, service_role;
        END IF;

    END;
    $func$;

    CREATE OR REPLACE FUNCTION extensions.set_graphql_placeholder()
    RETURNS event_trigger
    LANGUAGE plpgsql
    AS $func$
        DECLARE
        graphql_is_dropped bool;
        BEGIN
        graphql_is_dropped = (
            SELECT ev.schema_name = 'graphql_public'
            FROM pg_event_trigger_dropped_objects() AS ev
            WHERE ev.schema_name = 'graphql_public'
        );

        IF graphql_is_dropped
        THEN
            create or replace function graphql_public.graphql(
                "operationName" text default null,
                query text default null,
                variables jsonb default null,
                extensions jsonb default null
            )
                returns jsonb
                language plpgsql
            as $$
                DECLARE
                    server_version float;
                BEGIN
                    server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);

                    IF server_version >= 14 THEN
                        RETURN jsonb_build_object(
                            'errors', jsonb_build_array(
                                jsonb_build_object(
                                    'message', 'pg_graphql extension is not enabled.'
                                )
                            )
                        );
                    ELSE
                        RETURN jsonb_build_object(
                            'errors', jsonb_build_array(
                                jsonb_build_object(
                                    'message', 'pg_graphql is only available on projects running Postgres 14 onwards.'
                                )
                            )
                        );
                    END IF;
                END;
            $$;
        END IF;

        END;
    $func$;

    -- GraphQL Placeholder Entrypoint
    create or replace function graphql_public.graphql(
        "operationName" text default null,
        query text default null,
        variables jsonb default null,
        extensions jsonb default null
    )
        returns jsonb
        language plpgsql
    as $$
        DECLARE
            server_version float;
        BEGIN
            server_version = (SELECT (SPLIT_PART((select version()), ' ', 2))::float);

            IF server_version >= 14 THEN
                RETURN jsonb_build_object(
                    'errors', jsonb_build_array(
                        jsonb_build_object(
                            'message', 'pg_graphql extension is not enabled.'
                        )
                    )
                );
            ELSE
                RETURN jsonb_build_object(
                    'errors', jsonb_build_array(
                        jsonb_build_object(
                            'message', 'pg_graphql is only available on projects running Postgres 14 onwards.'
                        )
                    )
                );
            END IF;
        END;
    $$;

    drop extension if exists pg_graphql;
    -- Avoids limitation of only being able to load the extension via dashboard
    -- Only install as well if the extension is actually installed
    DO $$
    DECLARE
    graphql_exists boolean;
    BEGIN
    graphql_exists = (
        select count(*) = 1
        from pg_available_extensions
        where name = 'pg_graphql'
    );

    IF graphql_exists
    THEN
    create extension if not exists pg_graphql;
    END IF;
    END $$;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220609081115_grant-supabase-auth-admin-and-supabase-storage-admin-to-postgres.sql

    -- This is done so that the `postgres` role can manage auth tables triggers,
    -- storage tables policies, etc. which unblocks the revocation of superuser
    -- access.
    --
    -- More context: https://www.notion.so/supabase/RFC-Postgres-Permissions-I-40cb4f61bd4145fd9e75ce657c0e31dd#bf5d853436384e6e8e339d0a2e684cbb
    grant supabase_auth_admin, supabase_storage_admin to postgres;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220613123923_pg_graphql-pg-dump-perms.sql

    create or replace function extensions.grant_pg_graphql_access()
        returns event_trigger
        language plpgsql
    AS $func$
    DECLARE
        func_is_graphql_resolve bool;
    BEGIN
        func_is_graphql_resolve = (
            SELECT n.proname = 'resolve'
            FROM pg_event_trigger_ddl_commands() AS ev
            LEFT JOIN pg_catalog.pg_proc AS n
            ON ev.objid = n.oid
        );

        IF func_is_graphql_resolve
        THEN
            -- Update public wrapper to pass all arguments through to the pg_graphql resolve func
            DROP FUNCTION IF EXISTS graphql_public.graphql;
            create or replace function graphql_public.graphql(
                "operationName" text default null,
                query text default null,
                variables jsonb default null,
                extensions jsonb default null
            )
                returns jsonb
                language sql
            as $$
                select graphql.resolve(
                    query := query,
                    variables := coalesce(variables, '{}'),
                    "operationName" := "operationName",
                    extensions := extensions
                );
            $$;

            -- This hook executes when `graphql.resolve` is created. That is not necessarily the last
            -- function in the extension so we need to grant permissions on existing entities AND
            -- update default permissions to any others that are created after `graphql.resolve`
            grant usage on schema graphql to postgres, anon, authenticated, service_role;
            grant select on all tables in schema graphql to postgres, anon, authenticated, service_role;
            grant execute on all functions in schema graphql to postgres, anon, authenticated, service_role;
            grant all on all sequences in schema graphql to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on tables to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on functions to postgres, anon, authenticated, service_role;
            alter default privileges in schema graphql grant all on sequences to postgres, anon, authenticated, service_role;
        END IF;

    END;
    $func$;

    -- Cycle the extension off and back on to apply the permissions update.

    drop extension if exists pg_graphql;
    -- Avoids limitation of only being able to load the extension via dashboard
    -- Only install as well if the extension is actually installed
    DO $$
    DECLARE
    graphql_exists boolean;
    BEGIN
    graphql_exists = (
        select count(*) = 1 
        from pg_available_extensions 
        where name = 'pg_graphql'
    );

    IF graphql_exists 
    THEN
    create extension if not exists pg_graphql;
    END IF;
    END $$;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20220713082019_pg_cron-pg_net-temp-perms-fix.sql
    DO $$
    DECLARE
    pg_cron_installed boolean;
    BEGIN
    -- checks if pg_cron is enabled   
    pg_cron_installed = (
        select count(*) = 1 
        from pg_available_extensions 
        where name = 'pg_cron'
        and installed_version is not null
    );

    IF pg_cron_installed
    THEN
        grant usage on schema cron to postgres with grant option;
        grant all on all functions in schema cron to postgres with grant option;

        alter default privileges in schema cron grant all on tables to postgres with grant option;
        alter default privileges in schema cron grant all on functions to postgres with grant option;
        alter default privileges in schema cron grant all on sequences to postgres with grant option;

        alter default privileges for user supabase_admin in schema cron grant all
            on sequences to postgres with grant option;
        alter default privileges for user supabase_admin in schema cron grant all
            on tables to postgres with grant option;
        alter default privileges for user supabase_admin in schema cron grant all
            on functions to postgres with grant option;

        grant all privileges on all tables in schema cron to postgres with grant option; 
    END IF;
    END $$;

    DO $$
    DECLARE
    pg_net_installed boolean;
    BEGIN
    -- checks if pg_net is enabled
    pg_net_installed = (
        select count(*) = 1 
        from pg_available_extensions 
        where name = 'pg_net'
        and installed_version is not null

    );

    IF pg_net_installed 
    THEN
        IF NOT EXISTS (
        SELECT 1
        FROM pg_roles
        WHERE rolname = 'supabase_functions_admin'
        )
        THEN
        CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
        END IF;

        GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role;

        ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
        ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;

        ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
        ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;

        REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
        REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;

        GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
        GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
    END IF;
    END $$;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20221028101028_set_authenticator_timeout.sql

    alter role authenticator set statement_timeout = '8s';

    -- migrate:down

    -- migrate:up migrations/db/migrations/20221103090837_revoke_admin.sql
    revoke supabase_admin from authenticator;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20221207154255_create_pgsodium_and_vault.sql

    DO $$
    DECLARE
    pgsodium_exists boolean;
    vault_exists boolean;
    BEGIN
    pgsodium_exists = (
        select count(*) = 1 
        from pg_available_extensions 
        where name = 'pgsodium'
    );

    vault_exists = (
        select count(*) = 1 
        from pg_available_extensions 
        where name = 'supabase_vault'
    );

    IF pgsodium_exists 
    THEN
        create extension if not exists pgsodium;

        grant pgsodium_keyiduser to postgres with admin option;
        grant pgsodium_keyholder to postgres with admin option;
        grant pgsodium_keymaker  to postgres with admin option;

        grant execute on function pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid, bytea) to service_role;
        grant execute on function pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid, bytea) to service_role;
        grant execute on function pgsodium.crypto_aead_det_keygen to service_role;

        IF vault_exists
        THEN
        create extension if not exists supabase_vault;
        END IF;
    END IF;
    END $$;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20230201083204_grant_auth_roles_to_postgres.sql

    grant anon, authenticated, service_role to postgres;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20230224042246_grant_extensions_perms_for_postgres.sql

    grant all privileges on all tables in schema extensions to postgres with grant option;
    grant all privileges on all routines in schema extensions to postgres with grant option;
    grant all privileges on all sequences in schema extensions to postgres with grant option;
    alter default privileges in schema extensions grant all on tables to postgres with grant option;
    alter default privileges in schema extensions grant all on routines to postgres with grant option;
    alter default privileges in schema extensions grant all on sequences to postgres with grant option;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20230306081037_grant_pg_monitor_to_postgres.sql

    grant pg_monitor to postgres;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20230327032006_grant_auth_roles_to_supabase_storage_admin.sql
    grant anon, authenticated, service_role to supabase_storage_admin;

    -- migrate:down

    -- migrate:up migrations/db/migrations/20230529180330_alter_api_roles_for_inherit.sql

    ALTER ROLE authenticated inherit;
    ALTER ROLE anon inherit;
    ALTER ROLE service_role inherit;

    GRANT pgsodium_keyholder to service_role;

    -- migrate:down

    -- migrate:up migrations/db/migrations/10000000000000_demote-postgres.sql

    -- demote postgres user
    GRANT ALL ON DATABASE postgres TO postgres;
    GRANT ALL ON SCHEMA auth TO postgres;
    GRANT ALL ON SCHEMA extensions TO postgres;
    GRANT ALL ON SCHEMA storage TO postgres;
    GRANT ALL ON ALL TABLES IN SCHEMA auth TO postgres;
    GRANT ALL ON ALL TABLES IN SCHEMA storage TO postgres;
    GRANT ALL ON ALL TABLES IN SCHEMA extensions TO postgres;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA auth TO postgres;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA storage TO postgres;
    GRANT ALL ON ALL SEQUENCES IN SCHEMA extensions TO postgres;
    GRANT ALL ON ALL ROUTINES IN SCHEMA auth TO postgres;
    GRANT ALL ON ALL ROUTINES IN SCHEMA storage TO postgres;
    GRANT ALL ON ALL ROUTINES IN SCHEMA extensions TO postgres;
    -- ALTER ROLE postgres NOSUPERUSER CREATEDB CREATEROLE LOGIN REPLICATION BYPASSRLS;

    -- migrate:down

[pk992]

Thank you very much! I noticed pg_tle was not useable and thus I couldn't install dbdev. This helped:

  postgresql:
    # https://github.com/supabase/postgres/blob/develop/ansible/files/postgresql_config/postgresql.conf.j2
    # all extensions like in the file above but not pgsodium, it fails with:
    # "The getkey script \"/usr/share/postgresql/15/extension/pgsodium_getkey\" does not exists."
    shared_preload_libraries:
      - 'pg_stat_statements'
      - 'pg_stat_monitor'
      - 'pgaudit'
      - 'plpgsql'
      - 'plpgsql_check'
      - 'pg_cron'
      - 'pg_net'
      - 'timescaledb'
      - 'auto_explain'
      - 'pg_tle'

The supabase/postgres image does also mount other .sql files for init in their docker selfhosting example. They were needed for the webhook functionality: https://github.com/supabase/supabase/tree/master/docker/volumes/db

[h0lybyte]

We are currently in the process of doing this right now, do you guys have any references?

[nthtrung09it]

@pk992 @jniclas hey, do you have fully working helm charts for supabase? I'm very new to the cloudnativepg. Thanks in advance.

[jniclas]

@nthtrung09it yes, I started with https://github.com/supabase-community/supabase-kubernetes and adjusted it to my needs. Like adding a nuxt frontend and removing the default db and put a cloudnativePB database in place and made sure to still have the init sql script in place charts/supabase/templates/db/initdb.config.yaml . And you may need to add your own tenant to make realtime working. See https://github.com/supabase/realtime?tab=readme-ov-file#server-setup. (But did not test edge functions yet)

[nthtrung09it]

Thanks @jniclas . Which version of supabase/postgres are you using? I see the latest version is 15.6.1.139. And do you update the new supavisor stuff which introduced last month in the supavisor MR?

[jniclas]

@nthtrung09it I just noticed that I switched back on my deployment to this repository here again with Postgres version 15.1.1.34. ...I should update in the next weeks. And I did not yet checked Supavisor. CloudnativePG has something similar, see https://cloudnative-pg.io/documentation/1.23/connection_pooling/, need to check at some point.