Обсуждение: Case sensitive grant statements in pg14?

Поиск
Список
Период
Сортировка

Case sensitive grant statements in pg14?

От
richard coleman
Дата:
Hello all, 

Just ran across a really strange thing in a PostgreSQL 14 database.  Apparently grant statements are now somehow case sensitive.

ex:
grant create, usage on schema {schema_name} to {role};

fails with the error:
ERROR: syntax error at or near "{role}"

but by changing the capitalization of the statement to:
GRANT CREATE, USAGE ON SCHEMA {schema_name} TO {role};

completes successfully.

Is this something new?

Thanks,
rik.

Re: Case sensitive grant statements in pg14?

От
Tom Lane
Дата:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> grant create, usage on schema {schema_name} to {role};

> fails with the error:
> ERROR: syntax error at or near "{role}"

> but by changing the capitalization of the statement to:
> GRANT CREATE, USAGE ON SCHEMA {schema_name} TO {role};

> completes successfully.

What are you expecting will replace "{role}" with something
valid?  Whatever that is is apparently somehow dependent
on the context being upper-case.

            regards, tom lane



Re: Case sensitive grant statements in pg14?

От
richard coleman
Дата:
Tom, 

The items in { } are just placeholders for actual values.  If the schema name was my_schema and the role name was bob, I could have written instead:
grant create, usage on schema my_schema to bob;

but I had thought that using {schema_name} and {role} as place holders for any valid schema and any valid role would be more concise.
It appears that I was mistaken.

I am having instances of (substituting example names for schema and role):
 grant create, usage on schema my_schema to bob;

returning the error message:
ERROR: syntax error at or near "bob"

but:
GRANT CREATE, USAGE ON SCHEMA my_schema TO bob;

succeeding.

I hope that's clearer.

rik.


On Thu, Dec 21, 2023 at 1:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> grant create, usage on schema {schema_name} to {role};

> fails with the error:
> ERROR: syntax error at or near "{role}"

> but by changing the capitalization of the statement to:
> GRANT CREATE, USAGE ON SCHEMA {schema_name} TO {role};

> completes successfully.

What are you expecting will replace "{role}" with something
valid?  Whatever that is is apparently somehow dependent
on the context being upper-case.

                        regards, tom lane

Re: Case sensitive grant statements in pg14?

От
Tom Lane
Дата:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> I am having instances of (substituting example names for schema and role):
>  grant create, usage on schema my_schema to bob;

> returning the error message:
> ERROR: syntax error at or near "bob"

> but:
> GRANT CREATE, USAGE ON SCHEMA my_schema TO bob;

> succeeding.

> I hope that's clearer.

Well, it's clearer, but I don't believe it.  What I think is
far more likely an explanation is that you've got some weird
non-ASCII whitespace in the first version of the command.
Copying-and-pasting from your mail doesn't show anything
strange though.

            regards, tom lane



Re: Case sensitive grant statements in pg14?

От
M Sarwar
Дата:
Richard Coleman,
Based on Tom's email, here is my advise to you.

Prepare all your commands and copy to a notepad.
Now copy from the notepad  and execute the commands and let us see if you get the same errors like in your first email.

Thanks,
Sarwar


From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, December 21, 2023 2:26 PM
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Case sensitive grant statements in pg14?
 
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> I am having instances of (substituting example names for schema and role):
>  grant create, usage on schema my_schema to bob;

> returning the error message:
> ERROR: syntax error at or near "bob"

> but:
> GRANT CREATE, USAGE ON SCHEMA my_schema TO bob;

> succeeding.

> I hope that's clearer.

Well, it's clearer, but I don't believe it.  What I think is
far more likely an explanation is that you've got some weird
non-ASCII whitespace in the first version of the command.
Copying-and-pasting from your mail doesn't show anything
strange though.

                        regards, tom lane


Re: Case sensitive grant statements in pg14?

От
richard coleman
Дата:
Thanks, 

But there is no Copy/Pasting involved.

if I open a psql prompt on my Ubuntu server.
type:
grant create on schema my_schema to bob;

by hand and press return, it replies with the error messages about a syntax error at "bob"

if I do the same with:
GRANT CREATE ON SCHEMA my_schema TO bob;

by hand and press return, it returns with "GRANT"

¯\_(ツ)_/¯

is seems very strange.

I don't know if that's the actual underlying error, or if throwing the "syntax error at bob"  is indicative of some other error.
I've never run across it before so I thought I would ask.

Thanks again, 
rik.



On Thu, Dec 21, 2023 at 2:34 PM M Sarwar <sarwarmd02@outlook.com> wrote:
Richard Coleman,
Based on Tom's email, here is my advise to you.

Prepare all your commands and copy to a notepad.
Now copy from the notepad  and execute the commands and let us see if you get the same errors like in your first email.

Thanks,
Sarwar


From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, December 21, 2023 2:26 PM
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Case sensitive grant statements in pg14?
 
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> I am having instances of (substituting example names for schema and role):
>  grant create, usage on schema my_schema to bob;

> returning the error message:
> ERROR: syntax error at or near "bob"

> but:
> GRANT CREATE, USAGE ON SCHEMA my_schema TO bob;

> succeeding.

> I hope that's clearer.

Well, it's clearer, but I don't believe it.  What I think is
far more likely an explanation is that you've got some weird
non-ASCII whitespace in the first version of the command.
Copying-and-pasting from your mail doesn't show anything
strange though.

                        regards, tom lane


Re: Case sensitive grant statements in pg14?

От
M Sarwar
Дата:
That does not look like a database issue but something is getting manipulated.
Did you try with different key boards and using different machines?
It is a kind of weird - Sorry.
Sarwar


