Re: Can't use passwords for users - Mailing list pgsql-general

From Neal Clark
Subject Re: Can't use passwords for users
Date
Msg-id DA10210D-F043-41FE-853C-FCF68C7B88AE@securescience.net
Whole thread Raw
In response to Can't use passwords for users  ("Stockho, Jonathan W " <jstockho@alionscience.com>)
List pgsql-general
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:

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

pgsql-general by date:

Previous
From: "A.M."
Date:
Subject: Re: PostgreSQL and embedded PC with Compact Flash?
Next
From: "Denis Lussier"
Date:
Subject: EnterpriseDB Apology