Thread: Can't use passwords for users
I’m new to postgres, so bare with me.
I installed version 8.2 from rpms on Suse 10.
I then changed to the postgres user and ran initdb.
After that I create a database called movies using the command “createdb movies”
Then I used the pg_ctl command to start the postmaster with the following command:
pg_ctl start –l /tmp/pg.log –o –i
Everything at this point was working fine. I was able to use the postgres user to work in the database.
I wanted to create another user so I quit the database and went back to my normal prompt.
I then used the command “createuser test –pwprompt”
I got the following questions (which I answered “n” for each):
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
PLEASE READ THE FOLLOWING ERROR AND THE STUFF BELOW. I KNOW THIS ERROR HAS BEEN SEEN BEFORE, BUT I HAVEN’T FOUND ANY EXAMPLES OF MY SPECIFIC PROBLEM!!
I then got the following output (I’m assuming this is when I’d be prompted for a password):
createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.0”?
I’ve looked at everything I could find about this error. I know the server is running because I can connect to it with the postgres account. It actually did create the user ‘test’ and I can connect to the database as long as I don’t attempt to use a password. The ‘test’ account must also be a valid account on my Linux machine which has something to do with the IDENT authentication type. I don’t want to use IDENT, I want to use passwords. I checked and there is an active listening Unix socket on port 5432 like there should be. I don’t have PGPORT set as an environment variable, and even setting one to 5432 did not get rid of this problem. I also tried manually creating the account test inside of postgresql using the command “create user test nocreatedb nocreateuser password ‘testPassword’”. This didn’t give me any errors, but when I tried to log into the database using the command: “psql –U test –p testPassword –d movies” I got the same error as above.
REPEAT: I AM ONLY SEEING THIS ERROR WHEN I TRY TO USE PASSWORDS ON USER ACCOUNTS. I need to use this type of security. Can anyone tell me what’s going on? Everything else works fine.
On a side note, I also want to use TCP/IP connections. I changed the postgresql.conf file to set LISTEN_ADDRESSES to ‘*’. Is there anything else I need to do? Do I need to set the old VIRTUAL_HOST and TCPIP_SOCKET variables? Or does LISTEN_ADDRESSES take care of all that? What do I have to do to the pg_hba.conf file to allow all incoming connections?
Thanks for any help you can offer. I greatly appreciate it.
-Jonathan
I’m new to postgres, so bare with me.
I installed version 8.2 from rpms on Suse 10.
I then changed to the postgres user and ran initdb.
After that I create a database called movies using the command “createdb movies”
Then I used the pg_ctl command to start the postmaster with the following command:
pg_ctl start –l /tmp/pg.log –o –i
Everything at this point was working fine. I was able to use the postgres user to work in the database.
I wanted to create another user so I quit the database and went back to my normal prompt.
I then used the command “createuser test –pwprompt”
I got the following questions (which I answered “n” for each):Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
PLEASE READ THE FOLLOWING ERROR AND THE STUFF BELOW. I KNOW THIS ERROR HAS BEEN SEEN BEFORE, BUT I HAVEN’T FOUND ANY EXAMPLES OF MY SPECIFIC PROBLEM!!
I then got the following output (I’m assuming this is when I’d be prompted for a password):
createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.0”?
I’ve looked at everything I could find about this error. I know the server is running because I can connect to it with the postgres account. It actually did create the user ‘test’ and I can connect to the database as long as I don’t attempt to use a password. The ‘test’ account must also be a valid account on my Linux machine which has something to do with the IDENT authentication type. I don’t want to use IDENT, I want to use passwords. I checked and there is an active listening Unix socket on port 5432 like there should be. I don’t have PGPORT set as an environment variable, and even setting one to 5432 did not get rid of this problem. I also tried manually creating the account test inside of postgresql using the command “create user test nocreatedb nocreateuser password ‘testPassword’”. This didn’t give me any errors, but when I tried to log into the database using the command: “psql –U test –p testPassword –d movies” I got the same error as above.
REPEAT: I AM ONLY SEEING THIS ERROR WHEN I TRY TO USE PASSWORDS ON USER ACCOUNTS. I need to use this type of security. Can anyone tell me what’s going on? Everything else works fine.
On a side note, I also want to use TCP/IP connections. I changed the postgresql.conf file to set LISTEN_ADDRESSES to ‘*’. Is there anything else I need to do? Do I need to set the old VIRTUAL_HOST and TCPIP_SOCKET variables? Or does LISTEN_ADDRESSES take care of all that? What do I have to do to the pg_hba.conf file to allow all incoming connections?
Thanks for any help you can offer. I greatly appreciate it.
-Jonathan
Attachment
Stockho, Jonathan W wrote: > I'm new to postgres, so bare with me. > > I installed version 8.2 from rpms on Suse 10. > > I then changed to the postgres user and ran initdb. > > After that I create a database called movies using the command "createdb > movies" > > Then I used the pg_ctl command to start the postmaster with the > following command: > > pg_ctl start -l /tmp/pg.log -o -i Hi Johnathan - I think this might be part of the problem. There's usually a startup script in /etc/init.d/ and you'd start the server with /etc/init.d/postgresql start What might be happening is that the RPM has changed something from the default initdb/pg_ctl. > createuser: could not connect to database postgres: could not connect to > server: No such file or directory > > Is the server running locally and accepting connections on > Unix domain socket "/tmp/.s.PGSQL.0"? You see, since we know the server is running then this socket must be somewhere else. That suggests half of the system is looking at PG defaults and the other half at the RPM settings. > On a side note, I also want to use TCP/IP connections. I changed the > postgresql.conf file to set LISTEN_ADDRESSES to '*'. Is there anything > else I need to do? You'll want to update the pg_hba.conf file (and for passwords too) - see the manuals for details. I'd remove your existing directories (I'm assuming you've not got any useful data there yet) and then start the database the /etc/init.d/... way. That will probably run initdb etc. for you. -- Richard Huxton Archonet Ltd
"Stockho, Jonathan W " <jstockho@alionscience.com> writes: > createuser: could not connect to database postgres: could not connect to > server: No such file or directory > Is the server running locally and accepting connections on > Unix domain socket "/tmp/.s.PGSQL.0"? ".0"? That suggests that the program is trying to connect to port number zero. That's got nothing to do with passwords or any of the other stuff you're going on about; it's trying to connect to the wrong place. You need to find out why. Could you have "PGPORT=0" in your environment for some reason? BTW, you didn't really type "createuser test -pwprompt" did you? Every version of createuser as far back as 7.3 rejects that as invalid syntax. If you've got a version that actually took it just like that, then you've got a stone-age Postgres and your first move should be to update to something that was released in this century. regards, tom lane