26 lines
1.5 KiB
SQL
26 lines
1.5 KiB
SQL
-- PROPOSTA NAO AUTORIZADA. NAO EXECUTAR.
|
|
-- Ligacao administrativa EXCLUSIVAMENTE ao cluster novo 55441.
|
|
-- Executar com ON_ERROR_STOP; sem reutilizar roles/bases existentes.
|
|
DO $$ BEGIN
|
|
IF inet_server_port() <> 55441 OR current_setting('cluster_name') <> 'sgf_ir_401c_20261102' THEN
|
|
RAISE EXCEPTION 'Cluster novo obrigatorio';
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname IN ('sgf_ir_owner_401c','sgf_ir_app_401c'))
|
|
OR EXISTS (SELECT 1 FROM pg_database WHERE datname='sgf_ir_dev_401c_20261102') THEN
|
|
RAISE EXCEPTION 'Destino ja existe; reconciliar sem sobrepor';
|
|
END IF;
|
|
END $$;
|
|
CREATE ROLE sgf_ir_owner_401c NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOREPLICATION NOBYPASSRLS;
|
|
CREATE ROLE sgf_ir_app_401c NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOREPLICATION NOBYPASSRLS;
|
|
CREATE DATABASE sgf_ir_dev_401c_20261102 OWNER sgf_ir_owner_401c;
|
|
REVOKE ALL ON DATABASE sgf_ir_dev_401c_20261102 FROM PUBLIC;
|
|
GRANT CONNECT ON DATABASE sgf_ir_dev_401c_20261102 TO sgf_ir_app_401c;
|
|
-- Na base nova, depois de restaurar sem owner/ACL com sgf_ir_owner_401c:
|
|
-- REVOKE CREATE ON SCHEMA public FROM PUBLIC;
|
|
-- REVOKE CREATE ON SCHEMA public FROM sgf_ir_app_401c;
|
|
-- GRANT USAGE ON SCHEMA public TO sgf_ir_app_401c;
|
|
-- Sem filiacoes entre roles, GRANT ALL ou DEFAULT PRIVILEGES globais.
|
|
-- Provisionamento privado posterior: duas senhas SCRAM aleatorias/distintas,
|
|
-- activar LOGIN nas duas roles apenas com HBA restrita a esta base/loopback.
|
|
-- Nenhuma senha faz parte deste ficheiro; nenhuma role existente e alterada.
|