Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8698fe8

Browse files
authored
chore: remove precreated Storage objects (#1911)
* chore: remove precreated storage objs from AMI buildLet Storage create these objects in its migrations.* test: remove storage tables in e2e testWe no longer precreate Storage objects* chore: update schema snapshots* chore: bump versions
1 parenta119df9 commit8698fe8

14 files changed

+91
-1853
lines changed

‎ansible/vars.yml‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,9 +10,9 @@ postgres_major:
1010

1111
# Full version strings for each major version
1212
postgres_release:
13-
postgresorioledb-17:"17.6.0.011-orioledb-INDATA-255"
14-
postgres17:"17.6.1.054-INDATA-255"
15-
postgres15:"15.14.1.054-INDATA-255"
13+
postgresorioledb-17:"17.6.0.012-orioledb"
14+
postgres17:"17.6.1.055"
15+
postgres15:"15.14.1.055"
1616

1717
# Non Postgres Extensions
1818
pgbouncer_release:1.19.0

‎migrations/db/init-scripts/00000000000002-storage-schema.sql‎

Lines changed: 13 additions & 112 deletions
Original file line numberDiff line numberDiff line change
@@ -2,119 +2,20 @@
22

33
CREATESCHEMAIF NOT EXISTS storage AUTHORIZATION supabase_admin;
44

5-
grant usageon schema storage to postgres, anon, authenticated, service_role;
6-
alter default privilegesin schema storagegrant allon tables to postgres, anon, authenticated, service_role;
7-
alter default privilegesin schema storagegrant allon functions to postgres, anon, authenticated, service_role;
8-
alter default privilegesin schema storagegrant allon sequences to postgres, anon, authenticated, service_role;
9-
10-
CREATETABLE "storage"."buckets" (
11-
"id"textnot NULL,
12-
"name"textNOT NULL,
13-
"owner" uuid,
14-
"created_at"timestamptz DEFAULT now(),
15-
"updated_at"timestamptz DEFAULT now(),
16-
CONSTRAINT"buckets_owner_fkey"FOREIGN KEY ("owner")REFERENCES"auth"."users"("id"),
17-
PRIMARY KEY ("id")
18-
);
19-
CREATEUNIQUE INDEX "bname"ON"storage"."buckets" USING BTREE ("name");
20-
21-
CREATETABLE "storage"."objects" (
22-
"id" uuidNOT NULL DEFAULTextensions.uuid_generate_v4(),
23-
"bucket_id"text,
24-
"name"text,
25-
"owner" uuid,
26-
"created_at"timestamptz DEFAULT now(),
27-
"updated_at"timestamptz DEFAULT now(),
28-
"last_accessed_at"timestamptz DEFAULT now(),
29-
"metadata" jsonb,
30-
CONSTRAINT"objects_bucketId_fkey"FOREIGN KEY ("bucket_id")REFERENCES"storage"."buckets"("id"),
31-
CONSTRAINT"objects_owner_fkey"FOREIGN KEY ("owner")REFERENCES"auth"."users"("id"),
32-
PRIMARY KEY ("id")
33-
);
34-
CREATEUNIQUE INDEX "bucketid_objname"ON"storage"."objects" USING BTREE ("bucket_id","name");
35-
CREATEINDEXname_prefix_searchONstorage.objects(name text_pattern_ops);
36-
37-
ALTERTABLEstorage.objects ENABLE ROW LEVEL SECURITY;
38-
39-
CREATEFUNCTIONstorage.foldername(nametext)
40-
RETURNStext[]
41-
LANGUAGE plpgsql
42-
AS $function$
43-
DECLARE
44-
_partstext[];
45-
BEGIN
46-
select string_to_array(name,'/') into _parts;
47-
return _parts[1:array_length(_parts,1)-1];
48-
END
49-
$function$;
50-
51-
CREATEFUNCTIONstorage.filename(nametext)
52-
RETURNStext
53-
LANGUAGE plpgsql
54-
AS $function$
55-
DECLARE
56-
_partstext[];
57-
BEGIN
58-
select string_to_array(name,'/') into _parts;
59-
return _parts[array_length(_parts,1)];
60-
END
61-
$function$;
62-
63-
CREATEFUNCTIONstorage.extension(nametext)
64-
RETURNStext
65-
LANGUAGE plpgsql
66-
AS $function$
67-
DECLARE
68-
_partstext[];
69-
_filenametext;
70-
BEGIN
71-
select string_to_array(name,'/') into _parts;
72-
select _parts[array_length(_parts,1)] into _filename;
73-
-- @todo return the last part instead of 2
74-
return split_part(_filename,'.',2);
75-
END
76-
$function$;
77-
78-
CREATEFUNCTIONstorage.search(prefixtext, bucketnametext, limitsint DEFAULT100, levelsint DEFAULT1, offsetsint DEFAULT0)
79-
RETURNS TABLE (
80-
nametext,
81-
id uuid,
82-
updated_atTIMESTAMPTZ,
83-
created_atTIMESTAMPTZ,
84-
last_accessed_atTIMESTAMPTZ,
85-
metadata jsonb
86-
)
87-
LANGUAGE plpgsql
88-
AS $function$
89-
DECLARE
90-
_bucketIdtext;
91-
BEGIN
92-
-- will be replaced by migrations when server starts
93-
-- saving space for cloud-init
94-
END
95-
$function$;
96-
97-
-- create migrations table
98-
-- https://github.com/ThomWright/postgres-migrations/blob/master/src/migrations/0_create-migrations-table.sql
99-
-- we add this table here and not let it be auto-created so that the permissions are properly applied to it
100-
CREATETABLEIF NOT EXISTSstorage.migrations (
101-
idintegerPRIMARY KEY,
102-
namevarchar(100) UNIQUENOT NULL,
103-
hashvarchar(40)NOT NULL,-- sha1 hex encoded hash of the file name and contents, to ensure it hasn't been altered since applying the migration
104-
executed_attimestamp DEFAULTcurrent_timestamp
105-
);
106-
1075
CREATEUSERsupabase_storage_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
108-
GRANT ALL PRIVILEGESON SCHEMA storage TO supabase_storage_admin;
109-
GRANT ALL PRIVILEGESON ALL TABLESIN SCHEMA storage TO supabase_storage_admin;
110-
GRANT ALL PRIVILEGESON ALL SEQUENCESIN SCHEMA storage TO supabase_storage_admin;
1116
ALTERUSER supabase_storage_adminSET search_path="storage";
112-
ALTERtable"storage".objects owner to supabase_storage_admin;
113-
ALTERtable"storage".buckets owner to supabase_storage_admin;
114-
ALTERtable"storage".migrations OWNER TO supabase_storage_admin;
115-
ALTERfunction"storage".foldername(text) owner to supabase_storage_admin;
116-
ALTERfunction"storage".filename(text) owner to supabase_storage_admin;
117-
ALTERfunction"storage".extension(text) owner to supabase_storage_admin;
118-
ALTERfunction"storage".search(text,text,int,int,int) owner to supabase_storage_admin;
7+
GRANT CREATEON DATABASE postgres TO supabase_storage_admin;
8+
9+
do $$
10+
begin
11+
if exists (selectfrom pg_namespacewhere nspname='storage') then
12+
grant usageon schema storage to postgres, anon, authenticated, service_role;
13+
alter default privilegesin schema storagegrant allon tables to postgres, anon, authenticated, service_role;
14+
alter default privilegesin schema storagegrant allon functions to postgres, anon, authenticated, service_role;
15+
alter default privilegesin schema storagegrant allon sequences to postgres, anon, authenticated, service_role;
16+
17+
grant allon schema storage to supabase_storage_admin withgrant option;
18+
end if;
19+
end $$;
11920

12021
-- migrate:down

‎migrations/db/init-scripts/00000000000003-post-setup.sql‎

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -105,15 +105,20 @@ CREATE ROLE dashboard_user NOSUPERUSER CREATEDB CREATEROLE REPLICATION;
105105
GRANT ALLON DATABASE postgres TO dashboard_user;
106106
GRANT ALLON SCHEMA auth TO dashboard_user;
107107
GRANT ALLON SCHEMA extensions TO dashboard_user;
108-
GRANT ALLON SCHEMA storage TO dashboard_user;
109108
GRANT ALLON ALL TABLESIN SCHEMA auth TO dashboard_user;
110109
GRANT ALLON ALL TABLESIN SCHEMA extensions TO dashboard_user;
111110
-- GRANT ALL ON ALL TABLES IN SCHEMA storage TO dashboard_user;
112111
GRANT ALLON ALL SEQUENCESIN SCHEMA auth TO dashboard_user;
113-
GRANT ALLON ALL SEQUENCESIN SCHEMA storage TO dashboard_user;
114112
GRANT ALLON ALL SEQUENCESIN SCHEMA extensions TO dashboard_user;
115113
GRANT ALLON ALL ROUTINESIN SCHEMA auth TO dashboard_user;
116-
GRANT ALLON ALL ROUTINESIN SCHEMA storage TO dashboard_user;
117114
GRANT ALLON ALL ROUTINESIN SCHEMA extensions TO dashboard_user;
115+
do $$
116+
begin
117+
if exists (selectfrom pg_namespacewhere nspname='storage') then
118+
GRANT ALLON SCHEMA storage TO dashboard_user;
119+
GRANT ALLON ALL SEQUENCESIN SCHEMA storage TO dashboard_user;
120+
GRANT ALLON ALL ROUTINESIN SCHEMA storage TO dashboard_user;
121+
end if;
122+
end $$;
118123

119124
-- migrate:down

‎migrations/db/migrations/10000000000000_demote-postgres.sql‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,16 +4,21 @@
44
GRANT ALLON DATABASE postgres TO postgres;
55
GRANT ALLON SCHEMA auth TO postgres;
66
GRANT ALLON SCHEMA extensions TO postgres;
7-
GRANT ALLON SCHEMA storage TO postgres;
87
GRANT ALLON ALL TABLESIN SCHEMA auth TO postgres;
9-
GRANT ALLON ALL TABLESIN SCHEMA storage TO postgres;
108
GRANT ALLON ALL TABLESIN SCHEMA extensions TO postgres;
119
GRANT ALLON ALL SEQUENCESIN SCHEMA auth TO postgres;
12-
GRANT ALLON ALL SEQUENCESIN SCHEMA storage TO postgres;
1310
GRANT ALLON ALL SEQUENCESIN SCHEMA extensions TO postgres;
1411
GRANT ALLON ALL ROUTINESIN SCHEMA auth TO postgres;
15-
GRANT ALLON ALL ROUTINESIN SCHEMA storage TO postgres;
1612
GRANT ALLON ALL ROUTINESIN SCHEMA extensions TO postgres;
13+
do $$
14+
begin
15+
if exists (selectfrom pg_namespacewhere nspname='storage') then
16+
GRANT ALLON SCHEMA storage TO postgres;
17+
GRANT ALLON ALL TABLESIN SCHEMA storage TO postgres;
18+
GRANT ALLON ALL SEQUENCESIN SCHEMA storage TO postgres;
19+
GRANT ALLON ALL ROUTINESIN SCHEMA storage TO postgres;
20+
end if;
21+
end $$;
1722
ALTER ROLE postgres NOSUPERUSER CREATEDB CREATEROLE LOGIN REPLICATION BYPASSRLS;
1823

1924
-- migrate:down
Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,25 @@
11
-- migrate:up
22
revoke supabase_storage_adminfrom postgres;
3-
revoke createon schema storagefrom postgres;
4-
revoke allonstorage.migrationsfrom anon, authenticated, service_role, postgres;
3+
do $$
4+
begin
5+
if exists (selectfrom pg_namespacewhere nspname='storage') then
6+
revoke createon schema storagefrom postgres;
7+
end if;
8+
end $$;
9+
do $$
10+
begin
11+
if exists (selectfrom pg_classwhere relnamespace= (selectoidfrom pg_namespacewhere nspname='storage')and relname='migrations') then
12+
revoke allonstorage.migrationsfrom anon, authenticated, service_role, postgres;
13+
end if;
14+
end $$;
515

616
revoke supabase_auth_adminfrom postgres;
717
revoke createon schema authfrom postgres;
8-
revoke allonauth.schema_migrationsfrom dashboard_user, postgres;
18+
do $$
19+
begin
20+
if exists (selectfrom pg_classwhere relnamespace='auth'::regnamespaceand relname='schema_migrations') then
21+
revoke allonauth.schema_migrationsfrom dashboard_user, postgres;
22+
end if;
23+
end $$;
924

1025
-- migrate:down
Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,14 @@
11
-- migrate:up
22
-- TODO: remove this migration once STORAGE-211 is completed
33
-- DRI: bobbie
4-
grant allonstorage.buckets,storage.objects to postgres withgrant option;
4+
do $$
5+
begin
6+
if exists (selectfrom pg_classwhere relnamespace= (selectoidfrom pg_namespacewhere nspname='storage')and relname='buckets') then
7+
grant allonstorage.buckets to postgres withgrant option;
8+
end if;
9+
if exists (selectfrom pg_classwhere relnamespace= (selectoidfrom pg_namespacewhere nspname='storage')and relname='objects') then
10+
grant allonstorage.objects to postgres withgrant option;
11+
end if;
12+
end $$;
513

614
-- migrate:down
Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,9 @@
11
-- migrate:up
2-
grant usageon schema storage to postgres withgrant option;
2+
do $$
3+
begin
4+
if exists (selectfrom pg_namespacewhere nspname='storage') then
5+
grant usageon schema storage to postgres withgrant option;
6+
end if;
7+
end $$;
38

49
-- migrate:down

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp