Proposal for new pgsqlODBC feature - hiding tables inaccessible to

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

LISTEN / NOTIFY Feture

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

Re: 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 ?

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

Re: LISTEN / NOTIFY Feature

----- Original Message -----=20
From: "Hiroshi Inoue"
To: "Adnan DURSUN"
Cc:
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 LIST=
EN=20
>> / 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 !.=
...=20
ADO doesnt have any event that will be raised when a notifications arrive=
d=20
to client.
I consider, maybe another way is to listen the port that ODBC=20
communicates with database on client machine. But, windows doesnt allow t=
o=20
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 bey=
ond=20
ODBC in the same app using winsock. But, libpg.dll coulnd not be used wit=
h=20
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=FEim Ltd.=20


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

http://archives.postgresql.org

Re: Proposal for new pgsqlODBC feature - hiding tables

--=-PSByADNWJfNODkiNCpAv
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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.p hp. 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.



--=-PSByADNWJfNODkiNCpAv
Content-Disposition: attachment; filename=psqlodbc-listonlyusertables.patch
Content-Type: text/x-patch; name=psqlodbc-listonlyusertables.patch; charset=utf-8
Content-Transfer-Encoding: 7bit

Index: connection.c
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/connection.c,v
retrieving revision 1.161
diff -u -r1.161 connection.c
--- connection.c 4 Jun 2007 11:49:49 -0000 1.161
+++ connection.c 7 Jun 2007 14:34:03 -0000
@@ -286,6 +286,7 @@
conninfo->fake_mss = -1;
conninfo->cvt_null_date_string = -1;
conninfo->autocommit_public = SQL_AUTOCOMMIT_ON;
+ conninfo->list_usertables_only = -1;
#ifdef _HANDLE_ENLIST_IN_DTC_
conninfo->xa_opt = -1;
#endif /* _HANDLE_ENLIST_IN_DTC_ */
Index: connection.h
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/connection.h,v
retrieving revision 1.85
diff -u -r1.85 connection.h
--- connection.h 2 Jun 2007 05:32:18 -0000 1.85
+++ connection.h 5 Jun 2007 05:18:57 -0000
@@ -306,6 +306,7 @@
signed char fake_mss;
signed char cvt_null_date_string;
signed char autocommit_public;
+ signed char list_usertables_only;
#ifdef _HANDLE_ENLIST_IN_DTC_
signed char xa_opt;
#endif /* _HANDLE_ENLIST_IN_DTC_ */
Index: dlg_specific.c
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/dlg_specific.c,v
retrieving revision 1.80
diff -u -r1.80 dlg_specific.c
--- dlg_specific.c 2 Jun 2007 05:32:18 -0000 1.80
+++ dlg_specific.c 7 Jun 2007 14:38:22 -0000
@@ -188,6 +188,7 @@
INI_BYTEAASLONGVARBINARY "=%d;"
INI_USESERVERSIDEPREPARE "=%d;"
INI_LOWERCASEIDENTIFIER "=%d;"
+ INI_LISTUSERTABLESONLY "=%d;"
#ifdef _HANDLE_ENLIST_IN_DTC_
INI_XAOPT "=%d" /* XAOPT */
#endif /* _HANDLE_ENLIST_IN_DTC_ */
@@ -223,6 +224,7 @@
,ci->bytea_as_longvarbinary
,ci->use_server_side_prepare
,ci->lower_case_identifier
+ ,ci->list_usertables_only
#ifdef _HANDLE_ENLIST_IN_DTC_
,ci->xa_opt
#endif /* _HANDLE_ENLIST_IN_DTC_ */
@@ -293,6 +295,8 @@
flag |= BIT_USESERVERSIDEPREPARE;
if (ci->lower_case_identifier)
flag |= BIT_LOWERCASEIDENTIFIER;
+ if (ci->list_usertables_only)
+ flag |= BIT_LISTUSERTABLESONLY;

