Обсуждение: Re: [ADMIN] Cannot connect to the database (PG 7.3)

I wrote:
> Michiel Lange <michiel@minas.demon.nl> writes:
>> It is, somehow, not possible to connect as a user which name is completely 
>> numeric.

> I muttered "nonsense!" to myself, but darned if you're not right:

> regression=# create user "12345";
> CREATE USER
> regression=# \q
> $ psql -U 12345 regression
> psql: FATAL:  SET SESSION AUTHORIZATION: permission denied

> Will look into it.

After some looking, it appears the culprit is
assign_session_authorization() in commands/variable.c, which is assuming
that a numeric-looking parameter string should be taken as a numeric
user sysid, rather than an actual user name.

The reason this was done was to avoid the need to do catalog lookups
when restoring a prior setting during error recovery.  That's still a
valid concern, so right offhand I don't see an easy fix.  Any ideas?
        regards, tom lane

On Tue, 28 Jan 2003, Tom Lane wrote:

> I wrote:
> > Michiel Lange <michiel@minas.demon.nl> writes:
> >> It is, somehow, not possible to connect as a user which name is completely 
> >> numeric.
> 
> > I muttered "nonsense!" to myself, but darned if you're not right:
> 
> > regression=# create user "12345";
> > CREATE USER
> > regression=# \q
> > $ psql -U 12345 regression
> > psql: FATAL:  SET SESSION AUTHORIZATION: permission denied
> 
> > Will look into it.
> 
> After some looking, it appears the culprit is
> assign_session_authorization() in commands/variable.c, which is assuming
> that a numeric-looking parameter string should be taken as a numeric
> user sysid, rather than an actual user name.
> 
> The reason this was done was to avoid the need to do catalog lookups
> when restoring a prior setting during error recovery.  That's still a
> valid concern, so right offhand I don't see an easy fix.  Any ideas?

How about throwing an error if an all digit user name is given to create
user as already alluded to?

Seems that would be simple, not that I know anything about the parser, but does
that break any standards?


-- 
Nigel J. Andrews

>> After some looking, it appears the culprit is
>> assign_session_authorization() in commands/variable.c, which is assuming
>> that a numeric-looking parameter string should be taken as a numeric
>> user sysid, rather than an actual user name.
>> 
>> The reason this was done was to avoid the need to do catalog lookups
>> when restoring a prior setting during error recovery.  That's still a
>> valid concern, so right offhand I don't see an easy fix.  Any ideas?

I've got an idea ... it's a bit grotty, but certainly not as ugly as
prohibiting all-numeric user names.

The problem for assign_session_authorization is to store a numeric sysid
in a form that can't be mistaken for a user name.  There is no string
that can't be generated by a quoted identifier (except for strings with
embedded nulls, which won't really help us here).  However, there *is*
the NAMEDATALEN limit.  What if we generate strings consisting of, say,
NAMEDATALEN+1 'x's and then the numeric sysid?

This might seem a tad wasteful of storage, but at most a couple of such
strings need be stored at one time, so it's really insignificant (the
code space to implement any more-complex solution would probably be
more).

If anyone has a cleaner solution, let's hear it; otherwise I'll put this
in.
        regards, tom lane

On Wednesday 29 Jan 2003 3:34 am, you wrote:
> I wrote:
> The reason this was done was to avoid the need to do catalog lookups
> when restoring a prior setting during error recovery.  That's still a
> valid concern, so right offhand I don't see an easy fix.  Any ideas?

Document it as a bug and recommend users that do not create a numerical only 
user name.

Agreed it is not a fix but a workaround. But it should be acceptable to most 
of users, isn't it?
Shridhar