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

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.

Adnan DURSUN wrote:
>
> Hi all,
>
> I am using VB6 for frontend. Does pgsqlODBC support PostgreSQL LISTEN
> / NOTIFY feature ?

Currently not.
How do you expect to handle LISTEN/NOTIFY using ODBC ?

regards,
Hiroshi Inoue

Hi all,

I am using VB6 for frontend. Does pgsqlODBC support PostgreSQL LISTEN /
NOTIFY feature ? If not, how can i handle the notifications ?

Best regards

Adnan DURSUN
ASRIN Bilisim Ltd.

----- Original Message -----
From: "Hiroshi Inoue" <inoue(at)tpf(dot)co(dot)jp>
To: "Adnan DURSUN" <a_dursun(at)hotmail(dot)com>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Sent: Thursday, May 31, 2007 5:48 PM
Subject: Re: [ODBC] LISTEN / NOTIFY Feture

> Adnan DURSUN wrote:
>>
>> Hi all,
>>
>> I am using VB6 for frontend. Does pgsqlODBC support PostgreSQL LISTEN
>> / NOTIFY feature ?
>
> Currently not.
> How do you expect to handle LISTEN/NOTIFY using ODBC ?
>

I dont know how. I think another problem to handle this is ADO !...
ADO doesnt have any event that will be raised when a notifications arrived
to client.
I consider, maybe another way is to listen the port that ODBC
communicates with database on client machine. But, windows doesnt allow to
listen that port.
It says "port is buys" when i try to listen that port.

Maybe it is possibble to make a second conneciton to database beyond
ODBC in the same app using winsock. But, libpg.dll coulnd not be used with
inVB6.
When i tried this i got "Bad DLL calling convention" message !

So, i didnt find any way to do this !!

Best regards

Adnan DURSUN
ASRIN Bilişim Ltd.

Hi everyone,

I've just completed the patch for only listing tables accessible by the
current user for psqlodbc as detailed in my email here:
http://archives.postgresql.org/pgsql-odbc/2007-05/msg00068.php. As
suggested in the original post, I have implemented a tick box called
"Only list user-accessible tables" on page 2 of the Datasource Advanced
Options dialog. When this box is ticked, only tables on which the user
has one of SELECT, INSERT, DELETE or UPDATE privileges will appear in
the table list for the given DSN.

I think that this would be a really useful feature to add to the
psqlodbc driver, and so I have attached a diff from CVS HEAD. As far as
I know, the only part I have missed is adding the tickbox and its
corresponding translation to the Japanese version of the dialog in
psqlodbc.rc. Please let me know if there is any more work I need to do
in order to get this applied.

Kind regards,

Mark.

On Thu, 2007-06-07 at 17:13 +0100, Mark Cave-Ayland wrote:
> Hi everyone,
>
> I've just completed the patch for only listing tables accessible by the
> current user for psqlodbc as detailed in my email here:
> http://archives.postgresql.org/pgsql-odbc/2007-05/msg00068.php. As
> suggested in the original post, I have implemented a tick box called
> "Only list user-accessible tables" on page 2 of the Datasource Advanced
> Options dialog. When this box is ticked, only tables on which the user
> has one of SELECT, INSERT, DELETE or UPDATE privileges will appear in
> the table list for the given DSN.
>
> I think that this would be a really useful feature to add to the
> psqlodbc driver, and so I have attached a diff from CVS HEAD. As far as
> I know, the only part I have missed is adding the tickbox and its
> corresponding translation to the Japanese version of the dialog in
> psqlodbc.rc. Please let me know if there is any more work I need to do
> in order to get this applied.
>
>
> Kind regards,
>
> Mark.

Hi everyone,

I haven't yet received any feedback (either positive or negative) from
any psqlODBC developers regarding my patch to hide tables that are not
accessible to the current user here:
http://archives.postgresql.org/pgsql-odbc/2007-06/msg00028.php. I'd like
to help get this patch applied to CVS if possible, as it has proved to
be a really useful feature for us.

Kind regards,

Mark.

Sorry for the late reply.

