PostgreSQL password issue with psql

postgresqlpsql

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

Best Answer

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.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Postgresql – How to show the content of a trigger with psql

OK, I found out about it myself.

The command \dft doesn't show the triggers itself (as I thought), it shows all trigger-functions (return-type trigger).

To see the trigger you can make \dS <tablename>, it shows not only columns of this table, but also all triggers defined on this table.

To show the source of the trigger-function (or any function) use \df+ <functionname>.

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Postgresql – How to show the content of a trigger with psql

Related Topic