Files

100 lines
2.9 KiB
SQL

\set ON_ERROR_STOP on
\getenv agent_username AGENT_DB_USERNAME
\getenv agent_password AGENT_DB_PASSWORD
\getenv database_name POSTGRESQL_DATABASENAME
\getenv idle_timeout IDLE_IN_TRANSACTION_SESSION_TIMEOUT
SELECT format(
'CREATE ROLE %I LOGIN PASSWORD %L NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS',
:'agent_username',
:'agent_password')
WHERE NOT EXISTS (
SELECT 1
FROM pg_roles
WHERE rolname = :'agent_username')
\gexec
SELECT format(
'ALTER ROLE %I WITH LOGIN PASSWORD %L NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS',
:'agent_username',
:'agent_password')
\gexec
SELECT format('GRANT CONNECT ON DATABASE %I TO %I', :'database_name', :'agent_username')
\gexec
SELECT format('ALTER DATABASE %I OWNER TO %I', :'database_name', :'agent_username')
\gexec
SELECT format('ALTER SCHEMA public OWNER TO %I', :'agent_username')
\gexec
SELECT format('GRANT ALL ON SCHEMA public TO %I', :'agent_username')
\gexec
-- Existing installations may have objects created by the former postgres application login.
-- Transfer only user objects in public; extension-owned objects remain with their extension owner.
SELECT format(
'ALTER %s %I.%I OWNER TO %I',
CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'p' THEN 'TABLE'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'v' THEN 'VIEW'
WHEN 'm' THEN 'MATERIALIZED VIEW'
WHEN 'f' THEN 'FOREIGN TABLE'
END,
n.nspname,
c.relname,
:'agent_username')
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind IN ('r', 'p', 'S', 'v', 'm', 'f')
AND pg_get_userbyid(c.relowner) <> :'agent_username'
AND NOT EXISTS (
SELECT 1
FROM pg_depend d
WHERE d.classid = 'pg_class'::regclass
AND d.objid = c.oid
AND d.deptype = 'e')
\gexec
SELECT format(
'ALTER %s %I.%I(%s) OWNER TO %I',
CASE p.prokind
WHEN 'p' THEN 'PROCEDURE'
ELSE 'FUNCTION'
END,
n.nspname,
p.proname,
pg_get_function_identity_arguments(p.oid),
:'agent_username')
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
AND p.prokind IN ('f', 'p', 'w')
AND pg_get_userbyid(p.proowner) <> :'agent_username'
AND NOT EXISTS (
SELECT 1
FROM pg_depend d
WHERE d.classid = 'pg_proc'::regclass
AND d.objid = p.oid
AND d.deptype = 'e')
\gexec
SELECT format(
'ALTER ROLE %I IN DATABASE %I SET idle_in_transaction_session_timeout TO %L',
:'agent_username',
:'database_name',
:'idle_timeout')
\gexec
-- Startup migrations run as the database owner. Keep active migration and maintenance work
-- unbounded here; only idle transactions receive the safety timeout.
SELECT format('ALTER ROLE %I IN DATABASE %I RESET statement_timeout', :'agent_username', :'database_name')
\gexec
SELECT format('ALTER ROLE %I IN DATABASE %I RESET lock_timeout', :'agent_username', :'database_name')
\gexec