Self Servicing Permissions on Database Objects

Commonly, users that have just been added to a new cluster, or users that were permissioned on a database object that has since been changed (ex. A table you were permissioned on got dropped and recreated) receive ‘Error in SQL: permission denied for relation...’ type errors when querying against objects stored in their cluster. 

Users experiencing these kinds of permissioning issues when querying against database objects like schemas and tables can follow the steps below to update their database privileges. 

Note that to run any of the queries referenced in this guide, you must have a user role created on the database you are querying against. If running these queries returns a ‘Invalid Database Username or Password. Please update your credential,’ error, you do not yet have a user role created in the cluster you are querying against, or need to update your database password. Reach out to your database administrator or Client Success for a user role to be created on the cluster.

1. Read our docs on permissioning in Platform to determine the appropriate set of privileges you need to do your work.  As a best practice, only request privileges necessary to complete your work (i.e. minimize ‘grant all’ statements)
2. Ask the owner of the object, or a superuser on your cluster, to grant you on the necessary privileges.
   • Not all organizations have superusers. To check if your organization has a superuser, run the following query in the query pane to determine who is a superuser on your cluster: 

     • select usename, usesysid, usesuper
       from pg_user 
       where usesuper = 't'
       and usename not in ('dbadmin', 'console', 'rdsdb');

   • If the owner of the schema is unknown, run the following query to determine the owner:

     • select s.nspname as schema,
       s.oid as schema_id,u.usename as owner
       from pg_catalog.pg_namespace s
       join pg_catalog.pg_user u on u.usesysid = s.nspowner
       where nspname not in ('information_schema', 'pg_catalog', 'public')
       and nspname not like 'pg_toast%'
       and nspname not like 'pg_temp_%'
       and s.nspname = 'mySchema'

   • If the owner of the table is unknown, run the following query to determine the owner:

     • select schemaname, tablename, tableowner 
       from pg_tables 
       where tablename = 'myTable'

If the problem persists, follow up with Client Success at support@civisanalytics.com