PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows you to build fault-tolerant environments or complex applications.
In this guide, we will explain how to install the PostgreSQL database server on Ubuntu 20.04, and explore the basics of PostgreSQL database administration.
To be able to install packages, you need to be logged in as root or user with sudo privileges.
Install PostgreSQL on Ubuntu
At the time of writing this article, the latest version of PostgreSQL available from the official Ubuntu repositories is PostgreSQL version 10.4.
Run the following commands to install PostgreSQL server on Ubuntu:
sudo apt update
sudo apt install postgresql postgresql-contrib
We’re also installing the PostgreSQL contrib package that provides several additional features for the PostgreSQL database system.
Once the installation is completed, the PostgreSQL service will start automatically. Use the
psql tool to verify the installation by connecting to the PostgreSQL database server and printing its version:
sudo -u postgres psql -c "SELECT version();"
PostgreSQL 12.2 (Ubuntu 12.2-4) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-8ubuntu1) 9.3.0, 64-bit
That’s it. PostgreSQL has been installed, and you can start using it.
PostgreSQL Roles and Authentication Methods
Database access permissions within PostgreSQL are handled with the concept of roles. A role can represent a database user or a group of database users.
PostgreSQL supports multiple authentication methods. The most commonly-used methods are:
- Trust – A role can connect without a password, as long as the conditions defined in the
- Password – A role can connect by providing a password. The passwords can be stored as
- Ident – Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
- Peer – Same as Ident, but it is supported on local connections only.
PostgreSQL client authentication is defined in the configuration file named
pg_hba.conf. By default, PostgreSQL is using the peer authentication method for local connections.
postgres user is automatically created when you install PostgreSQL. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.
To log in to the PostgreSQL server as the
postgres user, first switch to the user and then access the PostgreSQL prompt using the
sudo su - postgres
From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:
Another way to access the PostgreSQL prompt without switching users, is to use the
sudo -u postgres psql
Generally, you should log in to the database server as
postgres only from the localhost.
Creating PostgreSQL Role and Database
Only superusers and roles with
CREATEROLE privilege can create new roles.
The following example shows how to create a new role named
john a database named
johndb and grant privileges on the database:
Create a new PostgreSQL role:
sudo su - postgres -c "createuser john"
Create a new PostgreSQL Database:
sudo su - postgres -c "createdb johndb"
To grant permissions to the user on the database, connect to the PostgreSQL shell:
sudo -u postgres psql
and run the following query:
grant all privileges on database johndb to john;
Enable Remote Access to PostgreSQL server
By default, the PostgreSQL server listens only on the local interface (
To enable remote access to your PostgreSQL server open the configuration file
postgresql.conf and add
listen_addresses = '*' in the
CONNECTIONS AND AUTHENTICATION section.
sudo nano /etc/postgresql/12/main/postgresql.conf
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service:
sudo service postgresql restart
Verify the changes with the
ss -nlt | grep 5432
The output shows that the PostgreSQL server is listening on all interfaces (
LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* LISTEN 0 244 [::]:5432 [::]:*
The next step is to configure the server to accept remote connections by editing the
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD # The user jane can access all databases from all locations using md5 password host all jane 0.0.0.0/0 md5 # The user jane can access only the janedb from all locations using md5 password host janedb jane 0.0.0.0/0 md5 # The user jane can access all databases from a trusted location (192.168.1.134) without a password host all jane 192.168.1.134 trust
The last step is to open the port
5432 in your firewall.
Assuming you are using
UFW to manage your firewall, and you want to allow access from the
192.168.1.0/24 subnet, you would run the following command:
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432
Make sure your firewall is configured to accept connections only from trusted IP ranges.
We’ve shown you how to install and configure PostgreSQL on Ubuntu 20.04 server. Consult the PostgreSQL 12 Documentation for more information on this topic.
If you have any questions, please leave a comment below.