Re: pg_type.relacl - Mailing list pgsql-general

I thought I would share my hack to return a useful and dblink save way
of getting who has been granted to what functions.

CREATE SCHEMA "admin";

CREATE TYPE "admin".dba_fn_roles AS
   (schema_name character varying,
    function_name character varying,
    "owner" character varying,
    grantor character varying,
    grantee character varying,
    execute_grant boolean,
    grant_grant boolean);


CREATE OR REPLACE FUNCTION "admin".fn_get_dba_fn_roles(p_schema_name
character varying, p_function_name character varying)
  RETURNS SETOF "admin".dba_fn_roles AS
$BODY$
declare
  v_return admin.dba_fn_roles;
  v_acl aclitem[];
  v_grantee varchar;
  v_acl_string varchar;
  v_acl_item varchar;
  v_grants varchar;

begin
  select n.nspname as schema_name,
         p.proname as function_name,
         pg_get_userbyid(p.proowner) as owner,
         p.proacl
    into v_return.schema_name,
         v_return.function_name,
         v_return.owner,
         v_acl
    from pg_proc p
    join pg_namespace n
      on n.oid = p.pronamespace
   where p.proacl is not null
     and n.nspname = p_schema_name
     and p.proname = p_function_name;

   if v_acl is not null then

     for i in 1 .. array_upper(v_acl, 1) loop
       if i = 1 then
         v_acl_string := replace(array_to_string(v_acl, '|'), '"', '');
       end if;
         v_acl_item := split_part(v_acl_string, '|', i);
         v_return.grantee := substring(v_acl_item, 1, position('=' in
v_acl_item) - 1);
         if v_return.grantee = '' then
           v_return.grantee := 'public';
         end if;
         v_return.grantor := split_part(v_acl_item, '/', 2);
         v_grants := split_part(split_part(v_acl_item, '/', 1), '=', 2);
         v_return.execute_grant := case when position('X' in v_grants) >
0 then true else false end;
         v_return.grant_grant := case when position('*' in v_grants) > 0
then true else false end;
         return next v_return;
     end loop;
   end if;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

I then created an overloaded function to get everything.

CREATE OR REPLACE FUNCTION "admin".fn_get_dba_fn_roles()
  RETURNS SETOF "admin".dba_fn_roles AS
$BODY$
declare
  v_rec record;
  v_return admin.dba_fn_roles;
begin
  <<rec_loop>>
  for v_rec in select cast(n.nspname as varchar) as schema_name,
                      cast(p.proname as varchar) as function_name
                 from pg_proc p
                 join pg_namespace n
                   on n.oid = p.pronamespace
                where p.proacl is not null
                order by 1, 2 loop
     <<return_loop>>
     for v_return in select * from
admin.fn_get_dba_fn_roles(v_rec.schema_name, v_rec.function_name) loop
       return next v_return;
     end loop return_loop;
  end loop rec_loop;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


Now I can run these statements:


select * from admin.fn_get_dba_fn_roles();
and
select * from admin.fn_get_dba_fn_roles('abc', 'fn_123');


I wrote similar functions to show grants to tables.

Is there an easier way to handle this?


Jon

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Roberts, Jon
> Sent: Tuesday, March 11, 2008 8:52 AM
> To: Tom Lane
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] pg_type.relacl
>
> Thanks for the tips.  I was able to use array_to_string and then use
> split_part a bunch to split out the grantor, grantee, and each of the
> grants into separate columns.
>
> I really didn't see any documentation on aclitm[].  Generating a
report
> showing who has rights to what is little bit harder than it sounds.
>
>
> Jon
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Monday, March 10, 2008 4:08 PM
> > To: Roberts, Jon
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] pg_type.relacl
> >
> > "Roberts, Jon" <Jon.Roberts@asurion.com> writes:
> > > If the remote database is on the same server, it works but fails
on
> the
> > > different server.  It looks like the datatype aclitem[] is linked
to
> > > roles which seems weird to me.
> >
> > Why would you find that weird?  aclitem exists to store references
to
> > roles.
> >
> > > --doesn't work
> > > select t1.schema_name,
> > >         t1.table_name,
> > >         t1.table_owner,
> > >         t1.relacl
> > >    from dblink('dbname=test_dev_db user=scott password=tiger
> host=gp',
> > >                 'select n.nspname as schema_name, c.relname as
> > > table_name, pg_get_userbyid(c.relowner) as table_owner, c.relacl
> from
> > > pg_class c join pg_namespace n on n.oid = c.relnamespace'::text)
> > >                 t1(schema_name name, table_name name, table_owner
> name,
> > > relacl aclitem[]);
> >
> > > ERROR:  role "gpadmin" does not exist
> > > User gpadmin doesn't exist on my local server.
> >
> > Hmm, that's a bit annoying --- evidently, dblink is trying to cast
the
> > text string coming from the remote server into a local aclitem[]
> value,
> > and aclitemin is (quite properly) barfing.  So you need to get the
> > exposed type of the query result column to not be aclitem.
> >
> > > It wouldn't let me convert aclitem to varchar.
> >
> > 8.3 would let you do that, but in older releases you're going to
need
> > subterfuge.  Try using aclitemout() ... or actually, since relacl is
> > aclitem[], you probably need array_out().
> >
> >             regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general