Thread: Numeric user names

I am unable to use the "CREATE USER" command with numeric user names
(i.e. CREATE USER 35236 WITH PASSWORD '1234';).  Is this a limitation or
a problem somewhere with how I have things configured?  Is there are
workaround?

Any help would be greatly appreciated.

------------------------------
Ed Stoner
Network Administrator
Woodland Hills School District
Pittsburgh, PA 15221

On Wed, 2004-10-13 at 06:31, Ed Stoner wrote:
> I am unable to use the "CREATE USER" command with numeric user names
> (i.e. CREATE USER 35236 WITH PASSWORD '1234';).  Is this a limitation or
> a problem somewhere with how I have things configured?  Is there are
> workaround?

A username is an identifier; per the docs, "SQL identifiers and key
words must begin with a letter (a-z, but also letters with diacritical
marks and non-Latin letters) or an underscore (_). Subsequent characters
in an identifier or key word can be letters, underscores, digits (0-9),
or dollar signs ($)." So it's a limitation.

I don't know of an easy workaround. Why do you need numeric usernames?

-Neil

On Tue, 12 Oct 2004, Ed Stoner wrote:

> I am unable to use the "CREATE USER" command with numeric user names
> (i.e. CREATE USER 35236 WITH PASSWORD '1234';).  Is this a limitation or
> a problem somewhere with how I have things configured?  Is there are
> workaround?

I believe you can create a user with a quoted identifier that is all
numbers (ie CREATE USER "35236") but then you have to quote the username
for sql statements like grant and revoke as well.

Neil Conway <neilc@samurai.com> writes:
> I don't know of an easy workaround. Why do you need numeric usernames?

There's always double-quoted identifiers:
    create user "12345" with password ...

Considering that the SQL standard defines <authorization identifier>
as an <identifier>, I'm not sure why Ed is expecting that he should
be able to use a bare number as a user name.

            regards, tom lane

I want to use bare numbers because that is how the users (students in
this case) are identified on the network and in the student information
system.  They've been identified this way for over 20 years, so it would
be near impossible to change at this point (although it is not always
very convenient :-).  I'm trying (and almost finished now) to have the
postgres server be the source of all user account information on the
network (windows and linux).

-Ed

Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
>
>>I don't know of an easy workaround. Why do you need numeric usernames?
>
>
> There's always double-quoted identifiers:
>     create user "12345" with password ...
>
> Considering that the SQL standard defines <authorization identifier>
> as an <identifier>, I'm not sure why Ed is expecting that he should
> be able to use a bare number as a user name.
>
>             regards, tom lane

Thanks.  This worked.  This is exactly what I was looking for.

Stephan Szabo wrote:
> On Tue, 12 Oct 2004, Ed Stoner wrote:
>
>
>>I am unable to use the "CREATE USER" command with numeric user names
>>(i.e. CREATE USER 35236 WITH PASSWORD '1234';).  Is this a limitation or
>>a problem somewhere with how I have things configured?  Is there are
>>workaround?
>
>
> I believe you can create a user with a quoted identifier that is all
> numbers (ie CREATE USER "35236") but then you have to quote the username
> for sql statements like grant and revoke as well.

At 09:25 AM 10/19/2004 -0400, Ed Stoner wrote:

>I want to use bare numbers because that is how the users (students in this
>case) are identified on the network and in the student information
>system.  They've been identified this way for over 20 years, so it would
>be near impossible to change at this point (although it is not always very
>convenient :-).  I'm trying (and almost finished now) to have the postgres
>server be the source of all user account information on the network
>(windows and linux).

Just curious - but it doesn't seem obvious why you need all postgresql
users == all users in the student information system?

Any reasons why?

Assuming the student information system is an application, I'd have created
a table and each basic user account info would be in its own row in that
table, and link rows from other tables to those rows as necessary.

Or maybe used something like LDAP (and add the necessary glue :( ).

Of course if ALL students need to directly use the same postgresql database
with their own individual accounts then that's probably a good reason.

Regards,

Link.