8000 调整初始化脚本 · foxgis/supabase-postgres@9bc640f · GitHub
[go: up one dir, main page]
Skip to content

Commit 9bc640f

Browse files
jingsamjingsam
committed
调整初始化脚本
1 parent de70e88 commit 9bc640f

File tree

5 files changed

+39
-21
lines changed

5 files changed

+39
-21
lines changed

‎migrations/CHANGELOG.md

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -16,18 +16,18 @@
1616
- 增加`GRANT USAGE ON SCHEMA extensions TO supabase_storage_admin;`。（为了调用gen_rand_uuid函数）
1717
- `search_path`增加`extensions`。（为了调用gen_rand_uuid函数）
1818
4. 修改`00000000000003-post-setup.sql`
19-
- 注释`GRANT ALL ON DATABASE postgres TO dashboard_user;`。（瀚高数据库限制）
19+
- 增加`db`参数。（用来设置数据库名，而不是默认postgres数据库）
2020
5. 修改`99-jwt.sql`
2121
- 增加`db`参数。（用来设置数据库名，而不是默认postgres数据库）
2222

2323
## migrations
2424

2525
1. 排除`00-extensions.sql`。（pg_stat_statements默认已安装）
26-
2. 注释`20230529180330_alter_api_roles_for_inherit.sql`。（内容已经在00000000000000-initial-schema.sql实现）
27-
4. 修改`10000000000000_demote-postgres.sql`。
28-
- 注释`GRANT ALL ON DATABASE postgres TO postgres;`。（瀚高数据库限制）
26+
2. 修改`10000000000000_demote-postgres.sql`。
27+
- 注释`GRANT ALL ON DATABASE postgres TO postgres;`。（非必须）
2928
- 注释`ALTER ROLE postgres NOSUPERUSER CREATEDB CREATEROLE LOGIN REPLICATION BYPASSRLS;`（非必要）
30-
5. 修改`20221028101028_set_authenticator_timeout.sql`
29+
3. 修改`20221028101028_set_authenticator_timeout.sql`
3130
- 将`authenticator`角色的`statement_timeout`设置为60s。
32-
6. 修改`20240606060239_grant_predefined_roles_to_postgres.sql`
31+
4. 注释`20230529180330_alter_api_roles_for_inherit.sql`。（内容已经在00000000000000-initial-schema.sql实现）
32+
5. 修改`20240606060239_grant_predefined_roles_to_postgres.sql`
3333
- 去除`pg_read_all_data`角色。（PG12无此角色）

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
-- migrate:up
2+
\set db `echo "$POSTGRES_DB"`
23

34
ALTER ROLE supabase_admin SET search_path TO "\$user",public,auth,extensions;
45
ALTER ROLE postgres SET search_path TO "\$user",public,extensions;
@@ -101,8 +102,8 @@ END
101102
$$;
102103

103104
-- Supabase dashboard user
104-
-- CREATE ROLE dashboard_user NOSUPERUSER CREATEDB CREATEROLE REPLICATION;
105-
-- GRANT ALL ON DATABASE postgres TO dashboard_user;
105+
CREATE ROLE dashboard_user NOSUPERUSER CREATEDB CREATEROLE REPLICATION;
106+
GRANT ALL ON DATABASE :db TO dashboard_user;
106107
GRANT ALL ON SCHEMA auth TO dashboard_user;
107108
GRANT ALL ON SCHEMA extensions TO dashboard_user;
108109
GRANT ALL ON SCHEMA storage TO dashboard_user;

‎migrations/db/migrate.sh

