From: | "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp> |
---|---|
To: | Fernando Luna <Fernando(dot)Luna(at)Tideworks(dot)com> |
Cc: | "'pgsql-odbc(at)postgresql(dot)org'" <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Question regarding accessing only tables to which the user has access |
Date: | 2016-03-29 03:49:09 |
Message-ID: | 56F9FB35.1000801@dream.email.ne.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Luna | 2016-03-29 15:33:27 | Re: Question regarding accessing only tables to which the user has access |
Previous Message | Fernando Luna | 2016-03-29 03:27:30 | Re: Question regarding accessing only tables to which the user has access |