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

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:

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

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:

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

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:

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

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

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

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

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 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

"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

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