cancel
Showing results for 
Search instead for 
Did you mean: 

Checking user privileges on a table with a case-sensitive name

Symptoms​

Having trouble calling has_table_privilege() or otherwise retrieving table privileges for a user. Seeing ERROR: relation "<tablename>" does not exist in the logs.

Diagnosis

This occurs when a table is created with a name enclosed in double-quotes.  In these situations, the table name is case-sensitive, and needs to be accessed in a special way.

Solution

In PostgreSQL/EPAS, tables can be created with case-sensitive names using double quotes. In order to check the user privileges on such tables using has_table_privilege(), the following options exist:

 

1.  Use quote_ident around the table name as below:

 

               select t.schemaname, quote_ident(t.tablename), u.usename,
               has_table_privilege(u.usename, quote_ident(t.tablename), 'select') as select,
               has_table_privilege(u.usename, quote_ident(t.tablename), 'insert') as insert,
               has_table_privilege(u.usename, quote_ident(t.tablename), 'update') as update,
               has_table_privilege(u.usename, quote_ident(t.tablename), 'delete') as delete
                         from pg_tables t, pg_user u
               where t.schemaname not in ('information_schema','pg_catalog');

 

 

2.  Use pg_class and OID to gather information:

 

 

        select t.relname , u.usename , has_table_privilege(u.usename, t.oid, 'select')
        as
        select from pg_class t, pg_user u                 
        where relkind ='r' ;

 

Below are the reference links:

https://www.postgresql.org/docs/11/functions-info.html
https://www.enterprisedb.com/blog/list-user-privileges-postgresqlppas-11

 

Version history
Revision #:
6 of 6
Last update:
‎05-23-2019 06:59 AM
Updated by: