0

I'm trying to setup PostgreSQL onto a CentOS (that I connect through SSH). I need to unzip a package (so not downloading through yum or rpm): http://get.enterprisedb.com/postgresql/postgresql-9.3.4-3-linux-x64-binaries.tar.gz and set this up.

I unzipped, and ran the following

./postgresql-9.3/bin/initdb -D data/
./postgresql-9.3/bin/postgres -D data/

When I try to run ./postgresql-9.3/bin/psql or createuser I get asked for a password and I enter it, but get: psql.bin: FATAL: password authentication failed for user "myusername"

Question: How can I make psql and createuser work?

Additional info

My ./data/pg-hba.conf is as follows:

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust

Also, there doesn't seem to be a postgres user in the system, which makes sense since I simply unzip the tar.gz

1

First, postgres users and your operating system users are not the same thing, except by default.

Unless you create an actual postgres system account on your computer, you will need to edit your pg_hba.conf file and set up your authentication methods. The default should have a line something like

local   all             postgres                                peer

Which tells it to let the postgres super user log in locally if the person running psql is using an operating system account also named postgres. If you change peer to trust then it will allow anyone to log in locally with psql -U postgres. Once you can connect, you'll be able to create other users with their passwords.

Once things are set up, for security you should set a password on the postgres user, then change trust to md5 to require the password to connect.

  • When I put local all postgres peer, restart the postgres instance, and try bin/psql I get FATAL: Peer authentication failed for user "postgres" and a log statement saying LOG: provided username (postgres) and authenticated username (myusername) do not match When I put local all postgres trust instead, I get FATAL: role "postgres" does not exist – kolistivra Aug 27 '14 at 17:06
  • 1
    Try redoing initdb with -U postgres to set the superuser name – DerfK Aug 27 '14 at 17:26
  • Yep, this did the trick, finally I'm inside psql console!!! Though I need to use local all postgres trust, not peer. – kolistivra Aug 27 '14 at 17:53
1

You should issue the psql command as amministrative user, which is postgres as default: sudo -u postgres psql postgres

(assuming you added the postgres directory to your path, or sudo -u postgres ./postgresql-9.3/bin/psql postgres)

Once you are in psql console, \password postgres and enter your new password

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.