106 lines
5.0 KiB
PL/PgSQL
106 lines
5.0 KiB
PL/PgSQL
-- Inicialização explícita da identidade de adaptador-planilha-hml.
|
|
-- Não faz parte de migrations/. Migrate, seed e deploy não executam este arquivo.
|
|
-- Substitua <<PROJECT_REF>> pelo Reference ID do Dashboard: 20 letras ou dígitos minúsculos.
|
|
-- Rode o bloco inteiro de uma vez. Qualquer falha desfaz o schema inteiro.
|
|
-- Não altera app.settings.jwt_exp nem os schemas auth e storage.
|
|
-- Se o schema já existir, não use este arquivo. O comando identity:init recusa linha divergente e objeto desconhecido.
|
|
|
|
do $identity$
|
|
begin
|
|
create schema adaptador_guard;
|
|
revoke all on schema adaptador_guard from public;
|
|
|
|
create table adaptador_guard.project_identity (
|
|
singleton boolean primary key default true check (singleton),
|
|
project_name text not null,
|
|
project_ref text not null,
|
|
constraint project_identity_name check (project_name = 'adaptador-planilha-hml'),
|
|
constraint project_identity_ref check (project_ref ~ '^[a-z0-9]{20}$')
|
|
);
|
|
revoke all on table adaptador_guard.project_identity from public;
|
|
alter table adaptador_guard.project_identity enable row level security;
|
|
alter table adaptador_guard.project_identity force row level security;
|
|
comment on table adaptador_guard.project_identity is
|
|
'Identidade persistente do projeto de homologacao. Nao e prova criptografica.';
|
|
|
|
create function adaptador_guard.reject_identity_change()
|
|
returns trigger
|
|
language plpgsql
|
|
security invoker
|
|
set search_path = pg_catalog
|
|
as $body$
|
|
begin
|
|
if tg_op <> 'INSERT' then
|
|
raise exception 'identidade imutavel' using errcode = '42501';
|
|
end if;
|
|
if session_user <> 'postgres' and session_user !~ '^postgres[.][a-z0-9]{20}$' then
|
|
raise exception 'identidade protegida' using errcode = '42501';
|
|
end if;
|
|
return new;
|
|
end;
|
|
$body$;
|
|
|
|
create trigger project_identity_guard
|
|
before insert or update or delete on adaptador_guard.project_identity
|
|
for each row execute function adaptador_guard.reject_identity_change();
|
|
|
|
create function adaptador_guard.adapter_project_identity()
|
|
returns table (project_name text, project_ref text)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = pg_catalog
|
|
as $body$
|
|
select i.project_name, i.project_ref
|
|
from adaptador_guard.project_identity i
|
|
$body$;
|
|
|
|
revoke all on function adaptador_guard.adapter_project_identity() from public;
|
|
revoke all on function adaptador_guard.reject_identity_change() from public;
|
|
|
|
insert into adaptador_guard.project_identity (project_name, project_ref)
|
|
values ('adaptador-planilha-hml', '<<PROJECT_REF>>');
|
|
|
|
revoke all on schema adaptador_guard from public;
|
|
revoke all on table adaptador_guard.project_identity from public;
|
|
revoke all on all functions in schema adaptador_guard from public;
|
|
alter default privileges in schema adaptador_guard revoke all on tables from public;
|
|
alter default privileges in schema adaptador_guard revoke execute on functions from public;
|
|
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = 'adaptador_web') then
|
|
revoke all on schema adaptador_guard from adaptador_web;
|
|
revoke all on table adaptador_guard.project_identity from adaptador_web;
|
|
revoke all on all functions in schema adaptador_guard from adaptador_web;
|
|
grant usage on schema adaptador_guard to adaptador_web;
|
|
grant execute on function adaptador_guard.adapter_project_identity() to adaptador_web;
|
|
end if;
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = 'adaptador_worker') then
|
|
revoke all on schema adaptador_guard from adaptador_worker;
|
|
revoke all on table adaptador_guard.project_identity from adaptador_worker;
|
|
revoke all on all functions in schema adaptador_guard from adaptador_worker;
|
|
grant usage on schema adaptador_guard to adaptador_worker;
|
|
grant execute on function adaptador_guard.adapter_project_identity() to adaptador_worker;
|
|
end if;
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = 'anon') then
|
|
revoke all on schema adaptador_guard from anon;
|
|
revoke all on table adaptador_guard.project_identity from anon;
|
|
revoke all on all functions in schema adaptador_guard from anon;
|
|
end if;
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = 'authenticated') then
|
|
revoke all on schema adaptador_guard from authenticated;
|
|
revoke all on table adaptador_guard.project_identity from authenticated;
|
|
revoke all on all functions in schema adaptador_guard from authenticated;
|
|
end if;
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = 'service_role') then
|
|
revoke all on schema adaptador_guard from service_role;
|
|
revoke all on table adaptador_guard.project_identity from service_role;
|
|
revoke all on all functions in schema adaptador_guard from service_role;
|
|
end if;
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = 'app_user') then
|
|
revoke all on schema adaptador_guard from app_user;
|
|
revoke all on table adaptador_guard.project_identity from app_user;
|
|
revoke all on all functions in schema adaptador_guard from app_user;
|
|
end if;
|
|
end
|
|
$identity$;
|