Hello people,

I have this Postgres 7.3 database, and could not find if this is a known 
issue, but:
I can connect to the database with my postgres user to a remote database. 
This as supposed with
psql -d mydb -h 123.456.789.001 -U postgres
it goes fine... postgres user exists on my system and the other system. 
Both versions of Postgres are the same (7.3)

However, I have another user, (whose name is a number) that does not exist 
on operating system level:
psql -d mydb -h 123.456.789.001 -U 20020003
or from within php:
pg_connect("host=123.456.789.001 dbname=mydb user=20020003 password=secret");

I get this error message:
psql: FATAL:  permission denied

while I do not get that message with postgres-user...
Anyone who knows where the problem lies?
I am thinking of:
- Users must exist both in the database and on the operating system. (which 
would be strange I think)
- Usernames may not exist solely out of numbers

If the mistake is because I have missed it in the documentation, I am 
sorry... I'll try to be more cautious next time...

Michiel



Mon, 27 Jan 2003 01:25:06 +0100
Michiel Lange <michiel@minas.demon.nl>
Cannot connect to the database (PG 7.3)
Michiel Lange <michiel@minas.demon.nl> writes:
> I get this error message:
> psql: FATAL:  permission denied

As near as I can tell, you must have some code that is trying to do
SET SESSION AUTHORIZATION --- which only a superuser is allowed to do.
Evidently user 20020003 isn't one.

			regards, tom lane

Sun, 26 Jan 2003 19:42:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cannot connect to the database (PG 7.3) 
it is at connect-time, so there is nothing done with set session authorization.
I just want to connect, and read data from a table to which the group the 
user is in has read-access.
I have tried if it would differ when I try to connect with a user created like:
CREATE USER 20020003 WITH ENCRYPTED PASSWORD 'secret';
or
CREATE USER 20020004 WITH UNENCRYPTED PASSWORD 'secred';
it would not matter... even if there really is a trust-relation between my 
computer and the other machine in pg_hba.conf
it is pretty strange... might it be a bug?

Thanks for the try though,
Michiel
At 19:42 26-1-2003 -0500, Tom Lane wrote:
>Michiel Lange <michiel@minas.demon.nl> writes:
> > I get this error message:
> > psql: FATAL:  permission denied
>
>As near as I can tell, you must have some code that is trying to do
>SET SESSION AUTHORIZATION --- which only a superuser is allowed to do.
>Evidently user 20020003 isn't one.
>
>                         regards, tom lane



Mon, 27 Jan 2003 14:19:36 +0100
Michiel Lange <michiel@minas.demon.nl>
Re: Cannot connect to the database (PG 7.3) 
Michiel Lange <michiel@minas.demon.nl> writes:
> it is pretty strange... might it be a bug?

I'm quite sure it's a bug in your code.  Try turning on query logging so
you can see what queries are being issued; maybe that will help solve
the problem.

			regards, tom lane

Mon, 27 Jan 2003 09:10:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cannot connect to the database (PG 7.3) 
but, but.... it is even with the psql command...
it will differ if I put a -U parameter with postgres or 20020003
postgres, michiel or most other names will connect fine, however the user 
20020003 will not...
I get that FATAL error... all users should be able to connect to the 
database, according to pg_hba.conf
and when I do: SELECT * FROM pg_user; I get all users, including 20020003 
returned... and 20030003 has userid 106, which is mentioned in the list 
SELECT * FROM pg_group; so, the account should be set up correctly. A ALTER 
USER 20020003 WITH ... has not helped either, but I am very certain the 
user exists... but is not allowed to connect to the database...

But I will try to see what happens with query logging... never thought that 
connecting would issue a query as well... not so strange an idea though... 
I'll check it out...

Michiel

At 09:10 27-1-2003 -0500, Tom Lane wrote:
>Michiel Lange <michiel@minas.demon.nl> writes:
> > it is pretty strange... might it be a bug?
>
>I'm quite sure it's a bug in your code.  Try turning on query logging so
>you can see what queries are being issued; maybe that will help solve
>the problem.
>
>                         regards, tom lane



Mon, 27 Jan 2003 19:16:32 +0100
Michiel Lange <michiel@minas.demon.nl>
Re: Cannot connect to the database (PG 7.3) 
Michiel Lange <michiel@minas.demon.nl> writes:
> but, but.... it is even with the psql command...

Hmm.  You didn't by any chance put a SET for session_authorization into
the per-user settings for 20020003, did you?  Or the per-database
settings for whichever database he's trying to connect to?  Look at
pg_shadow.useconfig and pg_database.datconfig.  For that matter, I think
you could have produced this symptom by putting an entry into
postgresql.conf.

			regards, tom lane

Mon, 27 Jan 2003 16:02:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cannot connect to the database (PG 7.3) 
OK, I figured it out now:

It is, somehow, not possible to connect as a user which name is completely 
numeric. I did a few tests on the same machine, and this is the outcome:

