Обсуждение: Cannot connect to the database (PG 7.3)
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
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: > > 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
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: > > 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
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: >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 >
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
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
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 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