Programster's Blog

Tutorials focusing on Linux, programming, and open source

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

Use Password For Local Connections

You have now set a password for the user which will be used for remote connections (which still requires the steps in the next section). Connecting from a local socket on the server itself still doesn't require a password. If you wish to change this so that you do have to enter a password then Edit the pg_hba.conf file and change trust to md5 as shown below:

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

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 and add the listen_addresses = '*' line as indicated below.

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

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