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