Обсуждение: Case sensitive grant statements in pg14?
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.
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
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;
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
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
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?
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
> 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
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;
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;
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,SarwarFrom: 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
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?
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;
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;
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,SarwarFrom: 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
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
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
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
@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;
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
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
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
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