Configuring PostgreSQL After Installation

Set A Password

Login as the default user.

sudo -u postgres psql

In the CLI, enter:

ALTER USER postgres with encrypted password 'my_password';

Use the command \q to exit out of the CLI utility. Ctrl + D also works and is easier to remember, but clashes with byobu.

Restart the service for the changes to take effect.

sudo /etc/init.d/postgresql restart

Configure Local User Connections

Connecting from a local socket on the server itself doesn't require a password and requires you to be logged in as that user. If you wish to enable passwords, and be able to log in locally with users that exist in PostgreSQL that don't exist as users in your linux system, then change your pg_hba.conf file from trust or peer to md5 as shown below:

VERSION=*
sudo vim /etc/postgresql/$VERSION/main/pg_hba.conf

Change from:

# "local" is for Unix domain socket connections only
local   all             all                                     peer

to...

# "local" is for Unix domain socket connections only
local   all             all                                     md5

Allow Remote Connections

By default, your server will not accept remote connections. The steps below will show you how to configure your server to accept remote connections using a password for authentication. If you haven't set a password for your user already (above), do that now.

Edit the pg_hba.conf file and add the following line to allow connections from anywhere on the internet.

host    all    all    0.0.0.0/0    md5

sudo vim /etc/postgresql/9.*/main/pg_hba.conf

If you can swap out 0.0.0.0/0 for a more exact IP/CIDR of the servers that you wish to connect, that would be much better. To add just one IP, use xxx.xxx.xxx.xxx/32. You can add multiple lines for multiple CIDRs.

Edit postgresql.conf

Now edit the postgresql.conf file...

sudo vim /etc/postgresql/9.*/main/postgresql.conf

... and add the following line:

listen_addresses = '*'

Restart The Service

Now restart PostgreSQL for the changes to take effect.

sudo /etc/init.d/postgresql restart

Test It

To check that everything went correctly, try to make a connection to your local server by using your servers IP as below:

psql -U postgres -h [Server IP]

You should be prompted for a password, which only if you enter correctly, you will gain access.

References

• How to Allow Remote Connection to PostgreSQL Database using psql
• Stack Overflow - How to configure postgresql for the first time?
• Stack Overflow - PG Peer authentication failed