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.
