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@xxxxxxxxxxxxx] > Sent: Monday, March 10, 2008 4:08 PM > To: Roberts, Jon > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: pg_type.relacl > > "Roberts, Jon" <Jon.Roberts@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
