Een regelmatig voorkomend probleem is het toekennen van rechten op PostgreSQL-tabellen, waarbij voor nieuwe tabellen wel automatisch de default rechten van het schema uitgedeeld worden, maar bestaande tabellen die niet krijgen.
Dit PostgreSQL-script automatiseert het toekennen van standaardrechten (“default privileges”) aan alle bestaande tabellen in het public-schema.
Het script haalt eerst de standaardrechten op die zijn ingesteld voor het public-schema. Vervolgens genereert het SQL-statements om deze rechten toe te kennen aan de betreffende tabellen en voert deze statements uit. Het script negeert de oorspronkelijke toekenners (“grantors”) van de rechten en kent de rechten toe aan de opgegeven grantees, inclusief de optie om rechten door te geven (with grant option) als dat is toegestaan.
Dit zorgt ervoor dat alle tabellen in het public-schema tenminste dezelfde rechten hebben als nieuw gecreëerde tabellen, en zorgt ervoor dat onnodige itgenpsr003-meldingen voorkomen worden.
do $$
declare
r record;
begin
--
-- Grant table privileges, ignoring grantor.
--
for r in
( select 'grant '
|| rtt.privilege_type
|| ' on '
|| quote_ident(tbe.tablename)
|| ' to '
|| case
when rtt.grantee = 0
then 'PUBLIC'
else rle.rolname
end
|| case
when rtt.is_grantable
then ' with grant option'
else ''
end
stmt
--
-- Retrieve default privileges for PUBLIC schema.
--
from ( select (aclexplode(defaclacl)).privilege_type
, (aclexplode(defaclacl)).grantee
, (aclexplode(defaclacl)).is_grantable
, case defaclobjtype
when 'r' then 'table'
when 's' then 'sequence'
when 'f' then 'function'
when 't' then 'type'
else defaclobjtype::text
end
object_type
from pg_default_acl
where defaclnamespace::regnamespace = 'public'::regnamespace
) rtt
join pg_tables tbe
on tbe.schemaname = 'public'
and tbe.tablename != 'default_privileges'
and rtt.object_type = 'table'
left
outer
join pg_roles rle
on rle.oid = rtt.grantee
where 1=1
)
loop
execute r.stmt;
end loop;
end $$;