supabase-postgres.git / summary / log / commit / refs
commit 8698fe81703a4eade3b18bbe0adddd89628d15c2
Author: Bobbie Soedirgo <31685197+soedirgo@users.noreply.github.com>
Commit: GitHub <noreply@github.com>
Date: Wed Nov 26 07:48:05 2025 +0000
chore: remove precreated Storage objects (#1911)
* chore: remove precreated storage objs from AMI build
Let Storage create these objects in its migrations.
* test: remove storage tables in e2e test
We no longer precreate Storage objects
* chore: update schema snapshots
* chore: bump versions
ansible/vars.yml | 6 +-
.../init-scripts/00000000000002-storage-schema.sql | 125 +--
.../db/init-scripts/00000000000003-post-setup.sql | 11 +-
.../migrations/10000000000000_demote-postgres.sql | 13 +-
...0421084701_revoke_admin_roles_from_postgres.sql | 21 +-
...torage_tables_to_postgres_with_grant_option.sql | 10 +-
...torage_schema_to_postgres_with_grant_option.sql | 7 +-
migrations/schema-15.sql | 191 ----
migrations/schema-17.sql | 191 ----
migrations/schema-orioledb-17.sql | 191 ----
migrations/schema.sql | 997 ---------------------
nix/tests/expected/storage.out | 135 +--
nix/tests/sql/storage.sql | 11 +-
testinfra/test_ami_nix.py | 35 +-
14 files changed, 91 insertions(+), 1853 deletions(-)
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 5423e936..08828ce0 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -10,9 +10,9 @@ postgres_major:
# Full version strings for each major version
postgres_release:
- postgresorioledb-17: "17.6.0.011-orioledb-INDATA-255"
- postgres17: "17.6.1.054-INDATA-255"
- postgres15: "15.14.1.054-INDATA-255"
+ postgresorioledb-17: "17.6.0.012-orioledb"
+ postgres17: "17.6.1.055"
+ postgres15: "15.14.1.055"
# Non Postgres Extensions
pgbouncer_release: 1.19.0
diff --git a/migrations/db/init-scripts/00000000000002-storage-schema.sql b/migrations/db/init-scripts/00000000000002-storage-schema.sql
index 40503e4d..6c5081ea 100644
--- a/migrations/db/init-scripts/00000000000002-storage-schema.sql
+++ b/migrations/db/init-scripts/00000000000002-storage-schema.sql
@@ -2,119 +2,20 @@
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;
+GRANT CREATE ON DATABASE postgres TO supabase_storage_admin;
+
+do $$
+begin
+ if exists (select from pg_namespace where nspname = 'storage') then
+ 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;
+
+ grant all on schema storage to supabase_storage_admin with grant option;
+ end if;
+end $$;
-- migrate:down
diff --git a/migrations/db/init-scripts/00000000000003-post-setup.sql b/migrations/db/init-scripts/00000000000003-post-setup.sql
index 47cdd13a..3bfa90b3 100644
--- a/migrations/db/init-scripts/00000000000003-post-setup.sql
+++ b/migrations/db/init-scripts/00000000000003-post-setup.sql
@@ -105,15 +105,20 @@ 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;
+do $$
+begin
+ if exists (select from pg_namespace where nspname = 'storage') then
+ GRANT ALL ON SCHEMA storage TO dashboard_user;
+ GRANT ALL ON ALL SEQUENCES IN SCHEMA storage TO dashboard_user;
+ GRANT ALL ON ALL ROUTINES IN SCHEMA storage TO dashboard_user;
+ end if;
+end $$;
-- migrate:down
diff --git a/migrations/db/migrations/10000000000000_demote-postgres.sql b/migrations/db/migrations/10000000000000_demote-postgres.sql
index 1f7e2e24..c9279300 100644
--- a/migrations/db/migrations/10000000000000_demote-postgres.sql
+++ b/migrations/db/migrations/10000000000000_demote-postgres.sql
@@ -4,16 +4,21 @@
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;
+do $$
+begin
+ if exists (select from pg_namespace where nspname = 'storage') then
+ GRANT ALL ON SCHEMA storage TO postgres;
+ GRANT ALL ON ALL TABLES IN SCHEMA storage TO postgres;
+ GRANT ALL ON ALL SEQUENCES IN SCHEMA storage TO postgres;
+ GRANT ALL ON ALL ROUTINES IN SCHEMA storage TO postgres;
+ end if;
+end $$;
ALTER ROLE postgres NOSUPERUSER CREATEDB CREATEROLE LOGIN REPLICATION BYPASSRLS;
-- migrate:down
diff --git a/migrations/db/migrations/20250421084701_revoke_admin_roles_from_postgres.sql b/migrations/db/migrations/20250421084701_revoke_admin_roles_from_postgres.sql
index 4c5c48b7..16b697a3 100644
--- a/migrations/db/migrations/20250421084701_revoke_admin_roles_from_postgres.sql
+++ b/migrations/db/migrations/20250421084701_revoke_admin_roles_from_postgres.sql
@@ -1,10 +1,25 @@
-- migrate:up
revoke supabase_storage_admin from postgres;
-revoke create on schema storage from postgres;
-revoke all on storage.migrations from anon, authenticated, service_role, postgres;
+do $$
+begin
+ if exists (select from pg_namespace where nspname = 'storage') then
+ revoke create on schema storage from postgres;
+ end if;
+end $$;
+do $$
+begin
+ if exists (select from pg_class where relnamespace = (select oid from pg_namespace where nspname = 'storage') and relname = 'migrations') then
+ revoke all on storage.migrations from anon, authenticated, service_role, postgres;
+ end if;
+end $$;
revoke supabase_auth_admin from postgres;
revoke create on schema auth from postgres;
-revoke all on auth.schema_migrations from dashboard_user, postgres;
+do $$
+begin
+ if exists (select from pg_class where relnamespace = 'auth'::regnamespace and relname = 'schema_migrations') then
+ revoke all on auth.schema_migrations from dashboard_user, postgres;
+ end if;
+end $$;
-- migrate:down
diff --git a/migrations/db/migrations/20250623125453_tmp_grant_storage_tables_to_postgres_with_grant_option.sql b/migrations/db/migrations/20250623125453_tmp_grant_storage_tables_to_postgres_with_grant_option.sql
index 465aee22..b499f927 100644
--- a/migrations/db/migrations/20250623125453_tmp_grant_storage_tables_to_postgres_with_grant_option.sql
+++ b/migrations/db/migrations/20250623125453_tmp_grant_storage_tables_to_postgres_with_grant_option.sql
@@ -1,6 +1,14 @@
-- migrate:up
-- TODO: remove this migration once STORAGE-211 is completed
-- DRI: bobbie
-grant all on storage.buckets, storage.objects to postgres with grant option;
+do $$
+begin
+ if exists (select from pg_class where relnamespace = (select oid from pg_namespace where nspname = 'storage') and relname = 'buckets') then
+ grant all on storage.buckets to postgres with grant option;
+ end if;
+ if exists (select from pg_class where relnamespace = (select oid from pg_namespace where nspname = 'storage') and relname = 'objects') then
+ grant all on storage.objects to postgres with grant option;
+ end if;
+end $$;
-- migrate:down
diff --git a/migrations/db/migrations/20250709135250_grant_storage_schema_to_postgres_with_grant_option.sql b/migrations/db/migrations/20250709135250_grant_storage_schema_to_postgres_with_grant_option.sql
index 604ff998..74409248 100644
--- a/migrations/db/migrations/20250709135250_grant_storage_schema_to_postgres_with_grant_option.sql
+++ b/migrations/db/migrations/20250709135250_grant_storage_schema_to_postgres_with_grant_option.sql
@@ -1,4 +1,9 @@
-- migrate:up
-grant usage on schema storage to postgres with grant option;
+do $$
+begin
+ if exists (select from pg_namespace where nspname = 'storage') then
+ grant usage on schema storage to postgres with grant option;
+ end if;
+end $$;
-- migrate:down
diff --git a/migrations/schema-15.sql b/migrations/schema-15.sql
index 1f1d9849..b160ec91 100644
--- a/migrations/schema-15.sql
+++ b/migrations/schema-15.sql
@@ -495,73 +495,6 @@ 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
-$$;
-
-
SET default_tablespace = '';
SET default_table_access_method = heap;
@@ -707,47 +640,6 @@ CREATE TABLE public.schema_migrations (
);
---
--- 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: refresh_tokens id; Type: DEFAULT; Schema: auth; Owner: -
--
@@ -811,38 +703,6 @@ 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: -
--
@@ -885,57 +745,6 @@ CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id,
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: -
--
diff --git a/migrations/schema-17.sql b/migrations/schema-17.sql
index e0b353e9..c1f73fca 100644
--- a/migrations/schema-17.sql
+++ b/migrations/schema-17.sql
@@ -496,73 +496,6 @@ 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
-$$;
-
-
SET default_tablespace = '';
SET default_table_access_method = heap;
@@ -708,47 +641,6 @@ CREATE TABLE public.schema_migrations (
);
---
--- 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: refresh_tokens id; Type: DEFAULT; Schema: auth; Owner: -
--
@@ -812,38 +704,6 @@ 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: -
--
@@ -886,57 +746,6 @@ CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id,
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: -
--
diff --git a/migrations/schema-orioledb-17.sql b/migrations/schema-orioledb-17.sql
index 7abb6312..d8b8ddac 100644
--- a/migrations/schema-orioledb-17.sql
+++ b/migrations/schema-orioledb-17.sql
@@ -510,73 +510,6 @@ 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
-$$;
-
-
SET default_tablespace = '';
SET default_table_access_method = orioledb;
@@ -722,47 +655,6 @@ CREATE TABLE public.schema_migrations (
);
---
--- 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: refresh_tokens id; Type: DEFAULT; Schema: auth; Owner: -
--
@@ -826,38 +718,6 @@ 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: -
--
@@ -900,57 +760,6 @@ CREATE INDEX users_instance_id_email_idx ON auth.users USING btree (instance_id,
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: -
--
diff --git a/migrations/schema.sql b/migrations/schema.sql
deleted file mode 100644
index cb031f79..00000000
--- a/migrations/schema.sql
+++ /dev/null
@@ -1,997 +0,0 @@
-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: 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;
-
- 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;
-$$;
-
-
---
--- 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
-$$;
-
-
-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: 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
---
-
diff --git a/nix/tests/expected/storage.out b/nix/tests/expected/storage.out
index c6edf49e..8586cadb 100644
--- a/nix/tests/expected/storage.out
+++ b/nix/tests/expected/storage.out
@@ -35,12 +35,9 @@ group by
ns.nspname, c.relname, r.rolname, c.relrowsecurity
order by
c.relname;
- schema_name | table_name | owner | rls_enabled | rls_policies
--------------+------------+------------------------+-------------+--------------
- storage | buckets | supabase_storage_admin | f |
- storage | migrations | supabase_storage_admin | f |
- storage | objects | supabase_storage_admin | t |
-(3 rows)
+ schema_name | table_name | owner | rls_enabled | rls_policies
+-------------+------------+-------+-------------+--------------
+(0 rows)
-- storage schema objects with roles privileges
select
@@ -65,86 +62,9 @@ order by
c.relname,
r.rolname,
a.privilege_type;
- schema_name | table_name | role_name | privilege_type | is_grantable
--------------+------------+------------------------+----------------+--------------
- storage | buckets | anon | DELETE | f
- storage | buckets | anon | INSERT | f
- storage | buckets | anon | REFERENCES | f
- storage | buckets | anon | SELECT | f
- storage | buckets | anon | TRIGGER | f
- storage | buckets | anon | TRUNCATE | f
- storage | buckets | anon | UPDATE | f
- storage | buckets | authenticated | DELETE | f
- storage | buckets | authenticated | INSERT | f
- storage | buckets | authenticated | REFERENCES | f
- storage | buckets | authenticated | SELECT | f
- storage | buckets | authenticated | TRIGGER | f
- storage | buckets | authenticated | TRUNCATE | f
- storage | buckets | authenticated | UPDATE | f
- storage | buckets | postgres | DELETE | t
- storage | buckets | postgres | INSERT | t
- storage | buckets | postgres | REFERENCES | t
- storage | buckets | postgres | SELECT | t
- storage | buckets | postgres | TRIGGER | t
- storage | buckets | postgres | TRUNCATE | t
- storage | buckets | postgres | UPDATE | t
- storage | buckets | service_role | DELETE | f
- storage | buckets | service_role | INSERT | f
- storage | buckets | service_role | REFERENCES | f
- storage | buckets | service_role | SELECT | f
- storage | buckets | service_role | TRIGGER | f
- storage | buckets | service_role | TRUNCATE | f
- storage | buckets | service_role | UPDATE | f
- storage | buckets | supabase_storage_admin | DELETE | f
- storage | buckets | supabase_storage_admin | INSERT | f
- storage | buckets | supabase_storage_admin | REFERENCES | f
- storage | buckets | supabase_storage_admin | SELECT | f
- storage | buckets | supabase_storage_admin | TRIGGER | f
- storage | buckets | supabase_storage_admin | TRUNCATE | f
- storage | buckets | supabase_storage_admin | UPDATE | f
- storage | migrations | supabase_storage_admin | DELETE | f
- storage | migrations | supabase_storage_admin | INSERT | f
- storage | migrations | supabase_storage_admin | REFERENCES | f
- storage | migrations | supabase_storage_admin | SELECT | f
- storage | migrations | supabase_storage_admin | TRIGGER | f
- storage | migrations | supabase_storage_admin | TRUNCATE | f
- storage | migrations | supabase_storage_admin | UPDATE | f
- storage | objects | anon | DELETE | f
- storage | objects | anon | INSERT | f
- storage | objects | anon | REFERENCES | f
- storage | objects | anon | SELECT | f
- storage | objects | anon | TRIGGER | f
- storage | objects | anon | TRUNCATE | f
- storage | objects | anon | UPDATE | f
- storage | objects | authenticated | DELETE | f
- storage | objects | authenticated | INSERT | f
- storage | objects | authenticated | REFERENCES | f
- storage | objects | authenticated | SELECT | f
- storage | objects | authenticated | TRIGGER | f
- storage | objects | authenticated | TRUNCATE | f
- storage | objects | authenticated | UPDATE | f
- storage | objects | postgres | DELETE | t
- storage | objects | postgres | INSERT | t
- storage | objects | postgres | REFERENCES | t
- storage | objects | postgres | SELECT | t
- storage | objects | postgres | TRIGGER | t
- storage | objects | postgres | TRUNCATE | t
- storage | objects | postgres | UPDATE | t
- storage | objects | service_role | DELETE | f
- storage | objects | service_role | INSERT | f
- storage | objects | service_role | REFERENCES | f
- storage | objects | service_role | SELECT | f
- storage | objects | service_role | TRIGGER | f
- storage | objects | service_role | TRUNCATE | f
- storage | objects | service_role | UPDATE | f
- storage | objects | supabase_storage_admin | DELETE | f
- storage | objects | supabase_storage_admin | INSERT | f
- storage | objects | supabase_storage_admin | REFERENCES | f
- storage | objects | supabase_storage_admin | SELECT | f
- storage | objects | supabase_storage_admin | TRIGGER | f
- storage | objects | supabase_storage_admin | TRUNCATE | f
- storage | objects | supabase_storage_admin | UPDATE | f
-(77 rows)
+ schema_name | table_name | role_name | privilege_type | is_grantable
+-------------+------------+-----------+----------------+--------------
+(0 rows)
-- storage indexes with owners
select
@@ -166,16 +86,9 @@ where
ns.nspname = 'storage'
order by
t.relname, i.relname;
- table_schema | table_name | index_name | index_owner
---------------+------------+---------------------+------------------------
- storage | buckets | bname | supabase_storage_admin
- storage | buckets | buckets_pkey | supabase_storage_admin
- storage | migrations | migrations_name_key | supabase_storage_admin
- storage | migrations | migrations_pkey | supabase_storage_admin
- storage | objects | bucketid_objname | supabase_storage_admin
- storage | objects | name_prefix_search | supabase_storage_admin
- storage | objects | objects_pkey | supabase_storage_admin
-(7 rows)
+ table_schema | table_name | index_name | index_owner
+--------------+------------+------------+-------------
+(0 rows)
-- storage schema functions with owners
select
@@ -192,36 +105,20 @@ where
n.nspname = 'storage'
order by
p.proname;
- schema_name | function_name | owner
--------------+---------------+------------------------
- storage | extension | supabase_storage_admin
- storage | filename | supabase_storage_admin
- storage | foldername | supabase_storage_admin
- storage | search | supabase_storage_admin
-(4 rows)
-
--- storage service migrations
-select * from storage.migrations ;
- id | name | hash | executed_at
-----+------+------+-------------
+ schema_name | function_name | owner
+-------------+---------------+-------
(0 rows)
-- postgres can grant storage privileges to custom roles
create role r;
grant r to postgres with admin option;
-set role r;
-select * from storage.buckets;
-ERROR: permission denied for schema storage
-LINE 1: select * from storage.buckets;
- ^
set role postgres;
grant usage on schema storage to r;
-grant select on storage.buckets to r;
-set role r;
-select * from storage.buckets;
- id | name | owner | created_at | updated_at
-----+------+-------+------------+------------
-(0 rows)
+select pg_catalog.has_schema_privilege('r', 'storage', 'usage');
+ has_schema_privilege
+----------------------
+ t
+(1 row)
set role postgres;
drop owned by r cascade;
diff --git a/nix/tests/sql/storage.sql b/nix/tests/sql/storage.sql
index 77074184..82d3e18f 100644
--- a/nix/tests/sql/storage.sql
+++ b/nix/tests/sql/storage.sql
@@ -93,22 +93,13 @@ where
order by
p.proname;
--- storage service migrations
-select * from storage.migrations ;
-
-- postgres can grant storage privileges to custom roles
create role r;
grant r to postgres with admin option;
-set role r;
-select * from storage.buckets;
-
set role postgres;
grant usage on schema storage to r;
-grant select on storage.buckets to r;
-
-set role r;
-select * from storage.buckets;
+select pg_catalog.has_schema_privilege('r', 'storage', 'usage');
set role postgres;
drop owned by r cascade;
diff --git a/testinfra/test_ami_nix.py b/testinfra/test_ami_nix.py
index 123ef1ba..2997d2fb 100644
--- a/testinfra/test_ami_nix.py
+++ b/testinfra/test_ami_nix.py
@@ -108,7 +108,7 @@ pgsodium_root_key_content = (
)
postgrest_base_conf_content = """
db-uri = "postgres://authenticator:postgres@localhost:5432/postgres?application_name=postgrest"
-db-schema = "public, storage, graphql_public"
+db-schema = "public, graphql_public"
db-anon-role = "anon"
jwt-secret = "my_jwt_secret_which_is_not_so_secret"
role-claim-key = ".role"
@@ -447,11 +447,10 @@ def test_postgrest_responds_to_requests(host):
def test_postgrest_can_connect_to_db(host):
"""Test if PostgREST can connect to the database."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
+ f"http://{host['ip']}/rest-admin/v1/ready",
headers={
"apikey": service_role_key,
"authorization": f"Bearer {service_role_key}",
- "accept-profile": "storage",
},
)
assert res.ok
@@ -460,10 +459,7 @@ def test_postgrest_can_connect_to_db(host):
def test_postgrest_starting_apikey_query_parameter_is_removed(host):
"""Test if PostgREST removes apikey query parameter at start."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
- headers={
- "accept-profile": "storage",
- },
+ f"http://{host['ip']}/rest/v1/",
params={
"apikey": service_role_key,
"id": "eq.absent",
@@ -476,10 +472,7 @@ def test_postgrest_starting_apikey_query_parameter_is_removed(host):
def test_postgrest_middle_apikey_query_parameter_is_removed(host):
"""Test if PostgREST removes apikey query parameter in middle."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
- headers={
- "accept-profile": "storage",
- },
+ f"http://{host['ip']}/rest/v1/",
params={
"id": "eq.absent",
"apikey": service_role_key,
@@ -492,10 +485,7 @@ def test_postgrest_middle_apikey_query_parameter_is_removed(host):
def test_postgrest_ending_apikey_query_parameter_is_removed(host):
"""Test if PostgREST removes apikey query parameter at end."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
- headers={
- "accept-profile": "storage",
- },
+ f"http://{host['ip']}/rest/v1/",
params={
"id": "eq.absent",
"name": "eq.absent",
@@ -508,10 +498,7 @@ def test_postgrest_ending_apikey_query_parameter_is_removed(host):
def test_postgrest_starting_empty_key_query_parameter_is_removed(host):
"""Test if PostgREST removes empty key query parameter at start."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
- headers={
- "accept-profile": "storage",
- },
+ f"http://{host['ip']}/rest/v1/",
params={
"": "empty_key",
"id": "eq.absent",
@@ -524,10 +511,7 @@ def test_postgrest_starting_empty_key_query_parameter_is_removed(host):
def test_postgrest_middle_empty_key_query_parameter_is_removed(host):
"""Test if PostgREST removes empty key query parameter in middle."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
- headers={
- "accept-profile": "storage",
- },
+ f"http://{host['ip']}/rest/v1/",
params={
"apikey": service_role_key,
"": "empty_key",
@@ -540,10 +524,7 @@ def test_postgrest_middle_empty_key_query_parameter_is_removed(host):
def test_postgrest_ending_empty_key_query_parameter_is_removed(host):
"""Test if PostgREST removes empty key query parameter at end."""
res = requests.get(
- f"http://{host['ip']}/rest/v1/buckets",
- headers={
- "accept-profile": "storage",
- },
+ f"http://{host['ip']}/rest/v1/",
params={
"id": "eq.absent",
"apikey": service_role_key,
[parent: a119df982dd3]