Обсуждение: 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