Search Postgresql Archives

pg_type.relacl

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux