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

Hi Fernando,

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

 

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

Hi Takayuki:

Thank you so much for your quick reply. I'll try this in the morning and let you know how it goes. Additionally I'll
passon your comments to the Tableau folks. 

Have a great day!

Fernando Luna
_______________________________________
From: Tsunakawa, Takayuki [tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, March 28, 2016 8:10 PM
To: Fernando Luna; 'pgsql-odbc@postgresql.org'
Subject: RE: Question regarding accessing only tables to which the user has access

Hello, Fernando,

Try setting the extra option parameter in either of the following ways:

* If you use the connection string, add "AB=10".

* If you configure the data source using the ODBC Administrator, enter "10" in the "Extra Opts" text box in advanced
settingspage 2. 


BTW, maybe Tableau should provide the option to list the tables on which the user has SELECT privilege.  Tableau should
doso by first calling SQLGetInfo(SQL_ACCESSIBLE_TABLES), and then calling SQLTablePrivileges() if SQLGetInfo() returns
"N". Tableau perhaps calls SQLTables() instead.  As the reference page describes, it is driver-dependent whether
SQLTables()returns only accessible tables or all tables. 


SQLTables Function
https://msdn.microsoft.com/en-us/library/ms711831(v=vs.85).aspx
--------------------------------------------------
SQLTables lists all tables in the requested range. A user may or may not have SELECT privileges to any of these tables.
Tocheck accessibility, an application can: 

Call SQLGetInfo and check the SQL_ACCESSIBLE_TABLES information type.

Call SQLTablePrivileges to check the privileges for each table.
--------------------------------------------------


Regards
Takayuki Tsunakawa

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