This is the first post in a short series documenting the various methods of installing PostgreSQL. In this post, we’ll look at how to install PostgreSQL natively on Linux and connect to the cluster we install from a remote client.
The VM
Firstly, let’s create a VM to install PostgreSQL on. I won’t detail the steps involved in this as that is outside the scope of this post, however, the VM that I used for this guide is the following spec:
- 4-Core CPU
- 16GB RAM
- Debian 13
Installation
To run the commands required, we can SSH into the server. Once in the shell, the first task is to update packages:
sudo apt update
We can see the local package index for the default repositories has been updated. Now we can install PostgreSQL:
sudo apt install postgresql -y
The install takes less than a minute.
Now that PostgreSQL is installed, let’s try to log into the cluster with the following command:
psql
Perhaps unsurprisingly, we got the error above.
As we didn’t specify a user to log in as, psql will attempt to log in under the context of the user logged into the shell, which in our case is not a user in PostgreSQL. This makes sense as we have not set anything up yet.
Let’s see if the installer has created a user:
cat /etc/passwd
We can see the last account created was the postgres account. Let’s log in to postgres as that user
psql -U postgres
This fails with the following error:
This is because peer authentication is used by PostgreSQL as a default authentication method when logging in from the local server. Peer authentication means PostgreSQL asks the OS if the user we are trying to log in as (specified in the -U parameter) is the user logged into the shell – if the answer is yes, PostgreSQL doesn’t ask for a password – it trusts the OS authentication and allows the user to log in, similar to Windows Authentication in SQL Server. If the user logged into the shell is not the one we are trying to log in as, peer authentication fails.
If we specify the host (localhost in our case) then password authentication will be used
psql -U postgres -h localhost
However, this then asks us for a password, which is not set yet, so we still can’t get in:
To log in, we need to use sudo to switch our user to the postgres user and then log in using peer authentication:
sudo -u postgres psql
We can see we have now logged into the cluster.
Note the version is 17.10, which is older than the most recent (at the time of writing) version (18) – this is because I am using the Debian stable repositories which can be a version or two behind the latest, depending on where we are in its release cycle. If we wanted to install the latest and greatest version of PostgreSQL on Debian, we’d have to install from the official PostgreSQL repository. For the sake of this demonstration, version 17 is fine.
To see what databases we have, we can use the following command:
\l
We can see the three system databases present.
Logging In Remotely
One final thing we can do is log into this server from the pgAdmin tool on our desktop machine.
To do this, we add a new server:
When hitting the save button, we see an error:
The reason for this is that by default, a PostgreSQL cluster does not allow remote connections and to change this, we need to alter postgresql.conf and pg_hba.conf. On Debian, the files are at /etc/postgresql/17/main, though this may vary on other distributions. Note that /17 is the version of PostgreSQL and /main is the name of the cluster, so these can be changed as appropriate.
We can use nano or some other favourite text editor to amend the file:
sudo nano /etc/postgresql/17/main/postgresql.conf
The config we need to add is below. This tells PostgreSQL to listen on all network interfaces, rather than just localhost.
listen_addresses = '*'
We then need to change pg_hba.conf to tell PostgreSQL which IP addresses to accept connections from. I’ll just allow all here, but it may be prudent to restrict via subnet or IP.
I’ll use nano to edit the file:
sudo nano /etc/postgresql/17/main/pg_hba.conf
We need to add the text:
host all all 0.0.0.0/0 scram-sha-256
Now we need to restart the service for changes to take effect:
sudo systemctl restart postgresql
Now let’s try to connect again in pgAdmin:
The issue now is that we haven’t supplied a password for the postgres user as the installer did not set one up. Let’s log in using peer authentication again:
sudo -u postgres psql
Alter the postgres user and set a password:
ALTER USER postgres with password 'MyPass';
Now let’s connect again from pgAdmin, supplying the password we just set…
Success:
Conclusion
In this post, the first in a multi-part series, we installed PostgreSQL directly on a VM. We changed the default configuration so we were able to connect remotely to the new cluster.
References / Further Reading
The PostgreSQL Global Development Group – Authentication Methods
The PostgreSQL Global Development Group – Debian Download Page