if (ci->sslmode[0])
olen = snprintf(&connect_string[hlen], nlen, ";"
@@ -407,10 +411,12 @@
sprintf(ci->show_system_tables, "%d", (char)((flag & BIT_SHOWSYSTEMTABLES) != 0));
sprintf(ci->show_oid_column, "%d", (char)((flag & BIT_SHOWOIDCOLUMN) != 0));
sprintf(ci->fake_oid_index, "%d", (char)((flag & BIT_FAKEOIDINDEX) != 0));
+ ci->list_usertables_only = (char)((flag & BIT_LISTUSERTABLESONLY) != 0);
ci->true_is_minus1 = (char)((flag & BIT_TRUEISMINUS1) != 0);
ci->bytea_as_longvarbinary = (char)((flag & BIT_BYTEAASLONGVARBINARY) != 0);
ci->use_server_side_prepare = (char)((flag & BIT_USESERVERSIDEPREPARE) != 0);
ci->lower_case_identifier = (char)((flag & BIT_LOWERCASEIDENTIFIER) != 0);
+
}
BOOL
copyAttributes(ConnInfo *ci, const char *attribute, const char *value)
@@ -494,6 +500,8 @@
ci->use_server_side_prepare = atoi(value);
else if (stricmp(attribute, INI_LOWERCASEIDENTIFIER) == 0 || stricmp(attribute, ABBR_LOWERCASEIDENTIFIER) == 0)
ci->lower_case_identifier = atoi(value);
+ else if (stricmp(attribute, INI_LISTUSERTABLESONLY) == 0 || stricmp(attribute, ABBR_LISTUSERTABLESONLY) == 0)
+ ci->list_usertables_only = atoi(value);
else if (stricmp(attribute, INI_SSLMODE) == 0 || stricmp(attribute, ABBR_SSLMODE) == 0)
{
switch (value[0])
@@ -670,6 +678,9 @@
ci->bde_environment = 0;
if (ci->cvt_null_date_string < 0)
ci->cvt_null_date_string = 0;
+ if (ci->list_usertables_only < 0)
+ ci->list_usertables_only = DEFAULT_LISTUSERTABLESONLY;
+
#ifdef _HANDLE_ENLIST_IN_DTC_
if (ci->xa_opt < 0)
ci->xa_opt = DEFAULT_XAOPT;
@@ -835,6 +846,14 @@
if (ci->sslmode[0] == '\0' || overwrite)
SQLGetPrivateProfileString(DSN, INI_SSLMODE, "", ci->sslmode, sizeof(ci->sslmode), ODBC_INI);

+ if (ci->list_usertables_only < 0 || overwrite)
+ {
+ SQLGetPrivateProfileString(DSN, INI_LISTUSERTABLESONLY, "", temp, sizeof(temp), ODBC_INI);
+ if (temp[0])
+ ci->list_usertables_only = atoi(temp);
+ }
+
+
#ifdef _HANDLE_ENLIST_IN_DTC_
if (ci->xa_opt < 0 || overwrite)
{
@@ -1117,6 +1136,11 @@
INI_SSLMODE,
ci->sslmode,
ODBC_INI);
+ sprintf(temp, "%d", ci->list_usertables_only);
+ SQLWritePrivateProfileString(DSN,
+ INI_LISTUSERTABLESONLY,
+ temp,
+ ODBC_INI);
#ifdef _HANDLE_ENLIST_IN_DTC_
sprintf(temp, "%d", ci->xa_opt);
SQLWritePrivateProfileString(DSN, INI_XAOPT, temp, ODBC_INI);
Index: dlg_specific.h
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/dlg_specific.h,v
retrieving revision 1.55
diff -u -r1.55 dlg_specific.h
--- dlg_specific.h 11 Apr 2007 15:22:23 -0000 1.55
+++ dlg_specific.h 7 Jun 2007 13:20:46 -0000
@@ -141,6 +141,8 @@
#define ABBR_LOWERCASEIDENTIFIER "C9"
#define INI_SSLMODE "SSLmode"
#define ABBR_SSLMODE "CA"
+#define INI_LISTUSERTABLESONLY "ListUserTablesOnly"
+#define ABBR_LISTUSERTABLESONLY "CB"
#define INI_EXTRAOPTIONS "AB"

#define SSLMODE_DISABLE "disable"
@@ -180,8 +182,9 @@
#define BIT_BYTEAASLONGVARBINARY (1L<<24)
#define BIT_USESERVERSIDEPREPARE (1L<<25)
#define BIT_LOWERCASEIDENTIFIER (1L<<26)
+#define BIT_LISTUSERTABLESONLY (1L<<27)

-#define EFFECTIVE_BIT_COUNT 27
+#define EFFECTIVE_BIT_COUNT 28

/* Mask for extra options */
#define BIT_FORCEABBREVCONNSTR 1L
@@ -203,6 +206,7 @@
#define DEFAULT_UNIQUEINDEX 1 /* dont recognize */
#define DEFAULT_COMMLOG 0 /* dont log */
#define DEFAULT_DEBUG 0
+#define DEFAULT_LISTUSERTABLESONLY 0 /* show all tables by default */
#define DEFAULT_UNKNOWNSIZES UNKNOWNS_AS_MAX


Index: dlg_wingui.c
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/dlg_wingui.c,v
retrieving revision 1.18
diff -u -r1.18 dlg_wingui.c
--- dlg_wingui.c 2 Jun 2007 05:32:18 -0000 1.18
+++ dlg_wingui.c 7 Jun 2007 14:43:51 -0000
@@ -566,6 +566,7 @@
CheckDlgButton(hdlg, DS_UPDATABLECURSORS, ci->allow_keyset);
CheckDlgButton(hdlg, DS_SERVERSIDEPREPARE, ci->use_server_side_prepare);
CheckDlgButton(hdlg, DS_BYTEAASLONGVARBINARY, ci->bytea_as_longvarbinary);
+ CheckDlgButton(hdlg, DS_LISTUSERTABLESONLY, ci->list_usertables_only);
/*CheckDlgButton(hdlg, DS_LOWERCASEIDENTIFIER, ci->lower_case_identifier);*/

EnableWindow(GetDlgItem(hdlg, DS_FAKEOIDINDEX), atoi(ci->show_oid_column));
@@ -638,6 +639,7 @@
ci->allow_keyset = IsDlgButtonChecked(hdlg, DS_UPDATABLECURSORS);
ci->use_server_side_prepare = IsDlgButtonChecked(hdlg, DS_SERVERSIDEPREPARE);
ci->bytea_as_longvarbinary = IsDlgButtonChecked(hdlg, DS_BYTEAASLONGVARBINARY);
+ ci->list_usertables_only = IsDlgButtonChecked(hdlg, DS_LISTUSERTABLESONLY);
/*ci->lower_case_identifier = IsDlgButtonChecked(hdlg, DS_LOWERCASEIDENTIFIER);*/

/* OID Options */
Index: info.c
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.145
diff -u -r1.145 info.c
--- info.c 2 Jun 2007 05:32:18 -0000 1.145
+++ info.c 7 Jun 2007 12:38:04 -0000
@@ -1709,6 +1709,18 @@
}
}

