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.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

----- Original Message -----
From: "Hiroshi Inoue" <inoue@tpf.co.jp>
To: "Adnan DURSUN" <a_dursun@hotmail.com>
Cc: <pgsql-odbc@postgresql.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.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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...5/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.




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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...5/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...6/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.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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...5/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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.




---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend