55 lines
2.3 KiB
SQL
55 lines
2.3 KiB
SQL
\set ON_ERROR_STOP on
|
|
|
|
-- This file is run by the postgres image as its bootstrap administrator. Passwords arrive only
|
|
-- through container environment variables; no credential is committed in SQL or an image layer.
|
|
\getenv owner_password MODELFORGE_MIGRATION_DB_PASSWORD
|
|
\getenv runtime_password MODELFORGE_RUNTIME_DB_PASSWORD
|
|
\getenv database_name POSTGRES_DB
|
|
|
|
select pg_catalog.format(
|
|
'create role modelforge login password %L nosuperuser nocreatedb nocreaterole inherit noreplication nobypassrls',
|
|
:'owner_password'
|
|
)
|
|
where not exists (select 1 from pg_catalog.pg_roles where rolname = 'modelforge')
|
|
\gexec
|
|
|
|
select pg_catalog.format(
|
|
'alter role modelforge with login password %L nosuperuser nocreatedb nocreaterole inherit noreplication nobypassrls',
|
|
:'owner_password'
|
|
)
|
|
\gexec
|
|
|
|
select pg_catalog.format(
|
|
'create role modelforge_runtime login password %L nosuperuser nocreatedb nocreaterole noinherit noreplication nobypassrls',
|
|
:'runtime_password'
|
|
)
|
|
where not exists (select 1 from pg_catalog.pg_roles where rolname = 'modelforge_runtime')
|
|
\gexec
|
|
|
|
select pg_catalog.format(
|
|
'alter role modelforge_runtime with login password %L nosuperuser nocreatedb nocreaterole noinherit noreplication nobypassrls',
|
|
:'runtime_password'
|
|
)
|
|
\gexec
|
|
|
|
-- NOINHERIT does not prevent SET ROLE. Remove every explicit membership so neither application
|
|
-- credential can assume a pre-existing owner or administrative role.
|
|
select pg_catalog.format('revoke %I from %I', granted.rolname, member.rolname)
|
|
from pg_catalog.pg_auth_members as membership
|
|
join pg_catalog.pg_roles as granted on granted.oid = membership.roleid
|
|
join pg_catalog.pg_roles as member on member.oid = membership.member
|
|
where member.rolname in ('modelforge', 'modelforge_runtime')
|
|
\gexec
|
|
select pg_catalog.format('revoke create, temporary on database %I from public', :'database_name')
|
|
\gexec
|
|
select pg_catalog.format('grant connect, create, temporary on database %I to modelforge', :'database_name')
|
|
\gexec
|
|
select pg_catalog.format('revoke all on database %I from modelforge_runtime', :'database_name')
|
|
\gexec
|
|
select pg_catalog.format('grant connect on database %I to modelforge_runtime', :'database_name')
|
|
\gexec
|
|
|
|
revoke create on schema public from public;
|
|
grant usage, create on schema public to modelforge;
|
|
grant usage on schema public to modelforge_runtime;
|