Subject: column_query buffer in PGAPI ColumnPrivileges

From: foxi_yiyi12081003 <[email-masked]>
Date: Wed, 9 Nov 2022 11:18:10 +0800

Hi,

Is that a bug in psqlodbc-13.02.0000 release version ?

file: info.c : 3734 ?

the SQL Command in the second appendPQExpBuffers

【 and table_schem %s'%s' 】table_schem or table_schema ??

code:

appendPQExpBufferStr(&column_query, "select '' as TABLE_CAT, table_schema as TABLE_SCHEM,"

" table_name, column_name, grantor, grantee,"

" privilege_type as PRIVILEGE, is_grantable from"

" information_schema.column_privileges where true");

op_string = gen_opestr(like_or_eq, conn);

eq_string = gen_opestr(eqop, conn);

if (escSchemaName)

appendPQExpBuffer(&column_query, " and table_schem %s'%s'", eq_string, escSchemaName);

if (escTableName)

appendPQExpBuffer(&column_query, " and table_name %s'%s'", eq_string, escTableName);

if (escColumnName)

appendPQExpBuffer(&column_query, " and column_name %s'%s'", op_string, escColumnName);

if (PQExpBufferDataBroken(column_query))

and I also found the same condition in master branch。

---

From: "Wal, Jan Tjalling van der" <[email-masked]>
Date: Wed, 9 Nov 2022 21:45:16 +0000

Dear foxi_yiyi12081003,

I’m not an expert on the inner workings of this specific driver, but in my opinion this is probably correct.
The query that is defined inside appendPQExpBufferStr, asks for data from column table_schema to be returned using a different name: TABLE_SCHEM.
When the results of that query are used further down, the correct name to use them will be that new name.

When the query is run against an running instance of a postgres-database it gives results (over 9000), here limited to just 5.
select '' as TABLE_CAT, table_schema as TABLE_SCHEM,
          table_name, column_name, grantor, grantee,
          privilege_type as PRIVILEGE, is_grantable from
          information_schema.column_privileges where true limit 5;
"table_cat"
"table_schem"
"table_name"
"column_name"
"grantor"
"grantee"
"privilege"
"is_grantable"

"information_schema"
"routines"
"scope_schema"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"UPDATE"
"YES"

"information_schema"
"routines"
"dtd_identifier"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"INSERT"
"YES"

"pg_catalog"
"pg_stat_progress_vacuum"
"datid"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"YES"

"information_schema"
"role_udt_grants"
"grantor"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"NO"

"pg_catalog"
"pg_namespace"
"nspname"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"YES"

Kind regards JT

From: foxi_yiyi12081003 <[email-masked]>
Sent: 09 November 2022 04:18
To: [email-masked]
Subject: column_query buffer in PGAPI ColumnPrivileges

Hi,

code:

---

From: Chiang Chan-i <[email-masked]>
Date: Thu, 10 Nov 2022 01:36:44 +0000

Dear Kind regards JT,

Thanks for your help and patience.
The error of SQL Command I mentioned is here：

3733     if (escSchemaName)                                                  ↓↓--  table_schema
3734         appendPQExpBuffer(&column_query, " and table_schem %s'%s'", eq_string, escSchemaName);

jiang=# select '' as TABLE_CAT, table_schema as TABLE_SCHEM,
                table_name, column_name, grantor, grantee,
                privilege_type as PRIVILEGE, is_grantable from
                information_schema.column_privileges where true
                and table_schem = 'public'          ---->>>>       Maybe table_schem[a]？
                and table_name ='test'
                and column_name = 'a';
ERROR:  column "table_schem" does not exist
LINE 5:                 and table_schem = 'public'
                            ^
HINT:  Perhaps you meant to reference the column "column_privileges.table_schema".
jiang=#

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows

From: Wal, Jan Tjalling van der<mailto:[email-masked]>
Sent: 2022年11月10日 5:45
To: foxi_yiyi12081003<mailto:[email-masked]>; [email-masked]<mailto:[email-masked]>
Subject: RE: column_query buffer in PGAPI ColumnPrivileges

Dear foxi_yiyi12081003,

"information_schema"
"routines"
"scope_schema"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"UPDATE"
"YES"

