RE: [ODBC] Question regarding accessing only tables to which the user has access - Mailing list pgsql-odbc

From: John Kew [mailto:jkew@tableau.com]
Sent: Tuesday, April 24, 2018 11:38 AM
To: Fernando Luna <Fernando.Luna@Tideworks.com>; 'pgsql-odbc@postgresql.org' <pgsql-odbc@postgresql.org>
Cc: Inoue, Hiroshi <h-inoue@dream.email.ne.jp>
Subject: Re: [ODBC] Question regarding accessing only tables to which the user has access

Hello:

I broached this subject a couple of years ago. Basically, Tableau, the product we use for Business Intelligence uses the Postgres JDBC driver and we were puzzled as to why it seemed to allow users to view tables to which they didn’t have permissions on the database. It made for a poor user experience and with some sleuthing I discovered that the query that the JDBC driver uses doesn’t seem to take into account whether or not the connecting user has the SELECT privilege.

This is the query the JDBC driver is issuing:

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

Not only is this not taking into account the user’s SELECT privilege but it completely excludes materialized views!

Now, I know that your source code provides for this under specific conditions:

IE:

       if (!list_some)

       {

              if (CC_accessible_only(conn))

                     strcat(tables_query, " and has_table_privilege(c.oid, 'select')");

       }

But this is an incomplete solution and it would not be desirable to present a list of tables to a user that they have no hope of accessing.

I propose that someone change this permanently (under all conditions) to something that makes more sense like this:

select c.relname, n.nspname, c.relkind

  from pg_catalog.pg_class c, pg_catalog.pg_namespace n

where c.relkind in ('r', 'v', 'm')

   and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1')

   and has_table_privilege(c.oid, 'select')

   and n.oid = c.relnamespace

order by n.nspname, c.relname

Note that I have added an extra condition for relkind (‘m’ for materialized views) and also an extra check (has_table_privilege).

This would help our problem and provide a solution for all Tableau users as Tableau is recommending creating dynamic views that do not perform well as a work-around (which suggests they don’t know the solution to this is tied to the driver).

Let me know if this would be possible. While Hiroshi’s solution below was appreciated, I don’t think it’s workable for us to require our customer base to make changes as to how the driver is engaged within Tableau.

Regards,

Fernando Luna

Fernando.Luna@Tideworks.com

Senior SDE/Data Engineering

-----Original Message-----
From: Fernando Luna
Sent: Tuesday, March 29, 2016 8:33 AM
To: Inoue, Hiroshi <h-inoue@dream.email.ne.jp>
Cc: 'pgsql-odbc@postgresql.org' <pgsql-odbc@postgresql.org>
Subject: RE: [ODBC] Question regarding accessing only tables to which the user has access

Hi Hiroshi!

I will definitely give that a shot. 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 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<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 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