Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user

From: Mark Cave-Ayland <mark(dot)cave-ayland(at)ilande(dot)co(dot)uk>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user
Date: 2007-05-31 16:22:25
Message-ID: 1180628545.15060.26.camel@mca-desktop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi everyone,

I have a question as to whether a patch for the following would be
accepted by the pgsqlODBC project. The setup is a large database with
several hundred tables, with a small number of views for reporting
purposes. In order to generate these reports, users connect to the
PostgreSQL database as a lower-privileged user which only has
permissions to access the views.

Now the problem is that when connecting to the ODBC data source as the
low-privileged user, *all* of the tables appear in the Excel data wizard
even though the low-privileged user doesn't have any access to them. The
net result is that some of the users generating reports are struggling
to locate the views through the hundreds of listed tables which is
causing considerable frustration.

Having a look at the pgsqlODBC source, I can see that the list of
tables/views is taken from the catalogs. So I was wondering about the
possibility of adding a new tickbox option to the pgsqlODBC
configuration dialog along the lines of "Show only tables accessible by
database user" which would augment the query generating the list of
database tables with SQL similar to this:

SELECT ... WHERE ... AND (has_table_privilege(session_user, c.oid,
'select') || has_table_privilege(session_user, c.oid, 'insert') ||
has_table_privilege(session_user, c.oid, 'update') ||
has_table_privilege(session_user, c.oid, 'delete'));

This would have the effect of only displaying tables for which the user
has any of SELECT, INSERT, UPDATE and DELETE privileges. So by removing
all of these user privileges from the table, it would be removed from
the database table listing for that user.

So my question would be: i) is this the most sensible option to hide
tables from the ODBC table listing? and ii) would the project be willing
to accept a patch to implement this into the current codebase?

Many thanks,

Mark.

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Rainer Bauer 2007-05-31 17:46:45 Re: [psqlODBC 08.02.0400] Cannot build with VS2005 SP1
Previous Message Hiroshi Inoue 2007-05-30 09:35:43 Re: [psqlODBC 08.02.0400] Cannot build with VS2005 SP1