Massepåvirkning af standardrettigheder til tabeller i PostgreSQL

Et ofte forekommende problem er tildeling af rettigheder på PostgreSQL-tabeller, hvor nye tabeller automatisk får tildelt skemaets standardrettigheder, men eksisterende tabeller ikke gør.

Dette PostgreSQL-script automatiserer tildelingen af standardrettigheder (“default privileges”) til alle eksisterende tabeller i public-skemaet.

Scriptet henter først de standardrettigheder, der er sat op for public-skemaet. Derefter genererer det SQL-statements til at tildele disse rettigheder til de pågældende tabeller/sequences og udfører disse statements. Scriptet ignorerer de oprindelige tildelere (“grantors”) af rettighederne og tildeler rettighederne til de angivne grantees, inklusive muligheden for at videregive rettigheder (with grant option), hvis det er tilladt.

Dette sikrer, at alle tabeller og sequences i public-skemaet som minimum har de samme rettigheder som nyoprettede tabeller, og forhindrer unødvendige itgenpsr003-meddelelser.

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 $$;

er det også meningen, at rettighederne på funktioner og typer skal videregives?

Tilføj så et left outer join for hver kategori.