29 lines
1.0 KiB
SQL
29 lines
1.0 KiB
SQL
-- 1) 如果没有就创建应用用户
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'appuser') THEN
|
||
CREATE ROLE appuser LOGIN PASSWORD 'changeme';
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
-- 2) 如果没有就创建数据库（顶层语句，不能放在 DO/事务里）
|
||
SELECT
|
||
'CREATE DATABASE appdb ENCODING ''UTF8'' TEMPLATE template0 ' ||
|
||
'LC_COLLATE ''en_US.UTF-8'' LC_CTYPE ''en_US.UTF-8'';'
|
||
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'appdb')
|
||
\gexec
|
||
|
||
-- 3) 设定库所有者/权限（无论是否新建都可安全执行）
|
||
ALTER DATABASE appdb OWNER TO appuser;
|
||
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
|
||
|
||
-- 4) 进入 appdb，配置 schema 与默认权限（可选但常用）
|
||
\connect appdb
|
||
GRANT ALL ON SCHEMA public TO appuser;
|
||
ALTER SCHEMA public OWNER TO appuser;
|
||
|
||
-- 让以后在 public 里新建的对象默认给 appuser 权限（可按需保留）
|
||
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO appuser;
|
||
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO appuser;
|