"information_schema"
"routines"
"dtd_identifier"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"INSERT"
"YES"

"pg_catalog"

"information_schema"
"role_udt_grants"
"grantor"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"NO"

"pg_catalog"
"pg_namespace"
"nspname"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"YES"

Kind regards JT

Hi,

code:

---

From: "Wal, Jan Tjalling van der" <[email-masked]>
Date: Thu, 10 Nov 2022 09:56:09 +0000

Dear Jiang,

Yes, you are right. I stand corrected.

With that clause added as per line 3743, the query returns an error.
When that missing ‘a’ is added, so table_schemA  it yields results.

Now we’ll have to wait and see if this get noticed and fixed.
Or you could consider making the change and doing a pull request.
So that your find and fix get pulled into the code base?

Kind regards, Jan Tjalling

From: Chiang Chan-i <[email-masked]>
Sent: 10 November 2022 02:37
To: Wal, Jan Tjalling van der <[email-masked]>; [email-masked]
Subject: RE: column_query buffer in PGAPI ColumnPrivileges

Dear Kind regards JT,

Sent from Mail<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=05%7C01%7Cjan_tjalling.vanderwal%40wur.nl%7Caa970d19afc647708e7508dac2bc095f%7C27d137e5761f4dc1af88d26430abb18f%7C0%7C0%7C638036410113327794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=RNVXP6BuybEVW8Xs6sSyyU8PoSlthmTUHstVJHRMejg%3D&reserved=0> for Windows

Dear foxi_yiyi12081003,

"information_schema"
"routines"
"scope_schema"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"UPDATE"
"YES"

"information_schema"
"routines"
"dtd_identifier"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"INSERT"
"YES"

"pg_catalog"

"information_schema"
"role_udt_grants"
"grantor"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"NO"

"pg_catalog"
"pg_namespace"
"nspname"
"xxxxxxxxxxxxx"
"xxxxxxxxxxxxx"
"SELECT"
"YES"

Kind regards JT

From: foxi_yiyi12081003 <[email-masked]<mailto:[email-masked]>>
Sent: 09 November 2022 04:18
To: [email-masked]<mailto:[email-masked]>
Subject: column_query buffer in PGAPI ColumnPrivileges

Hi,

code:

---

From: foxi_yiyi12081003 <[email-masked]>
Date: Thu, 10 Nov 2022 18:20:01 +0800

Hi, All:

