330 lines
12 KiB
PL/PgSQL
330 lines
12 KiB
PL/PgSQL
create or replace function public.auth_uid()
|
|
returns uuid
|
|
language sql
|
|
stable
|
|
as $$
|
|
select nullif(current_setting('app.user_id', true), '')::uuid
|
|
$$;
|
|
|
|
create or replace function public.is_member(ws uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select exists (
|
|
select 1 from public.workspace_members m
|
|
where m.workspace_id = ws and m.user_id = public.auth_uid()
|
|
)
|
|
$$;
|
|
|
|
create or replace function public.member_role(ws uuid)
|
|
returns text
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select m.role from public.workspace_members m
|
|
where m.workspace_id = ws and m.user_id = public.auth_uid()
|
|
$$;
|
|
|
|
create or replace function public.module_enabled(ws uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select coalesce((
|
|
select f.enabled from public.workspace_module_flags f
|
|
where f.workspace_id = ws and f.module_key = 'spreadsheet_adapter'
|
|
), false)
|
|
$$;
|
|
|
|
create or replace function public.can_manage_adapter(ws uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select public.module_enabled(ws) and public.member_role(ws) in ('owner', 'admin')
|
|
$$;
|
|
|
|
create or replace function public.can_upload_model(ws uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select public.module_enabled(ws) and (
|
|
public.member_role(ws) in ('owner', 'admin')
|
|
or (
|
|
public.member_role(ws) in ('gestor', 'agent')
|
|
and coalesce((
|
|
select s.allow_non_admin_model_upload
|
|
from public.spreadsheet_adapter_settings s
|
|
where s.workspace_id = ws
|
|
), false)
|
|
)
|
|
)
|
|
$$;
|
|
|
|
create or replace function public.shares_workspace(other_user uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select exists (
|
|
select 1
|
|
from public.workspace_members a
|
|
join public.workspace_members b on a.workspace_id = b.workspace_id
|
|
where a.user_id = public.auth_uid() and b.user_id = other_user
|
|
)
|
|
$$;
|
|
|
|
create or replace function public.model_has_active_job(model uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = pg_catalog
|
|
as $$
|
|
select exists (
|
|
select 1
|
|
from public.spreadsheet_jobs j
|
|
join public.spreadsheet_models m on m.id = j.model_id
|
|
where j.model_id = model
|
|
and j.status in ('validating', 'ready', 'queued', 'processing')
|
|
and public.is_member(m.workspace_id)
|
|
)
|
|
$$;
|
|
|
|
revoke all on function public.auth_uid() from public;
|
|
revoke all on function public.is_member(uuid) from public;
|
|
revoke all on function public.member_role(uuid) from public;
|
|
revoke all on function public.module_enabled(uuid) from public;
|
|
revoke all on function public.can_manage_adapter(uuid) from public;
|
|
revoke all on function public.can_upload_model(uuid) from public;
|
|
revoke all on function public.shares_workspace(uuid) from public;
|
|
revoke all on function public.model_has_active_job(uuid) from public;
|
|
|
|
grant execute on function public.auth_uid() to app_user;
|
|
grant execute on function public.is_member(uuid) to app_user;
|
|
grant execute on function public.member_role(uuid) to app_user;
|
|
grant execute on function public.module_enabled(uuid) to app_user;
|
|
grant execute on function public.can_manage_adapter(uuid) to app_user;
|
|
grant execute on function public.can_upload_model(uuid) to app_user;
|
|
grant execute on function public.shares_workspace(uuid) to app_user;
|
|
grant execute on function public.model_has_active_job(uuid) to app_user;
|
|
|
|
alter table public.profiles enable row level security;
|
|
alter table public.workspaces enable row level security;
|
|
alter table public.workspace_members enable row level security;
|
|
alter table public.workspace_module_flags enable row level security;
|
|
alter table public.audit_log enable row level security;
|
|
alter table public.spreadsheet_adapter_settings enable row level security;
|
|
alter table public.spreadsheet_models enable row level security;
|
|
alter table public.spreadsheet_jobs enable row level security;
|
|
alter table public.spreadsheet_job_sources enable row level security;
|
|
alter table public.spreadsheet_job_mappings enable row level security;
|
|
|
|
alter table public.profiles force row level security;
|
|
alter table public.workspaces force row level security;
|
|
alter table public.workspace_members force row level security;
|
|
alter table public.workspace_module_flags force row level security;
|
|
alter table public.audit_log force row level security;
|
|
alter table public.spreadsheet_adapter_settings force row level security;
|
|
alter table public.spreadsheet_models force row level security;
|
|
alter table public.spreadsheet_jobs force row level security;
|
|
alter table public.spreadsheet_job_sources force row level security;
|
|
alter table public.spreadsheet_job_mappings force row level security;
|
|
|
|
drop policy if exists profiles_select on public.profiles;
|
|
create policy profiles_select on public.profiles
|
|
for select to app_user
|
|
using (id = public.auth_uid() or public.shares_workspace(id));
|
|
|
|
drop policy if exists workspaces_select on public.workspaces;
|
|
create policy workspaces_select on public.workspaces
|
|
for select to app_user
|
|
using (public.is_member(id));
|
|
|
|
drop policy if exists members_select on public.workspace_members;
|
|
create policy members_select on public.workspace_members
|
|
for select to app_user
|
|
using (user_id = public.auth_uid() or public.is_member(workspace_id));
|
|
|
|
drop policy if exists flags_select on public.workspace_module_flags;
|
|
create policy flags_select on public.workspace_module_flags
|
|
for select to app_user
|
|
using (public.is_member(workspace_id));
|
|
|
|
drop policy if exists audit_insert on public.audit_log;
|
|
create policy audit_insert on public.audit_log
|
|
for insert to app_user
|
|
with check (actor_id = public.auth_uid() and (workspace_id is null or public.is_member(workspace_id)));
|
|
|
|
drop policy if exists audit_select on public.audit_log;
|
|
create policy audit_select on public.audit_log
|
|
for select to app_user
|
|
using (public.can_manage_adapter(workspace_id));
|
|
|
|
drop policy if exists settings_select on public.spreadsheet_adapter_settings;
|
|
create policy settings_select on public.spreadsheet_adapter_settings
|
|
for select to app_user
|
|
using (public.is_member(workspace_id) and public.module_enabled(workspace_id));
|
|
|
|
drop policy if exists settings_insert on public.spreadsheet_adapter_settings;
|
|
create policy settings_insert on public.spreadsheet_adapter_settings
|
|
for insert to app_user
|
|
with check (public.can_manage_adapter(workspace_id));
|
|
|
|
drop policy if exists settings_update on public.spreadsheet_adapter_settings;
|
|
create policy settings_update on public.spreadsheet_adapter_settings
|
|
for update to app_user
|
|
using (public.can_manage_adapter(workspace_id))
|
|
with check (public.can_manage_adapter(workspace_id));
|
|
|
|
drop policy if exists models_select on public.spreadsheet_models;
|
|
create policy models_select on public.spreadsheet_models
|
|
for select to app_user
|
|
using (public.is_member(workspace_id) and public.module_enabled(workspace_id));
|
|
|
|
drop policy if exists models_insert on public.spreadsheet_models;
|
|
create policy models_insert on public.spreadsheet_models
|
|
for insert to app_user
|
|
with check (
|
|
public.can_upload_model(workspace_id)
|
|
and created_by = public.auth_uid()
|
|
);
|
|
|
|
drop policy if exists models_update on public.spreadsheet_models;
|
|
create policy models_update on public.spreadsheet_models
|
|
for update to app_user
|
|
using (public.can_manage_adapter(workspace_id))
|
|
with check (public.can_manage_adapter(workspace_id));
|
|
|
|
drop policy if exists jobs_all on public.spreadsheet_jobs;
|
|
drop policy if exists jobs_select on public.spreadsheet_jobs;
|
|
create policy jobs_select on public.spreadsheet_jobs
|
|
for select to app_user
|
|
using (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id));
|
|
|
|
drop policy if exists jobs_insert on public.spreadsheet_jobs;
|
|
create policy jobs_insert on public.spreadsheet_jobs
|
|
for insert to app_user
|
|
with check (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id));
|
|
|
|
drop policy if exists jobs_update on public.spreadsheet_jobs;
|
|
create policy jobs_update on public.spreadsheet_jobs
|
|
for update to app_user
|
|
using (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id))
|
|
with check (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id));
|
|
|
|
drop policy if exists sources_select on public.spreadsheet_job_sources;
|
|
create policy sources_select on public.spreadsheet_job_sources
|
|
for select to app_user
|
|
using (exists (
|
|
select 1 from public.spreadsheet_jobs j
|
|
where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
|
|
));
|
|
|
|
drop policy if exists sources_insert on public.spreadsheet_job_sources;
|
|
create policy sources_insert on public.spreadsheet_job_sources
|
|
for insert to app_user
|
|
with check (exists (
|
|
select 1 from public.spreadsheet_jobs j
|
|
where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
|
|
));
|
|
|
|
drop policy if exists sources_update on public.spreadsheet_job_sources;
|
|
create policy sources_update on public.spreadsheet_job_sources
|
|
for update to app_user
|
|
using (exists (
|
|
select 1 from public.spreadsheet_jobs j
|
|
where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
|
|
))
|
|
with check (exists (
|
|
select 1 from public.spreadsheet_jobs j
|
|
where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
|
|
));
|
|
|
|
drop policy if exists sources_delete on public.spreadsheet_job_sources;
|
|
create policy sources_delete on public.spreadsheet_job_sources
|
|
for delete to app_user
|
|
using (exists (
|
|
select 1 from public.spreadsheet_jobs j
|
|
where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
|
|
));
|
|
|
|
drop policy if exists mappings_select on public.spreadsheet_job_mappings;
|
|
create policy mappings_select on public.spreadsheet_job_mappings
|
|
for select to app_user
|
|
using (exists (
|
|
select 1 from public.spreadsheet_job_sources s
|
|
join public.spreadsheet_jobs j on j.id = s.job_id
|
|
where s.id = job_source_id and j.user_id = public.auth_uid()
|
|
));
|
|
|
|
drop policy if exists mappings_insert on public.spreadsheet_job_mappings;
|
|
create policy mappings_insert on public.spreadsheet_job_mappings
|
|
for insert to app_user
|
|
with check (exists (
|
|
select 1 from public.spreadsheet_job_sources s
|
|
join public.spreadsheet_jobs j on j.id = s.job_id
|
|
where s.id = job_source_id and j.user_id = public.auth_uid()
|
|
));
|
|
|
|
drop policy if exists mappings_delete on public.spreadsheet_job_mappings;
|
|
create policy mappings_delete on public.spreadsheet_job_mappings
|
|
for delete to app_user
|
|
using (exists (
|
|
select 1 from public.spreadsheet_job_sources s
|
|
join public.spreadsheet_jobs j on j.id = s.job_id
|
|
where s.id = job_source_id and j.user_id = public.auth_uid()
|
|
));
|
|
|
|
grant usage on schema public to app_user;
|
|
grant select, insert, update, delete on all tables in schema public to app_user;
|
|
grant usage, select on all sequences in schema public to app_user;
|
|
|
|
-- schema_migrations não tem RLS. A escrita fica só com o dono que aplica as migrations.
|
|
revoke insert, update, delete, truncate, references, trigger on table public.schema_migrations from public, app_user;
|
|
|
|
do $priv$
|
|
declare
|
|
versao int := current_setting('server_version_num')::int;
|
|
escrita text := 'insert, update, delete, truncate, references, trigger';
|
|
papel text;
|
|
begin
|
|
if versao >= 170000 then
|
|
escrita := escrita || ', maintain';
|
|
execute 'revoke maintain on table public.schema_migrations from public, app_user';
|
|
end if;
|
|
foreach papel in array array['anon', 'authenticated', 'service_role', 'adaptador_web', 'adaptador_worker']
|
|
loop
|
|
if exists (select 1 from pg_catalog.pg_roles where rolname = papel) then
|
|
execute format(
|
|
'revoke %s on table public.profiles, public.workspaces, public.workspace_members, public.workspace_module_flags, public.audit_log, public.spreadsheet_adapter_settings, public.spreadsheet_models, public.spreadsheet_jobs, public.spreadsheet_job_sources, public.spreadsheet_job_mappings, public.schema_migrations from %I',
|
|
escrita,
|
|
papel
|
|
);
|
|
if papel in ('anon', 'authenticated', 'service_role') then
|
|
execute format('revoke select on table public.schema_migrations from %I', papel);
|
|
execute format(
|
|
'alter default privileges in schema public revoke %s on tables from %I',
|
|
escrita,
|
|
papel
|
|
);
|
|
end if;
|
|
end if;
|
|
end loop;
|
|
end
|
|
$priv$;
|