psql -h myhost -d mydb -U postgres -> connects fine
CREATE USER "12345" WITH PASSWORD 'secret';
CREATE USER
\q
psql -h myhost -d mydb -U 12345 -> error
psql -h myhost -d mydb -U "12345" -> error
psql -h myhost -d mydb -U '12345' -> error

psql -h myhost -d mydb -U postgres
DROP USER "12345";
DROP USER
CREATE USER n12345 WITH PASSWORD 'secret';
CREATE USER
\q
psql -h myhost -d mydb -U n12345 -> connects fine!

I think this is a bug, I will report it as one anyways... who knows what 
comes from it...
it is not nice that the DBMS does not prevent me from creating a user that 
cannot connect...
so either it should be prevented or possible...

But thanks for thinking along with me...

Michiel

At 14:19 27-1-2003 +0100, Michiel Lange wrote:
>it is at connect-time, so there is nothing done with set session 
>authorization.
>I just want to connect, and read data from a table to which the group the 
>user is in has read-access.
>I have tried if it would differ when I try to connect with a user created 
>like:
>CREATE USER 20020003 WITH ENCRYPTED PASSWORD 'secret';
>or
>CREATE USER 20020004 WITH UNENCRYPTED PASSWORD 'secred';
>it would not matter... even if there really is a trust-relation between my 
>computer and the other machine in pg_hba.conf
>it is pretty strange... might it be a bug?
>
>Thanks for the try though,
>Michiel
>At 19:42 26-1-2003 -0500, Tom Lane wrote:
>>Michiel Lange <michiel@minas.demon.nl> writes:
>> > I get this error message:
>> > psql: FATAL:  permission denied
>>
>>As near as I can tell, you must have some code that is trying to do
>>SET SESSION AUTHORIZATION --- which only a superuser is allowed to do.
>>Evidently user 20020003 isn't one.
>>
>>                         regards, tom lane
>



Tue, 28 Jan 2003 22:20:52 +0100
Michiel Lange <michiel@minas.demon.nl>
Re: Cannot connect to the database (PG 7.3) 
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.

			regards, tom lane

Tue, 28 Jan 2003 16:43:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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

Tue, 28 Jan 2003 17:04:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ADMIN] Cannot connect to the database (PG 7.3) 
Hmmm, I tried it (7.3.1) and it seems to work fine. Perhaps something else is 
going on - pg_hba.conf setup??:

steve=# create user "12345";
CREATE USER

steve@betelgeuse:~> psql -h betelgeuse -U 12345 steve
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

steve=> 

Cheers,
Steve


On Tuesday 28 January 2003 1:43 pm, Tom Lane 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.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Tue, 28 Jan 2003 15:26:01 -0800
Steve Crawford <scrawford@pinpointresearch.com>
Re: Cannot connect to the database (PG 7.3)
Steve Crawford <scrawford@pinpointresearch.com> writes:
> Hmmm, I tried it (7.3.1) and it seems to work fine.

> steve@betelgeuse:~> psql -h betelgeuse -U 12345 steve
> Welcome to psql, the PostgreSQL interactive terminal.

That's not 7.3 ... or at least not a 7.3 psql.  It would show its
version number if it were.

			regards, tom lane

Tue, 28 Jan 2003 18:29:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cannot connect to the database (PG 7.3) 
OK, (putting on best Ed McMahon voice), "you are correct, sir." That's what I 
get for keeping those old versions around. But...

7.2.3 psql -> 7.2.3 server = OK
7.3.1 psql -> 7.2.3 server = OK
7.3.1 psql -> 7.3.1 server = broken
7.2.3 psql -> 7.3.1 server = broken

So, looks like it's in the backend.

-Steve


On Tuesday 28 January 2003 3:29 pm, Tom Lane wrote:
> Steve Crawford <scrawford@pinpointresearch.com> writes:
> > Hmmm, I tried it (7.3.1) and it seems to work fine.
> >
> > steve@betelgeuse:~> psql -h betelgeuse -U 12345 steve
> > Welcome to psql, the PostgreSQL interactive terminal.
>
> That's not 7.3 ... or at least not a 7.3 psql.  It would show its
> version number if it were.
>
> 			regards, tom lane

Tue, 28 Jan 2003 15:49:47 -0800
Steve Crawford <scrawford@pinpointresearch.com>
Re: Cannot connect to the database (PG 7.3)
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


Wed, 29 Jan 2003 01:44:55 +0000 (GMT)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: [ADMIN] Cannot connect to the database (PG 7.3)
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> How about throwing an error if an all digit user name is given to create
> user as already alluded to?

I do *not* want to do that; that's creating a user-visible restriction
for the convenience of one very small part of the implementation.  I
think this behavior is a bug, and we should fix it not institutionalize
the misbehavior.  Just haven't figured out how, yet.

			regards, tom lane

Tue, 28 Jan 2003 21:00:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ADMIN] Cannot connect to the database (PG 7.3) 
>> 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

Tue, 28 Jan 2003 23:40:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ADMIN] Cannot connect to the database (PG 7.3) 
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

Wed, 29 Jan 2003 11:52:06 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Re: [ADMIN] Cannot connect to the database (PG 7.3)