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