diff --git a/info.c b/info.c
index 8563a89..5ce531a 100644
--- a/info.c
+++ b/info.c
@@ -3731,7 +3731,7 @@ PGAPI_ColumnPrivileges(HSTMT hstmt,

if (escSchemaName)
-               appendPQExpBuffer(&column_query, " and table_schem %s'%s'", eq_string, escSchemaName);
+               appendPQExpBuffer(&column_query, " and table_schema %s'%s'", eq_string, escSchemaName);

if (escTableName)

if (escColumnName)
(END)

Zhanyi Jiang

[email-masked]

---- Replied Message ----

From

Wal, Jan Tjalling van der<[email-masked]>

Date

11/10/2022 17:56

To

Chiang Chan-i<[email-masked]>
,

[email-masked]<[email-masked]>

Subject

RE: column_query buffer in PGAPI ColumnPrivileges

Dear Jiang,

When that missing ‘a’ is added, so table_schemA
it yields results.

Dear
Kind regards JT,

3733     if (escSchemaName)                                                  ↓↓--
table_schema

and
table_schem = 'public'          ---->>>>       Maybe table_schem[a]？

and table_name ='test'

and column_name = 'a';

^

jiang=#

Sent from

Mail for Windows

From:
Wal, Jan Tjalling van der

Sent: 2022年11月10日
5:45

To: foxi_yiyi12081003;
[email-masked]

Dear foxi_yiyi12081003,

The query that is defined inside appendPQExpBufferStr, asks for data from column table_schema to be returned using a different name:
TABLE_SCHEM.

"table_cat"

"table_schem"

"table_name"

"column_name"

"grantor"

"grantee"

"privilege"

"is_grantable"

"information_schema"

"routines"

"scope_schema"

"xxxxxxxxxxxxx"

"xxxxxxxxxxxxx"

"UPDATE"

"YES"

"information_schema"

"routines"

"dtd_identifier"

"xxxxxxxxxxxxx"

"xxxxxxxxxxxxx"

"INSERT"

"YES"

"pg_catalog"

"datid"

"xxxxxxxxxxxxx"

"xxxxxxxxxxxxx"

"SELECT"

"YES"

"information_schema"

"role_udt_grants"

"grantor"

"xxxxxxxxxxxxx"

"xxxxxxxxxxxxx"

"SELECT"

"NO"

"pg_catalog"

"pg_namespace"

"nspname"

"xxxxxxxxxxxxx"

"xxxxxxxxxxxxx"

"SELECT"

"YES"

Kind regards JT

Hi,

file: info.c : 3734 ?

【
and table_schem %s'%s' 】table_schem or table_schema ??

code:

if (escSchemaName)

if (escTableName)

if (escColumnName)

---

From: "Inoue,Hiroshi" <[email-masked]>
Date: Thu, 1 Dec 2022 14:59:42 +0900

Hi foxi_yiyi12081003,

Thanks for the report and the patch.
I would commit the patch after enabling SQLColumnPrivileges functionality.

regards,
Hiroshi Inoue

2022年11月10日(木) 20:14 foxi_yiyi12081003 <[email-masked]>:

> +               appendPQExpBuffer(&column_query, " and table_schem*a*
> %s'%s'", eq_string, escSchemaName);
>        if (escTableName)
> <https://maas.mail.163.com/dashi-web-extend/html/proSignature.html?ftlId=1&name=foxi_yiyi12081003&uid=foxi_yiyi12081003%40outlook.com&iconUrl=https%3A%2F%2Fmail-online.nosdn.127.net%2Fsmfe9208f0436281c93c638c7e4abebbad.jpg&items=%5B%22foxi_yiyi12081003%40outlook.com%22%5D>
> From Wal, Jan Tjalling van der<[email-masked]>
> <[email-masked]>
> Date 11/10/2022 17:56
> To Chiang Chan-i<[email-masked]> ,
> <[email-masked]> [email-masked]<[email-masked]>
> <[email-masked]>
> Subject RE: column_query buffer in PGAPI ColumnPrivileges
>
> Dear Jiang,
>
>
>
> When that missing ‘a’ is added, so table_schem*A * it yields results.
>
>
>
> *From:* Chiang Chan-i <[email-masked]>
> *Sent:* 10 November 2022 02:37
> *To:* Wal, Jan Tjalling van der <[email-masked]>;
> *Subject:* RE: column_query buffer in PGAPI ColumnPrivileges
>
>
>
> Dear Kind regards JT,
>
>
>
> Sent from Mail
> <https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=05%7C01%7Cjan_tjalling.vanderwal%40wur.nl%7Caa970d19afc647708e7508dac2bc095f%7C27d137e5761f4dc1af88d26430abb18f%7C0%7C0%7C638036410113327794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=RNVXP6BuybEVW8Xs6sSyyU8PoSlthmTUHstVJHRMejg%3D&reserved=0>
> for Windows
>
>
>
> *From: *Wal, Jan Tjalling van der <[email-masked]>
> *Sent: *2022年11月10日 5:45
> *To: *foxi_yiyi12081003 <[email-masked]>;
> *Subject: *RE: column_query buffer in PGAPI ColumnPrivileges
>
>
>
> Dear foxi_yiyi12081003,
>
>
>
> The query that is defined inside appendPQExpBufferStr, asks for data from
> column table_schema to be returned using a different name: TABLE_SCHEM.
>
> When the query is run against an running instance of a postgres-database
> it gives results (over 9000), here limited to just 5.
>
> *"table_cat"*
> *"table_schem"*
> *"table_name"*
> *"column_name"*
> *"grantor"*
> *"grantee"*
> *"privilege"*
> *"is_grantable"*
> *From:* foxi_yiyi12081003 <[email-masked]>
> *Sent:* 09 November 2022 04:18
> *To:* [email-masked]
> *Subject:* column_query buffer in PGAPI ColumnPrivileges
>
>
>
> Hi,
>