1073 lines
27 KiB
PL/PgSQL
1073 lines
27 KiB
PL/PgSQL
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET xmloption = content;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
--
|
|
-- Name: auth; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA auth;
|
|
|
|
|
|
--
|
|
-- Name: extensions; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA extensions;
|
|
|
|
|
|
--
|
|
-- Name: graphql; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA graphql;
|
|
|
|
|
|
--
|
|
-- Name: graphql_public; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA graphql_public;
|
|
|
|
|
|
--
|
|
-- Name: pgbouncer; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA pgbouncer;
|
|
|
|
|
|
--
|
|
-- Name: pgsodium; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA pgsodium;
|
|
|
|
|
|
--
|
|
-- Name: pgsodium; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgsodium WITH SCHEMA pgsodium;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION pgsodium; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION pgsodium IS 'Pgsodium is a modern cryptography library for Postgres.';
|
|
|
|
|
|
--
|
|
-- Name: realtime; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA realtime;
|
|
|
|
|
|
--
|
|
-- Name: storage; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA storage;
|
|
|
|
|
|
--
|
|
-- Name: vault; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA vault;
|
|
|
|
|
|
--
|
|
-- Name: pg_graphql; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pg_graphql WITH SCHEMA graphql;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION pg_graphql; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION pg_graphql IS 'pg_graphql: GraphQL support';
|
|
|
|
|
|
--
|
|
-- Name: pg_stat_statements; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA extensions;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION pg_stat_statements; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION pg_stat_statements IS 'track planning and execution statistics of all SQL statements executed';
|
|
|
|
|
|
--
|
|
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';
|
|
|
|
|
|
--
|
|
-- Name: pgjwt; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgjwt WITH SCHEMA extensions;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION pgjwt; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION pgjwt IS 'JSON Web Token API for Postgresql';
|
|
|
|
|
|
--
|
|
-- Name: supabase_vault; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS supabase_vault WITH SCHEMA vault;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION supabase_vault; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION supabase_vault IS 'Supabase Vault Extension';
|
|
|
|
|
|
--
|
|
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
|
|
--
|
|
|
|
COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
|
|
|
|
|
|
--
|
|
-- Name: email(); Type: FUNCTION; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION auth.email() RETURNS text
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select nullif(current_setting('request.jwt.claim.email', true), '')::text;
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: role(); Type: FUNCTION; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION auth.role() RETURNS text
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select nullif(current_setting('request.jwt.claim.role', true), '')::text;
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: uid(); Type: FUNCTION; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION auth.uid() RETURNS uuid
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid;
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: grant_pg_cron_access(); Type: FUNCTION; Schema: extensions; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION extensions.grant_pg_cron_access() RETURNS event_trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT
|
|
FROM pg_event_trigger_ddl_commands() AS ev
|
|
JOIN pg_extension AS ext
|
|
ON ev.objid = ext.oid
|
|
WHERE ext.extname = 'pg_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;
|
|
revoke all on table cron.job from postgres;
|
|
grant select on table cron.job to postgres with grant option;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: FUNCTION grant_pg_cron_access(); Type: COMMENT; Schema: extensions; Owner: -
|
|
--
|
|
|
|
COMMENT ON FUNCTION extensions.grant_pg_cron_access() IS 'Grants access to pg_cron';
|
|
|
|
|
|
--
|
|
-- Name: grant_pg_graphql_access(); Type: FUNCTION; Schema: extensions; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION extensions.grant_pg_graphql_access() RETURNS event_trigger
|
|
LANGUAGE plpgsql
|
|
AS $_$
|
|
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;
|
|
|
|
-- Allow postgres role to allow granting usage on graphql and graphql_public schemas to custom roles
|
|
grant usage on schema graphql_public to postgres with grant option;
|
|
grant usage on schema graphql to postgres with grant option;
|
|
END IF;
|
|
|
|
END;
|
|
$_$;
|
|
|
|
|
|
--
|
|
-- Name: FUNCTION grant_pg_graphql_access(); Type: COMMENT; Schema: extensions; Owner: -
|
|
--
|
|
|
|
COMMENT ON FUNCTION extensions.grant_pg_graphql_access() IS 'Grants access to pg_graphql';
|
|
|
|
|
|
--
|
|
-- Name: grant_pg_net_access(); Type: FUNCTION; Schema: extensions; Owner: -
|
|
--
|
|
|
|
CREATE 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;
|
|
|
|
IF EXISTS (
|
|
SELECT FROM pg_extension
|
|
WHERE extname = 'pg_net'
|
|
-- all versions in use on existing projects as of 2025-02-20
|
|
-- version 0.12.0 onwards don't need these applied
|
|
AND extversion IN ('0.2', '0.6', '0.7', '0.7.1', '0.8', '0.10.0', '0.11.0')
|
|
) THEN
|
|
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 IF;
|
|
END;
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: FUNCTION grant_pg_net_access(); Type: COMMENT; Schema: extensions; Owner: -
|
|
--
|
|
|
|
COMMENT ON FUNCTION extensions.grant_pg_net_access() IS 'Grants access to pg_net';
|
|
|
|
|
|
--
|
|
-- Name: pgrst_ddl_watch(); Type: FUNCTION; Schema: extensions; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION extensions.pgrst_ddl_watch() RETURNS event_trigger
|
|
LANGUAGE plpgsql
|
|
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; $$;
|
|
|
|
|
|
--
|
|
-- Name: pgrst_drop_watch(); Type: FUNCTION; Schema: extensions; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION extensions.pgrst_drop_watch() RETURNS event_trigger
|
|
LANGUAGE plpgsql
|
|
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; $$;
|
|
|
|
|
|
--
|
|
-- Name: set_graphql_placeholder(); Type: FUNCTION; Schema: extensions; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION extensions.set_graphql_placeholder() RETURNS event_trigger
|
|
LANGUAGE plpgsql
|
|
AS $_$
|
|
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;
|
|
$_$;
|
|
|
|
|
|
--
|
|
-- Name: FUNCTION set_graphql_placeholder(); Type: COMMENT; Schema: extensions; Owner: -
|
|
--
|
|
|
|
COMMENT ON FUNCTION extensions.set_graphql_placeholder() IS 'Reintroduces placeholder function for graphql_public.graphql';
|
|
|
|
|
|
--
|
|
-- Name: get_auth(text); Type: FUNCTION; Schema: pgbouncer; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION pgbouncer.get_auth(p_usename text) RETURNS TABLE(username text, password text)
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
RAISE WARNING 'PgBouncer auth request: %', p_usename;
|
|
|
|
RETURN QUERY
|
|
SELECT usename::TEXT, passwd::TEXT FROM pg_catalog.pg_shadow
|
|
WHERE usename = p_usename;
|
|
END;
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: extension(text); Type: FUNCTION; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION storage.extension(name text) RETURNS text
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
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
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: filename(text); Type: FUNCTION; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION storage.filename(name text) RETURNS text
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
_parts text[];
|
|
BEGIN
|
|
select string_to_array(name, '/') into _parts;
|
|
return _parts[array_length(_parts,1)];
|
|
END
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: foldername(text); Type: FUNCTION; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION storage.foldername(name text) RETURNS text[]
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
_parts text[];
|
|
BEGIN
|
|
select string_to_array(name, '/') into _parts;
|
|
return _parts[1:array_length(_parts,1)-1];
|
|
END
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: search(text, text, integer, integer, integer); Type: FUNCTION; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION storage.search(prefix text, bucketname text, limits integer DEFAULT 100, levels integer DEFAULT 1, offsets integer DEFAULT 0) RETURNS TABLE(name text, id uuid, updated_at timestamp with time zone, created_at timestamp with time zone, last_accessed_at timestamp with time zone, metadata jsonb)
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
_bucketId text;
|
|
BEGIN
|
|
-- will be replaced by migrations when server starts
|
|
-- saving space for cloud-init
|
|
END
|
|
$$;
|
|
|
|
|
|
--
|
|
-- Name: secrets_encrypt_secret_secret(); Type: FUNCTION; Schema: vault; Owner: -
|
|
--
|
|
|
|
CREATE FUNCTION vault.secrets_encrypt_secret_secret() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
new.secret = CASE WHEN new.secret IS NULL THEN NULL ELSE
|
|
CASE WHEN new.key_id IS NULL THEN NULL ELSE pg_catalog.encode(
|
|
pgsodium.crypto_aead_det_encrypt(
|
|
pg_catalog.convert_to(new.secret, 'utf8'),
|
|
pg_catalog.convert_to((new.id::text || new.description::text || new.created_at::text || new.updated_at::text)::text, 'utf8'),
|
|
new.key_id::uuid,
|
|
new.nonce
|
|
),
|
|
'base64') END END;
|
|
RETURN new;
|
|
END;
|
|
$$;
|
|
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- Name: audit_log_entries; Type: TABLE; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE TABLE auth.audit_log_entries (
|
|
instance_id uuid,
|
|
id uuid NOT NULL,
|
|
payload json,
|
|
created_at timestamp with time zone
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: TABLE audit_log_entries; Type: COMMENT; Schema: auth; Owner: -
|
|
--
|
|
|
|
COMMENT ON TABLE auth.audit_log_entries IS 'Auth: Audit trail for user actions.';
|
|
|
|
|
|
--
|
|
-- Name: instances; Type: TABLE; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE TABLE auth.instances (
|
|
id uuid NOT NULL,
|
|
uuid uuid,
|
|
raw_base_config text,
|
|
created_at timestamp with time zone,
|
|
updated_at timestamp with time zone
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: TABLE instances; Type: COMMENT; Schema: auth; Owner: -
|
|
--
|
|
|
|
COMMENT ON TABLE auth.instances IS 'Auth: Manages users across multiple sites.';
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens; Type: TABLE; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE TABLE auth.refresh_tokens (
|
|
instance_id uuid,
|
|
id bigint NOT NULL,
|
|
token character varying(255),
|
|
user_id character varying(255),
|
|
revoked boolean,
|
|
created_at timestamp with time zone,
|
|
updated_at timestamp with time zone
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: TABLE refresh_tokens; Type: COMMENT; Schema: auth; Owner: -
|
|
--
|
|
|
|
COMMENT ON TABLE auth.refresh_tokens IS 'Auth: Store of tokens used to refresh JWT tokens once they expire.';
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens_id_seq; Type: SEQUENCE; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE auth.refresh_tokens_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens_id_seq; Type: SEQUENCE OWNED BY; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE auth.refresh_tokens_id_seq OWNED BY auth.refresh_tokens.id;
|
|
|
|
|
|
--
|
|
-- Name: schema_migrations; Type: TABLE; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE TABLE auth.schema_migrations (
|
|
version character varying(255) NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: TABLE schema_migrations; Type: COMMENT; Schema: auth; Owner: -
|
|
--
|
|
|
|
COMMENT ON TABLE auth.schema_migrations IS 'Auth: Manages updates to the auth system.';
|
|
|
|
|
|
--
|
|
-- Name: users; Type: TABLE; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE TABLE auth.users (
|
|
instance_id uuid,
|
|
id uuid NOT NULL,
|
|
aud character varying(255),
|
|
role character varying(255),
|
|
email character varying(255),
|
|
encrypted_password character varying(255),
|
|
confirmed_at timestamp with time zone,
|
|
invited_at timestamp with time zone,
|
|
confirmation_token character varying(255),
|
|
confirmation_sent_at timestamp with time zone,
|
|
recovery_token character varying(255),
|
|
recovery_sent_at timestamp with time zone,
|
|
email_change_token character varying(255),
|
|
email_change character varying(255),
|
|
email_change_sent_at timestamp with time zone,
|
|
last_sign_in_at timestamp with time zone,
|
|
raw_app_meta_data jsonb,
|
|
raw_user_meta_data jsonb,
|
|
is_super_admin boolean,
|
|
created_at timestamp with time zone,
|
|
updated_at timestamp with time zone
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: TABLE users; Type: COMMENT; Schema: auth; Owner: -
|
|
--
|
|
|
|
COMMENT ON TABLE auth.users IS 'Auth: Stores user login data within a secure schema.';
|
|
|
|
|
|
--
|
|
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
|
|
--
|
|
|
|
CREATE TABLE public.schema_migrations (
|
|
version character varying(128) NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: buckets; Type: TABLE; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE TABLE storage.buckets (
|
|
id text NOT NULL,
|
|
name text NOT NULL,
|
|
owner uuid,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: migrations; Type: TABLE; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE TABLE storage.migrations (
|
|
id integer NOT NULL,
|
|
name character varying(100) NOT NULL,
|
|
hash character varying(40) NOT NULL,
|
|
executed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: objects; Type: TABLE; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE TABLE storage.objects (
|
|
id uuid DEFAULT extensions.uuid_generate_v4() NOT NULL,
|
|
bucket_id text,
|
|
name text,
|
|
owner uuid,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
last_accessed_at timestamp with time zone DEFAULT now(),
|
|
metadata jsonb
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: decrypted_secrets; Type: VIEW; Schema: vault; Owner: -
|
|
--
|
|
|
|
CREATE VIEW vault.decrypted_secrets AS
|
|
SELECT secrets.id,
|
|
secrets.name,
|
|
secrets.description,
|
|
secrets.secret,
|
|
CASE
|
|
WHEN (secrets.secret IS NULL) THEN NULL::text
|
|
ELSE
|
|
CASE
|
|
WHEN (secrets.key_id IS NULL) THEN NULL::text
|
|
ELSE convert_from(pgsodium.crypto_aead_det_decrypt(decode(secrets.secret, 'base64'::text), convert_to(((((secrets.id)::text || secrets.description) || (secrets.created_at)::text) || (secrets.updated_at)::text), 'utf8'::name), secrets.key_id, secrets.nonce), 'utf8'::name)
|
|
END
|
|
END AS decrypted_secret,
|
|
secrets.key_id,
|
|
secrets.nonce,
|
|
secrets.created_at,
|
|
secrets.updated_at
|
|
FROM vault.secrets;
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens id; Type: DEFAULT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.refresh_tokens ALTER COLUMN id SET DEFAULT nextval('auth.refresh_tokens_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: audit_log_entries audit_log_entries_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.audit_log_entries
|
|
ADD CONSTRAINT audit_log_entries_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: instances instances_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.instances
|
|
ADD CONSTRAINT instances_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens refresh_tokens_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.refresh_tokens
|
|
ADD CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.schema_migrations
|
|
ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
|
|
|
|
|
|
--
|
|
-- Name: users users_email_key; Type: CONSTRAINT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.users
|
|
ADD CONSTRAINT users_email_key UNIQUE (email);
|
|
|
|
|
|
--
|
|
-- Name: users users_pkey; Type: CONSTRAINT; Schema: auth; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY auth.users
|
|
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY public.schema_migrations
|
|
ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
|
|
|
|
|
|
--
|
|
-- Name: buckets buckets_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.buckets
|
|
ADD CONSTRAINT buckets_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: migrations migrations_name_key; Type: CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.migrations
|
|
ADD CONSTRAINT migrations_name_key UNIQUE (name);
|
|
|
|
|
|
--
|
|
-- Name: migrations migrations_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.migrations
|
|
ADD CONSTRAINT migrations_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: objects objects_pkey; Type: CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.objects
|
|
ADD CONSTRAINT objects_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: audit_logs_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE INDEX audit_logs_instance_id_idx ON auth.audit_log_entries USING btree (instance_id);
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE INDEX refresh_tokens_instance_id_idx ON auth.refresh_tokens USING btree (instance_id);
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens_instance_id_user_id_idx; Type: INDEX; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE INDEX refresh_tokens_instance_id_user_id_idx ON auth.refresh_tokens USING btree (instance_id, user_id);
|
|
|
|
|
|
--
|
|
-- Name: refresh_tokens_token_idx; Type: INDEX; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE INDEX refresh_tokens_token_idx ON auth.refresh_tokens USING btree (token);
|
|
|
|
|
|
--
|
|
-- Name: users_instance_id_email_idx; Type: INDEX; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id, email);
|
|
|
|
|
|
--
|
|
-- Name: users_instance_id_idx; Type: INDEX; Schema: auth; Owner: -
|
|
--
|
|
|
|
CREATE INDEX users_instance_id_idx ON auth.users USING btree (instance_id);
|
|
|
|
|
|
--
|
|
-- Name: bname; Type: INDEX; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE UNIQUE INDEX bname ON storage.buckets USING btree (name);
|
|
|
|
|
|
--
|
|
-- Name: bucketid_objname; Type: INDEX; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE UNIQUE INDEX bucketid_objname ON storage.objects USING btree (bucket_id, name);
|
|
|
|
|
|
--
|
|
-- Name: name_prefix_search; Type: INDEX; Schema: storage; Owner: -
|
|
--
|
|
|
|
CREATE INDEX name_prefix_search ON storage.objects USING btree (name text_pattern_ops);
|
|
|
|
|
|
--
|
|
-- Name: buckets buckets_owner_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.buckets
|
|
ADD CONSTRAINT buckets_owner_fkey FOREIGN KEY (owner) REFERENCES auth.users(id);
|
|
|
|
|
|
--
|
|
-- Name: objects objects_bucketId_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.objects
|
|
ADD CONSTRAINT "objects_bucketId_fkey" FOREIGN KEY (bucket_id) REFERENCES storage.buckets(id);
|
|
|
|
|
|
--
|
|
-- Name: objects objects_owner_fkey; Type: FK CONSTRAINT; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY storage.objects
|
|
ADD CONSTRAINT objects_owner_fkey FOREIGN KEY (owner) REFERENCES auth.users(id);
|
|
|
|
|
|
--
|
|
-- Name: objects; Type: ROW SECURITY; Schema: storage; Owner: -
|
|
--
|
|
|
|
ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;
|
|
|
|
--
|
|
-- Name: supabase_realtime; Type: PUBLICATION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE PUBLICATION supabase_realtime WITH (publish = 'insert, update, delete, truncate');
|
|
|
|
|
|
--
|
|
-- Name: issue_graphql_placeholder; Type: EVENT TRIGGER; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EVENT TRIGGER issue_graphql_placeholder ON sql_drop
|
|
WHEN TAG IN ('DROP EXTENSION')
|
|
EXECUTE FUNCTION extensions.set_graphql_placeholder();
|
|
|
|
|
|
--
|
|
-- Name: issue_pg_cron_access; Type: EVENT TRIGGER; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EVENT TRIGGER issue_pg_cron_access ON ddl_command_end
|
|
WHEN TAG IN ('CREATE EXTENSION')
|
|
EXECUTE FUNCTION extensions.grant_pg_cron_access();
|
|
|
|
|
|
--
|
|
-- Name: issue_pg_graphql_access; Type: EVENT TRIGGER; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EVENT TRIGGER issue_pg_graphql_access ON ddl_command_end
|
|
WHEN TAG IN ('CREATE FUNCTION')
|
|
EXECUTE FUNCTION extensions.grant_pg_graphql_access();
|
|
|
|
|
|
--
|
|
-- Name: issue_pg_net_access; Type: EVENT TRIGGER; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EVENT TRIGGER issue_pg_net_access ON ddl_command_end
|
|
WHEN TAG IN ('CREATE EXTENSION')
|
|
EXECUTE FUNCTION extensions.grant_pg_net_access();
|
|
|
|
|
|
--
|
|
-- Name: pgrst_ddl_watch; Type: EVENT TRIGGER; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EVENT TRIGGER pgrst_ddl_watch ON ddl_command_end
|
|
EXECUTE FUNCTION extensions.pgrst_ddl_watch();
|
|
|
|
|
|
--
|
|
-- Name: pgrst_drop_watch; Type: EVENT TRIGGER; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EVENT TRIGGER pgrst_drop_watch ON sql_drop
|
|
EXECUTE FUNCTION extensions.pgrst_drop_watch();
|
|
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|
|
|
|
--
|
|
-- Dbmate schema migrations
|
|
--
|
|
|