+ /*
+ * Check to see if we are only interested in listing tables upon
+ * which the user has one of SELECT, INSERT, DELETE or UPDATE
+ * privileges
+ */
+ if (!list_some)
+ {
+ if (ci->list_usertables_only)
+ strcat(tables_query, " and (has_table_privilege(current_user, c.oid, 'select') or has_table_privilege(current_user, c.oid, 'insert') or"
+ " has_table_privilege(current_user, c.oid, 'delete') or has_table_privilege(current_user, c.oid, 'update'))");
+ }
+
if (list_schemas)
strcat(tables_query, " order by nspname");
else if (list_some)
Index: psqlodbc.rc
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/psqlodbc.rc,v
retrieving revision 1.87
diff -u -r1.87 psqlodbc.rc
--- psqlodbc.rc 23 Oct 2006 15:58:29 -0000 1.87
+++ psqlodbc.rc 4 Jun 2007 20:59:01 -0000
@@ -542,6 +542,8 @@
BS_AUTOCHECKBOX | WS_TABSTOP,163,71,90,10
CONTROL "bytea as LO",DS_BYTEAASLONGVARBINARY,"Button",
BS_AUTOCHECKBOX | WS_TABSTOP,16,84,87,10
+ CONTROL "Only list user-accessible tables",DS_LISTUSERTABLESONLY,"Button",
+ BS_AUTOCHECKBOX | WS_TABSTOP,163,84,147,10
GROUPBOX "Int8 As",IDC_STATIC,5,97,256,25
CONTROL "default",DS_INT8_AS_DEFAULT,"Button",BS_AUTORADIOBUTTON |
WS_GROUP,12,107,40,10
Index: resource.h
============================================================ =======
RCS file: /cvsroot/psqlodbc/psqlodbc/resource.h,v
retrieving revision 1.40
diff -u -r1.40 resource.h
--- resource.h 23 Oct 2006 15:58:29 -0000 1.40
+++ resource.h 4 Jun 2007 20:58:51 -0000
@@ -94,6 +94,7 @@
#define DRV_DTCLOG 1083
#define DS_EXTRA_OPTIONS 1084
#define IDC_TEST 1085
+#define DS_LISTUSERTABLESONLY 1086

// Next default values for new objects
//
--=-PSByADNWJfNODkiNCpAv
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(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

--=-PSByADNWJfNODkiNCpAv--

Re: Proposal for new pgsqlODBC feature - hiding tables

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.p hp. 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.p hp. 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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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.p hp. 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

Re: Proposal for new pgsqlODBC feature - hiding

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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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

Re: Proposal for new pgsqlODBC feature -

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.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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

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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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

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

Re: Proposal for new pgsqlODBC feature

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.




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

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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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

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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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


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

Re: Proposal for new pgsqlODBC feature - hiding tables inaccessibleto the current user

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Proposal for new pgsqlODBC feature

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.




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

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