Mark Cave-Ayland wrote:
> On Thu, 2007-06-07 at 17:13 +0100, Mark Cave-Ayland wrote:
>> Hi everyone,
>>
>> I've just completed the patch for only listing tables accessible by the
>> current user for psqlodbc as detailed in my email here:
>> http://archives.postgresql.org/pgsql-odbc/2007-05/msg00068.php. As
>> suggested in the original post, I have implemented a tick box called
>> "Only list user-accessible tables" on page 2 of the Datasource Advanced
>> Options dialog.

Recently added boolean options are defined as some bits in Extra Opts.
Is it OK to define the option as a bit in Extra Opts ?

>> When this box is ticked, only tables on which the user
>> has one of SELECT, INSERT, DELETE or UPDATE privileges will appear in
>> the table list for the given DSN.

Isn't it sufficient to chack SELECT privilege only ?

>> I think that this would be a really useful feature to add to the
>> psqlodbc driver, and so I have attached a diff from CVS HEAD. As far as
>> I know, the only part I have missed is adding the tickbox and its
>> corresponding translation to the Japanese version of the dialog in
>> psqlodbc.rc. Please let me know if there is any more work I need to do
>> in order to get this applied.

regards,
Hiroshi Inoue

On Wed, 2007-06-20 at 23:47 +0900, Hiroshi Inoue wrote:

> Recently added boolean options are defined as some bits in Extra Opts.
> Is it OK to define the option as a bit in Extra Opts ?

Inoue-san,

Thanks for your feedback. I've had a look at the code for Extra Options
(and thought a bit more about it), and I'd prefer to keep the option as
a separate tick-box if possible. My reason for this is that the people
who want this feature are the people who want to get rid of extra tables
they see over ODBC. Generally these people have already found the "Hide
System Tables" option but can't work out how to remove the extra tables,
so for me it makes sense to put the two options close to each other.
Also, for support purposes, it would be much easier to explain a tick
box over the phone rather than get them to read out the "Extra Options",
calculate the new value and then type it back in as a hex value!

I wonder if anyone else who would use this option has any other feelings
on this?

> >> When this box is ticked, only tables on which the user
> >> has one of SELECT, INSERT, DELETE or UPDATE privileges will appear in
> >> the table list for the given DSN.
>
> Isn't it sufficient to chack SELECT privilege only ?

Possibly. I wasn't sure on the exact semantics of ODBC, but considered
that for example, if you have DELETE privilege on a table, you would
still expect to see it in a table listing even if you couldn't see the
contents itself. This is the same behaviour psqlODBC exhibits without
the patch if your user doesn't have permissions on the tables in the
ODBC listing.

Kind regards,

Mark.

Mark Cave-Ayland wrote:
> On Wed, 2007-06-20 at 23:47 +0900, Hiroshi Inoue wrote:
>
>> Recently added boolean options are defined as some bits in Extra Opts.
>> Is it OK to define the option as a bit in Extra Opts ?
>
> Inoue-san,

Hi Mark,
Sorry for the late answer.

> Thanks for your feedback. I've had a look at the code for Extra Options
> (and thought a bit more about it), and I'd prefer to keep the option as
> a separate tick-box if possible.

There are already so many options and I'm not eager to increase
the options excessively. It's also pretty painful to add a check
box etc in the setup dialog. Basically I want to use bits in the
Extra Opts for boolean options herafter.

> My reason for this is that the people
> who want this feature are the people who want to get rid of extra tables
> they see over ODBC. Generally these people have already found the "Hide
> System Tables" option but can't work out how to remove the extra tables,
> so for me it makes sense to put the two options close to each other.
> Also, for support purposes, it would be much easier to explain a tick
> box over the phone rather than get them to read out the "Extra Options",
> calculate the new value and then type it back in as a hex value!

How about changing the default behavior of SQLTables to list only
SELECTable tables and add a bit to Extra Opts to list inaccessible
tables also ?

regards,
HIroshi Inoue

Hiroshi Inoue wrote:
> How about changing the default behavior of SQLTables to list only
> SELECTable tables and add a bit to Extra Opts to list inaccessible
> tables also ?

