Re: pg_type.relacl

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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Roberts, Jon
> Sent: Tuesday, March 11, 2008 8:52 AM
> To: Tom Lane
> Cc: pgsql-general(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
> > Sent: Monday, March 10, 2008 4:08 PM
> > To: Roberts, Jon
> > Cc: pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] pg_type.relacl
> >
> > "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general