Re: Question regarding accessing only tables to which the user has access

Hi Hiroshi!

I will definitely give that a shit. Thanks so much for your quick reply!

Fernando Luna
________________________________________
From: Inoue, Hiroshi [h-inoue@dream.email.ne.jp]
Sent: Monday, March 28, 2016 8:49 PM
To: Fernando Luna
Cc: 'pgsql-odbc@postgresql.org'
Subject: Re: [ODBC] Question regarding accessing only tables to which the user has access

Hi Fernando,

On 2016/03/29 2:40, Fernando Luna wrote:
I am using Tableau version 9.2 to connect to a PostgreSQL database (version 9.4). This database resides in a VM running
RedhatCentOS 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<https://ftp.postgresql.org/pub/odbc/versions/msi/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
listingtables to which the connected user has no privileges and it makes for a poor user experience to allow Tableau to
dothat 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
andfound 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
latestdriver 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
nothingabout Postgres and ODBC and would appreciate being pointed in the right direction as to how to influence pgsql
odbcto 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


Please set the "Extra Opts" option of  Advanced Options(Datasource) Page 2 using ODBC Data Source Administrator.
Try to set Extra Opts to 0x10.

regards,
Hiroshi Inoue