That has real potential to break applications - please don't! At the
very least it should have the check for all possible privileges as I
think Mark originally proposed, but even then I'm not convinced it's a
good idea.

Regards, Dave

On Wed, 2007-06-27 at 09:01 +0900, Hiroshi Inoue wrote:

> There are already so many options and I'm not eager to increase
> the options excessively. It's also pretty painful to add a check
> box etc in the setup dialog. Basically I want to use bits in the
> Extra Opts for boolean options herafter.

I can see that adding extra options can be a little difficult, but I'd
still like to see a simple tick box to toggle the option, purely because
it is a useful feature. Perhaps another way forward is that "Extra
Options" could be extended into a new dialog, and then the option added
there? If it were just an option for developers I wouldn't be too
worried, but there does appear to be user demand for this, and users
aren't the best at calculating bitmasks in hex ;)

> How about changing the default behavior of SQLTables to list only
> SELECTable tables and add a bit to Extra Opts to list inaccessible
> tables also ?

I think this would risk breaking a lot of existing applications. I'm not
worried that the extra tables are there normally, but there are
applications where they can be disabled to help less skilled users who
are often tasked with creating reports with other tools such as Excel.
I've already had people emailing offlist asking about when this feature
will become available in the official driver, and it would be a shame to
relegate such a useful feature to an obscure part of the driver.

Kind regards,

Mark.

Mark Cave-Ayland wrote:
> On Wed, 2007-06-27 at 09:01 +0900, Hiroshi Inoue wrote:
>
>> How about changing the default behavior of SQLTables to list only
>> SELECTable tables and add a bit to Extra Opts to list inaccessible
>> tables also ?
>
> I think this would risk breaking a lot of existing applications.

Really ?
I can hardly imagine the applications which would update/delete
the rows whose contents couldn't be seen.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> Mark Cave-Ayland wrote:
>> On Wed, 2007-06-27 at 09:01 +0900, Hiroshi Inoue wrote:
>>
>>> How about changing the default behavior of SQLTables to list only
>>> SELECTable tables and add a bit to Extra Opts to list inaccessible
>>> tables also ?
>> I think this would risk breaking a lot of existing applications.
>
> Really ?
> I can hardly imagine the applications which would update/delete
> the rows whose contents couldn't be seen.

insert-only audit tables?

Yes, they should really be handled by server-side triggers, but I know
of at least a couple of apps running on PostgreSQL with psqlODBC that do
it client side.

Regards, Dave

On Thu, 2007-06-28 at 16:54 +0100, Dave Page wrote:
> Hiroshi Inoue wrote:
> > Mark Cave-Ayland wrote:
> >> On Wed, 2007-06-27 at 09:01 +0900, Hiroshi Inoue wrote:
> >>
> >>> How about changing the default behavior of SQLTables to list only
> >>> SELECTable tables and add a bit to Extra Opts to list inaccessible
> >>> tables also ?
> >> I think this would risk breaking a lot of existing applications.
> >
> > Really ?
> > I can hardly imagine the applications which would update/delete
> > the rows whose contents couldn't be seen.
>
> insert-only audit tables?
>
> Yes, they should really be handled by server-side triggers, but I know
> of at least a couple of apps running on PostgreSQL with psqlODBC that do
> it client side.
>
> Regards, Dave

Hi guys,

The use-case I was thinking of was SQL builder tools that allow you to
choose table names from the SQLTables list - even if you don't want to
perform a SELECT, you may still want to be able to include the table in
your query for an INSERT, UPDATE or DELETE.

Where does that leave the status of the patch? Is there any way at all
of getting the feature included with a GUI tickbox to select/deselect
it, or would the patch be rejected regardless?

Kind regards,

Mark.

Mark Cave-Ayland wrote:
> The use-case I was thinking of was SQL builder tools that allow you to
> choose table names from the SQLTables list - even if you don't want to
> perform a SELECT, you may still want to be able to include the table in
> your query for an INSERT, UPDATE or DELETE.
>
> Where does that leave the status of the patch? Is there any way at all
> of getting the feature included with a GUI tickbox to select/deselect
> it, or would the patch be rejected regardless?
>

