I am using Tableau version 9.2 to connect to a PostgreSQL database (version 9.4). This database resides in a VM running Redhat CentOS and I am connecting from my Windows 7 box using the latest Windows driver found under http://www.postgresql.org/ftp/odbc/versions/msi/ . The zipfile name is psqlodbc_09_05_0100-x64.zip
My question is this. When Tableau connects it retrieves a list of tables/views to use. My problem is that Tableau is listing tables to which the connected user has no privileges and it makes for a poor user experience to allow Tableau to do that in our case. We want to only list those tables/views to which the connected user has at the very least select privileges. At first I thought it was Tableau doing this and after a while I decided to turn on logging on PostgreSQL and found that the following query is issued whenever I connect using Tableau.
select relname, nspname, relkind
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where relkind in ('r', 'v')
and nspname not in ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1')
and n.oid = relnamespace
order by nspname, relname
I discovered that this query is executed by the postgres odbc driver on connect. I looked at the source code for the latest driver and found this code snippet in info.c:
if (!list_some)
{
if (CC_accessible_only(conn))
strcat(tables_query, " and has_table_privilege(c.oid, 'select')");
}
My question is, how can I get the driver to add this snippet to the query? Please bear in mind that I know nearly nothing about Postgres and ODBC and would appreciate being pointed in the right direction as to how to influence pgsql odbc to issue the query like this:
select relname, nspname, relkind
from pg_catalog.pg_class c, pg_catalog.pg_namespace n
where relkind in ('r', 'v')
and nspname not in ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1')
and has_table_privilege(c.oid, 'select')
and n.oid = relnamespace
order by nspname, relname
I’ve tried searching for references to CC_accessible_only and references to “has_table_privileges” in your forum/mailing list but I’ve not found anything that tells me definitively how to accomplish this.
Thanks!
Fernando Luna