Re: [ Bug Report ]Re: column_query buffer in PGAPI ColumnPrivileges

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 <foxi_yiyi12081003(at)outlook(dot)com>:

> 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,
> 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);
> + appendPQExpBuffer(&column_query, " and table_schem*a*
> %s'%s'", eq_string, escSchemaName);
> if (escTableName)
> appendPQExpBuffer(&column_query, " and table_name %s'%s'",
> eq_string, escTableName);
> if (escColumnName)
> (END)
>
> Zhanyi Jiang
> foxi_yiyi12081003(at)outlook(dot)com
>
> <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>
> ---- Replied Message ----
> From Wal, Jan Tjalling van der<jan_tjalling(dot)vanderwal(at)wur(dot)nl>
> <jan_tjalling(dot)vanderwal(at)wur(dot)nl>
> Date 11/10/2022 17:56
> To Chiang Chan-i<foxi_yiyi12081003(at)outlook(dot)com> ,
> <foxi_yiyi12081003(at)outlook(dot)com> pgsql-odbc(at)postgresql(dot)org<pgsql-odbc(at)postgresql(dot)org>
> <pgsql-odbc(at)postgresql(dot)org>
> Subject RE: column_query buffer in PGAPI ColumnPrivileges
>
> 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_schem*A * 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 <foxi_yiyi12081003(at)outlook(dot)com>
> *Sent:* 10 November 2022 02:37
> *To:* Wal, Jan Tjalling van der <jan_tjalling(dot)vanderwal(at)wur(dot)nl>;
> pgsql-odbc(at)postgresql(dot)org
> *Subject:* RE: column_query buffer in PGAPI ColumnPrivileges
>
>
>
> 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://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 <jan_tjalling(dot)vanderwal(at)wur(dot)nl>
> *Sent: *2022年11月10日 5:45
> *To: *foxi_yiyi12081003 <foxi_yiyi12081003(at)outlook(dot)com>;
> pgsql-odbc(at)postgresql(dot)org
> *Subject: *RE: column_query buffer in PGAPI ColumnPrivileges
>
>
>
> 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 <foxi_yiyi12081003(at)outlook(dot)com>
> *Sent:* 09 November 2022 04:18
> *To:* pgsql-odbc(at)postgresql(dot)org
> *Subject:* column_query buffer in PGAPI ColumnPrivileges
>
>
>
> 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。
>
>
>
>
>