Обсуждение: Valid role name (PostgreSQL 9.0.4)

In standard postgres/main/5432 cluster I created new role (from
postgres account):

createuser -SRD user
psql -c "\du"
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}
 user      |                                   | {}

Then I run:

psql -c "ALTER ROLE user CREATEDB"
ERROR:  syntax error at or near "user"
LINE 1: ALTER ROLE user CREATEDB
                  ^
After some searching I found that 'user' is reserved PostgreSQL keyword
(http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html),
but I think that PostgreSQL should deny my request to create role with
reserved keyword. What do you think ? I mean something like:

createuser -SRD user
ERROR: illegal name

Thanks,
Grzegorz Sz.

On Thu, Apr 7, 2011 at 11:21 PM, Grzegorz Szpetkowski
<gszpetkowski@gmail.com> wrote:
> Then I run:
>
> psql -c "ALTER ROLE user CREATEDB"
> ERROR:  syntax error at or near "user"
> LINE 1: ALTER ROLE user CREATEDB
>                  ^

If you quote the "user" username like so:

ALTER ROLE "user" CREATEDB;

the command should work as you expected it to.

Josh

You are right. I found in documentation:

"As a general rule, if you get spurious parser errors for commands
that contain any of the listed key words as an identifier you should
try to quote the identifier to see if the problem goes away."

psql -c ALTER ROLE "user" CREATEDB'

On the other hand there is also:

"According to the standard, reserved key words are the only real key
words; they are never allowed as identifiers."

Since USER is reserved PostgreSQL keywords should I generally avoid
such names ? I found that I can even create (distinct) "USER" role:

createuser -SdR USER
psql -c "\du"
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 USER      | Create DB                         | {}
 postgres  | Superuser, Create role, Create DB | {}
 user      | Create DB                         | {}

Regards,
Grzegorz Sz.

2011/4/8 Josh Kupershmidt <schmiddy@gmail.com>:
> On Thu, Apr 7, 2011 at 11:21 PM, Grzegorz Szpetkowski
> <gszpetkowski@gmail.com> wrote:
>> Then I run:
>>
>> psql -c "ALTER ROLE user CREATEDB"
>> ERROR:  syntax error at or near "user"
>> LINE 1: ALTER ROLE user CREATEDB
>>                  ^
>
> If you quote the "user" username like so:
>
> ALTER ROLE "user" CREATEDB;
>
> the command should work as you expected it to.
>
> Josh
>

On Fri, Apr 8, 2011 at 10:11 AM, Grzegorz Szpetkowski
<gszpetkowski@gmail.com> wrote:
> Since USER is reserved PostgreSQL keywords should I generally avoid
> such names ?

I would avoid creating user names and other identifiers (names of
functions, schemas, databases, tables, etc.) which collide with
reserved words.

> I found that I can even create (distinct) "USER" role:
>
> createuser -SdR USER
> psql -c "\du"
>                       List of roles
>  Role name |            Attributes             | Member of
> -----------+-----------------------------------+-----------
>  USER      | Create DB                         | {}
>  postgres  | Superuser, Create role, Create DB | {}
>  user      | Create DB                         | {}

Yup; from:
<http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS>
  "Quoting an identifier also makes it case-sensitive, whereas
unquoted names are always folded to lower case."

Josh