Hi Mark,

I don't object to the feature; just to making it the default because of
the potential to break apps. I can appreciate where Hiroshi is coming
from - we are getting too many options - but I don't necessarily agree
that the answer is to squeeze all the new ones into a set of bit flags
though, except for *really* obscure or expert options. What I think
should happen instead is a concerted effort to move some of the older
options that aren't really used any more into the bit flags to simplify
the dialogues. Obviously they would also retain their existing connect
string options.

Ones that spring to mind include the protocol versions which is rarely,
if ever needed these days, KSQO, Recognise Unique Indexes, Disallow
premature...

Regards, Dave

Hiroshi Inoue wrote:
> There are already so many options and I'm not eager to increase
> the options excessively. It's also pretty painful to add a check
> box etc in the setup dialog.

Inoue san,

while the parameter topic is on I would like to ask wether there is a
plan to include further ssl-parameters in ther connection string like
ngODBC has.
In theory the ngODBC accepts a path to the key- and cerificate-files
along with a passphrase. As of now I couldn't get it to work.
It would be nice if those features could be provided by the PostgreSQL
project's own ODBC driver, too.

At least the passphrase would be handy so that one could keep the
ssl-keyfile encrypted on the harddisk.
For security reasons this concerns me a bit because we have notebooks
that run our application and some usbsticks that hold the keys.

AFAIK PGAdmin doesn't support this either.

Regards
Andreas

Dave Page wrote:
> Hiroshi Inoue wrote:
>> Mark Cave-Ayland wrote:
>>> On Wed, 2007-06-27 at 09:01 +0900, Hiroshi Inoue wrote:
>>>
>>>> How about changing the default behavior of SQLTables to list only
>>>> SELECTable tables and add a bit to Extra Opts to list inaccessible
>>>> tables also ?
>>> I think this would risk breaking a lot of existing applications.
>> Really ?
>> I can hardly imagine the applications which would update/delete
>> the rows whose contents couldn't be seen.
>
> insert-only audit tables?
>
> Yes, they should really be handled by server-side triggers, but I know
> of at least a couple of apps running on PostgreSQL with psqlODBC that do
> it client side.

Hmm the apps list the tables using SQLTables() ?

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> Dave Page wrote:
>> Hiroshi Inoue wrote:
>>> Mark Cave-Ayland wrote:
>>>> On Wed, 2007-06-27 at 09:01 +0900, Hiroshi Inoue wrote:
>>>>
>>>>> How about changing the default behavior of SQLTables to list only
>>>>> SELECTable tables and add a bit to Extra Opts to list inaccessible
>>>>> tables also ?
>>>> I think this would risk breaking a lot of existing applications.
>>> Really ?
>>> I can hardly imagine the applications which would update/delete
>>> the rows whose contents couldn't be seen.
>> insert-only audit tables?
>>
>> Yes, they should really be handled by server-side triggers, but I know
>> of at least a couple of apps running on PostgreSQL with psqlODBC that do
>> it client side.
>
> Hmm the apps list the tables using SQLTables() ?

I don't recall (and I don't have access to them any more to check), but
as we've had the current behaviour for the last 10 years or so, and
no-one has really complained until now, I don't think we should risk
changing the default - at least without checking for SELECT, UPDATE,
INSERT or DELETE rather than just SELECT.

Regards, Dave

On Fri, 2007-06-29 at 08:37 +0100, Dave Page wrote:

> I don't recall (and I don't have access to them any more to check), but
> as we've had the current behaviour for the last 10 years or so, and
> no-one has really complained until now, I don't think we should risk
> changing the default - at least without checking for SELECT, UPDATE,
> INSERT or DELETE rather than just SELECT.
>
> Regards, Dave

Hi Dave,

Also just to clarify in case it's not clear - my preference is to keep
the existing behaviour by default (and allow the new behaviour as an
option), mainly to avoid breaking existing applications. I'm not too
worried about the argument as to whether it is the default behaviour or
not. As long as the feature can be included in the driver with a GUI
option to enable it, I'll be happy.

Kind regards,

Mark.