+ New Ticket

Error reading schema list : Permission denied for relation pg_user



  • Official comment
    Aleksander Vinokur

    Hello Supriya,

    Please touch base with IT support of your company in order to get a select permission on all necessary system objects.

    Regards, Alex.

    Comment actions Permalink
  • Avatar
    Supriya Thigale

    Hi Alex,

    I can grant permissions to all system tables. Granting access is not an issue.

    But i would like to know which all system tables does Object Browser need and why since I do not face this issue with any other tool to connect redshift ?



    Comment actions Permalink
  • Avatar
    Aleksander Vinokur


    Unfortunately, we have no access to any Redshift cluster at this moment.

    Please, use the menu Help - Show Log File in order to find all the system objects used by Aginity Workbench.

    Regards, Alex.

    Comment actions Permalink
  • Avatar
    Supriya Thigale

    Thanks a lot Alex. I figured it out with help of logs

    It refers pg_user and pg_database to check which permission user has to view specific databases

    select d.datname as dbname,  -- 0
           d.oid,                -- 1
           u.usename as dbowner, -- 2
           pg_encoding_to_char(d.encoding), -- 3
           obj_description(d.oid, 'pg_database'), -- 4
           d.datallowconn        -- 5
      from pg_catalog.pg_database d
      left join pg_catalog.pg_user u on u.usesysid = d.datdba



    To load objects in databases,  it uses

    select c.relname,      -- 0
           c.oid::bigint , -- 1
           d.description,  -- 2
           u.nspname,      -- 3
           '',                         -- 4
           ts.spcname,     -- 5
           uo.usename as ObjectOwner, -- 6
           c.relnatts,     -- 7
           c.relacl,       -- 8
           c.reldiststyle  -- 9
      from pg_catalog.pg_class c
      join pg_catalog.pg_namespace u on u.oid = c.relnamespace
      join pg_catalog.pg_user uo on uo.usesysid = c.relowner
      left join pg_catalog.pg_description d on d.objoid = c.oid and d.objsubid = 0
      left join pg_catalog.pg_tablespace ts on ts.oid = c.reltablespace
     where lower(c.relkind) in ('v')
       and lower(u.nspname) not like 'pg_temp_%'
       and c.oid not in (select distinct dep.objid -- exclude autogenerated tables
                           from pg_catalog.pg_depend dep
                          where dep.deptype = 'i')

    Post answered

    Comment actions Permalink
  • Avatar
    Thomas Koshy

    Alex, some of our users are getting this error also. It works on old Windows version 4.9.*, but not on any new PC or any Mac version. It gives this error java.sql.SQLException "[Amazon](500310) Invalid operation: permission denied for relation pg_user_info;"

    We would rather not give any access to system tables if we do not have to. Any idea why its working in some versions without allowing this access and not all?



    Comment actions Permalink

Please sign in to leave a comment.