From: | "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_type.relacl |
Date: | 2008-03-11 13:52:11 |
Message-ID: | 1A6E6D554222284AB25ABE3229A9276271572B@nrtexcus702.int.asurion.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2008-03-11 13:58:30 | Re: Database OID xxxxx now seems to belong to "foo" |
Previous Message | rrahul | 2008-03-11 13:47:48 | postgre vs MySQL |