CODEWITHSUNDEEP
postgresql
smithfarm
smithfarm

Reputation: 333

PostgreSQL 9.3.5 numeric role name

I have a PostgreSQL (9.3.5) cluster with the following roles:

dochazka-test=> \du
                              List of roles
 Role name |                   Attributes                   | Member of  
-----------+------------------------------------------------+------------
 1         |                                                | {admin}
 2         |                                                | {passerby}
 active    | Cannot login                                   | {everyone}
 admin     | Cannot login                                   | {everyone}
 dochazka  |                                                | {admin}
 everyone  | Cannot login                                   | {}
 inactive  | Cannot login                                   | {everyone}
 passerby  | Cannot login                                   | {everyone}
 postgres  | Superuser, Create role, Create DB, Replication | {}

(The first two entries were created dynamically when new users were added to the application.)

As you can see, both roles "dochazka" and "1" are in the "admin" role, which is in the "everyone" role. All roles are set to INHERIT.

The "everyone" role has all privileges on all tables, functions, and sequences in schema "public".

As expected, I can run SELECTs on tables in the database when I connect as the user "dochazka". However, I cannot run the same SELECTs when I connect to the same database as the user "1":

$ psql -U 1 dochazka-test
Password for user 1: 
psql (9.3.5)
Type "help" for help.

dochazka-test=> select * from employees;
ERROR:  permission denied for relation employees

Could it be that PostgreSQL has a fundamental problem with numeric role names?

Upvotes: 0

Views: 690

Answers (2)

smithfarm
smithfarm

Reputation: 333

Sorry, everyone. This question was not adequately prepared.

The problem was that I was (stupidly) running the statement

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO everyone

before creating the tables!

As far as the numeric role names are concerned, neither psql nor the Perl DBI seem to have any issue with them.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

Identifiers and catalogs use the name type, which is kind of like a varchar but not quite. Some magic goes on to make names case sensitive unless they're quoted as identifiers, and there are some constraints to make it easier to parse SQL strings, such as limitations on the first character (e.g. not a number) when not quoted.

This isn't to say you cannot use identifiers with uppercase or that start with a number. Rather, you must quote the offending identifiers when you do: select * from "0_Foo".

My guess is that you're either missing the double quotes, or the client you're using is not quoting identifiers properly. (I've just tested, and it works fine with psql.)

Whichever problem it is, though, I'd advise against using such identifiers even if it's supported on paper, and would suggest using more talkative role names such as server_1.

Edit: alternatively, you might have not configured your permissions properly.

Upvotes: 2

Related Questions