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
Have you tried the CREATE USER command from psql/a client
application? I.e. to create a user that is not a super user and can't
create databases or roles:
CREATE USER [name] WITH NOCREATEDB NOCREATEUSER PASSWORD '[password]';
as for your 'createuser' problem... i've got nothin
On Jan 18, 2007, at 11:27 AM, Stockho, Jonathan W wrote:
Show quoted text
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 directoryIs 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
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 directoryIs 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