Обсуждение: pg_type.relacl

I need to get a list of roles granted select on a table from a remote
database.  Ideally, each grantee would be represented as VARCHAR and not
an ACLITEM.

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.

--works!
select t1.schema_name,
        t1.table_name,
        t1.table_owner,
        t1.relacl
   from dblink('dbname=elt0n user=scott password=tiger host=localhost',
                '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[]);


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

********** Error **********

ERROR: role "gpadmin" does not exist
SQL state: 42704

User gpadmin doesn't exist on my local server.

I also tried looping through the array in the source db to return a
result set but that didn't work.  It wouldn't let me convert aclitem to
varchar.


ERROR: cannot cast type aclitem to character varying
SQL state: 42846


What is the trick??


Jon

"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

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

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