From: richard coleman <rcoleman.ascentgl@gmail.com>
Sent: Thursday, December 21, 2023 2:51 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Case sensitive grant statements in pg14?
 
Thanks, 

But there is no Copy/Pasting involved.

if I open a psql prompt on my Ubuntu server.
type:
grant create on schema my_schema to bob;

by hand and press return, it replies with the error messages about a syntax error at "bob"

if I do the same with:
GRANT CREATE ON SCHEMA my_schema TO bob;

by hand and press return, it returns with "GRANT"

¯\_(ツ)_/¯

is seems very strange.

I don't know if that's the actual underlying error, or if throwing the "syntax error at bob"  is indicative of some other error.
I've never run across it before so I thought I would ask.

Thanks again, 
rik.



On Thu, Dec 21, 2023 at 2:34 PM M Sarwar <sarwarmd02@outlook.com> wrote:
Richard Coleman,
Based on Tom's email, here is my advise to you.

Prepare all your commands and copy to a notepad.
Now copy from the notepad  and execute the commands and let us see if you get the same errors like in your first email.

Thanks,
Sarwar


From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, December 21, 2023 2:26 PM
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Case sensitive grant statements in pg14?
 
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> I am having instances of (substituting example names for schema and role):
>  grant create, usage on schema my_schema to bob;

> returning the error message:
> ERROR: syntax error at or near "bob"

> but:
> GRANT CREATE, USAGE ON SCHEMA my_schema TO bob;

> succeeding.

> I hope that's clearer.

Well, it's clearer, but I don't believe it.  What I think is
far more likely an explanation is that you've got some weird
non-ASCII whitespace in the first version of the command.
Copying-and-pasting from your mail doesn't show anything
strange though.

                        regards, tom lane


Re: Case sensitive grant statements in pg14?

От
Tom Lane
Дата:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> But there is no Copy/Pasting involved.

> if I open a psql prompt on my Ubuntu server.
> type:
> grant create on schema my_schema to bob;

> by hand and press return, it replies with the error messages about a syntax
> error at "bob"

All I can say is it works for me, and I can't tell from here
what's different on your end.

            regards, tom lane



Case sensitive grant statements in pg14?

От
"Wetmore, Matthew (CTR)"
Дата:
PG 13.11 checking in here.

psql=> grant create on schema my_schema to mwetmore;
GRANT
psql=> grant create, usage on schema my_schema to mwetmore;
GRANT

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, December 21, 2023 12:08 PM
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: M Sarwar <sarwarmd02@outlook.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Case sensitive grant statements in pg14?

richard coleman <rcoleman.ascentgl@gmail.com> writes:
> But there is no Copy/Pasting involved.

> if I open a psql prompt on my Ubuntu server.
> type:
> grant create on schema my_schema to bob;

> by hand and press return, it replies with the error messages about a
> syntax error at "bob"

All I can say is it works for me, and I can't tell from here what's different on your end.

            regards, tom lane





Re: Case sensitive grant statements in pg14?

От
Joe Conway
Дата:
On 12/21/23 15:08, Tom Lane wrote:
> richard coleman <rcoleman.ascentgl@gmail.com> writes:
>> But there is no Copy/Pasting involved.
> 
>> if I open a psql prompt on my Ubuntu server.
>> type:
>> grant create on schema my_schema to bob;
> 
>> by hand and press return, it replies with the error messages about a syntax
>> error at "bob"
> 
> All I can say is it works for me, and I can't tell from here
> what's different on your end.

I forget the specifics, but I have seen issues before if the terminal 
encoding and client encoding don't match. Could it be something like that?

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com




Re: Case sensitive grant statements in pg14?

От
kaido vaikla
Дата:
@richard coleman
Maybe you have some disturbing env parameter set,  you can try this:
#env -i /usr/pgsql-14/bin/psql
postgres=#grant create, usage on schema my_schema to bob;


If this work, check an env(1) output.
br
Kaido

On Thu, 21 Dec 2023 at 22:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> But there is no Copy/Pasting involved.

> if I open a psql prompt on my Ubuntu server.
> type:
> grant create on schema my_schema to bob;

> by hand and press return, it replies with the error messages about a syntax
> error at "bob"

All I can say is it works for me, and I can't tell from here
what's different on your end.

                        regards, tom lane


Re: Case sensitive grant statements in pg14?

От
Tom Lane
Дата:
Joe Conway <mail@joeconway.com> writes:
> I forget the specifics, but I have seen issues before if the terminal 
> encoding and client encoding don't match. Could it be something like that?

Theoretically, if the example is all-ASCII, that shouldn't matter.
But for sure there is something weird happening.

One idea for investigation is to set log_statement = 'all' and
then see how the troublesome command shows up in the postmaster log.

            regards, tom lane



Re: Case sensitive grant statements in pg14?

От
richard coleman
Дата:
Tom,
Thanks for the suggestion.  As mentioned previously restarting the postgresql service seems to have fixed the issue.  Which would tend to rule out terminal issues.
If it happens again, I'll try the "log_statement = 'all' " setting and see if anything untold revels itself.

Until the next bought of weirdness...
rik.

On Thu, Dec 21, 2023 at 3:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> writes:
> I forget the specifics, but I have seen issues before if the terminal
> encoding and client encoding don't match. Could it be something like that?

Theoretically, if the example is all-ASCII, that shouldn't matter.
But for sure there is something weird happening.

One idea for investigation is to set log_statement = 'all' and
then see how the troublesome command shows up in the postmaster log.

                        regards, tom lane