Lines changed: 3 additions & 3 deletions
< 8000 /tr>
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ begin
3434
-- postgres role is pre-created during AMI build
3535
if not exists (select from pg_roles where rolname = 'postgres') then
3636
create role postgres superuser login password '$PGPASSWORD';
37-
alter database postgres owner to postgres;
37+
alter database $PGDATABASE owner to postgres;
3838
end if;
3939
end \$\$
4040
EOSQL
@@ -52,11 +52,11 @@ EOSQL
5252
else
5353
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U supabase_admin <<EOSQL
5454
create role postgres superuser login password '$PGPASSWORD';
55-
alter database postgres owner to postgres;
55+
alter database $PGDATABASE owner to postgres;
5656
EOSQL
5757
# run init scripts as postgres user
5858
DBMATE_MIGRATIONS_DIR="$db/init-scripts" DATABASE_URL="postgres://postgres:$connect" dbmate --no-dump-schema migrate
59-
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U postgres -c "ALTER USER supabase_admin WITH PASSWORD '$PGPASSWORD'"
59+
# psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U postgres -c "ALTER USER supabase_admin WITH PASSWORD '$PGPASSWORD'"
6060
# run migrations as super user - postgres user demoted in post-setup
6161
DBMATE_MIGRATIONS_DIR="$db/migrations" DATABASE_URL="postgres://supabase_admin:$connect" dbmate --no-dump-schema migrate
6262
fi

‎migrations/docker.sh

Lines changed: 6 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -30,9 +30,12 @@ until docker exec hgdb pg_isready -U sysdba -d highgo -q; do
3030
sleep 1
3131
done
3232

33+
# 复制许可文件
34+
cp hgdb.lic data/
35+
3336
# 更改select version()返回值，以兼容GDAL
3437
docker exec -i hgdb gosu highgo bash <<- "EOF"
35-
hg_version_gen "PostgreSQL 12.7 (HGDB-SEE V4.5)" "瀚高安全版 V4.5" "12.7"
38+
hg_version_gen "PostgreSQL 12.7 (HGDB-SEE V4.5)" "PostgreSQL 12.7 (HGDB-SEE V4.5)" "12.7"
3639
EOF
3740

3841
# 更改数据库配置
@@ -41,9 +44,6 @@ docker exec -i -e PGPASSWORD=$POSTGRES_PASSWORD hgdb gosu highgo psql -U sysdba
4144
alter system set wal_level = 'logical';
4245
EOF
4346

44-
# 复制许可文件
45-
cp hgdb.lic data/
46-
4747
# 重启数据库使配置生效
4848
docker restart hgdb
4949

@@ -58,18 +58,14 @@ docker exec -i -e PGPASSWORD=$POSTGRES_PASSWORD hgdb gosu highgo psql -U syssso
5858
select set_secure_param('hg_sepv4','dyn_off');
5959
EOF
6060

61-
# 创建supabase所需的数据库和用户
61+
# 创建数据库和用户
6262
docker exec -i -e PGPASSWORD=$POSTGRES_PASSWORD hgdb gosu highgo psql -U sysdba -d highgo <<- EOF
6363
create role postgres superuser login password '$POSTGRES_PASSWORD';
64-
create role supabase_admin superuser login password '$POSTGRES_PASSWORD';
65-
create role dashboard_user NOSUPERUSER CREATEDB CREATEROLE REPLICATION;
66-
create user supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION PASSWORD '$POSTGRES_PASSWORD';
6764
create database $POSTGRES_DB with owner postgres;
68-
grant all on database $POSTGRES_DB to dashboard_user;
6965
EOF
7066

7167
# 初始化数据库
72-
./db/migrate.sh
68+
./initdb.sh
7369

7470
# 恢复三权分立
7571
docker exec -i -e PGPASSWORD=$POSTGRES_PASSWORD hgdb gosu highgo psql -U syssso -d highgo <<- "EOF"

‎migrations/initdb.sh

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
#!/bin/bash
2+
3+
set -euo pipefail
4+
5+
# 加载.env中的环境变量
6+
set -a; source .env; set +a
7+
8+
# 设置数据库环境变量
9+
export PGDATABASE="${POSTGRES_DB:-postgres}"
10+
export PGHOST="${POSTGRES_HOST:-localhost}"
11+
export PGPORT="${POSTGRES_PORT:-5866}"
12+
export PGPASSWORD="${POSTGRES_PASSWORD:-}"
13+
14+
# 创建数据库用户
15+
psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -U postgres <<-EOSQL
16+
create role supabase_admin superuser login password '$POSTGRES_PASSWORD';
17+
create user supabase_functions_admin noinherit createrole login noreplication password '$POSTGRES_PASSWORD';
18+
EOSQL
19+
20+
# 初始化数据库
21+
./db/migrate.sh

0 commit comments
Comments
 (0)
0