Massaal toekennen van standaardrechten aan tabellen in PostgreSQL

[forums]

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/sequences 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 en sequences 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(coalesce(tbe.tablename, seq.sequencename))
           || ' 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(dal.defaclacl)).privilege_type
             ,      (aclexplode(dal.defaclacl)).grantee
             ,      (aclexplode(dal.defaclacl)).is_grantable
             ,      case dal.defaclobjtype
                    when 'r' then 'table'
                    when 'S' then 'sequence'
                    when 'f' then 'function'
                    when 't' then 'type'
                    else dal.defaclobjtype::text
                    end
                    object_type
             from   pg_default_acl dal
             where  dal.defaclnamespace::regnamespace = 'public'::regnamespace
           ) rtt
    left
    outer
    join   pg_tables tbe
    on     tbe.schemaname = 'public'
    and    tbe.tablename != 'default_privileges'
    and    rtt.object_type = 'table'
    left
    outer
    join   pg_sequences seq
    on     seq.schemaname = 'public'
    and    seq.sequencename != 'default_privileges'
    and    rtt.object_type = 'sequence'
    left
    outer
    join   pg_roles rle
    on     rle.oid = rtt.grantee
    where  1=1
    order
    by     rtt.object_type
    ,      tbe.tablename
    ,      seq.sequencename
    ,      rtt.privilege_type
  )
  loop
    raise notice '%', concat('Execute ', r.stmt);
    execute r.stmt;
  end loop;
end $$;

is het ook de bedoeling om de rechten op functies en types door te geven?

Voeg dan voor elke categorie een left outer join toe.