
PostgreSQL or Postgres is an open source relational database management system. It is a popular and has many advanced features like reliable transactions and concurrency without read locks. In this tutorial, you will learn how to install PostgreSQL on Ubuntu 20.04.
Prerequisites
Make sure you should have root or user account with sudo privileges.
Install PostgreSQL on Ubuntu
We will install PostgreSQL from the official Ubuntu repositories. At first you need to update the package index list by typing:
sudo apt update
After that, install PostgreSQL with postgresql-contrib
package for several additional features using below command:
sudo apt install postgresql postgresql-contrib
Once the installation is completed, PostgreSQL service will start automatically. You can verify installation by connection to the PostgreSQL database server and view version using below command:
sudo -u postgres psql -c "SELECT version();"
It will show output as given below:
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
PostgreSQL Roles and Authentication Methods
A role can represent a database user or a group of database users. There are multiple authentication methods in PostgreSQL and commonly used are Trust
, Ident
, Password
and Peer
.
- Trust – This method used to connect without password using given criteria in
pg_hba.conf
file. - Ident – This method is mainly used on TCP/IP connection. It is obtaining client’s operating system user name, etc. details.
- Password – A role can connect by providing a password.
- Peer – It’s same as Ident but it is only supported on local connections.
You can login to PostgreSQL but as postgres
user first you need to switch to the postgres user and then you can access a PostgreSQL prompt using the psql
utility:
sudo su - postgres
psql
Now here you can interact with your PostgreSQL instance. To get exit of PostgreSQL prompt type:
\q
Create PostgreSQL Role and Database
You can use createuser
command to create new roles from the command line. You can only create if you are superuser
or have roles with CREATEROLE
privileges. By using createdb
method you can create a database in Postgres.
Use the below command to create a new role:
sudo su - postgres -c "createuser demouser"
To create a new database use the following command:
sudo su - postgres -c "createdb demodb"
Now, you should grant permission to user demouser
for newly created demodb
database. So run below command to connect PostgreSQL shell:
sudo -u postgres psql
Next execute following command which will grant permissions:
grant all privileges on database demodb to demouser;
Enabling Remote Access to PostgreSQL server
By default, the PostgreSQL server listens only on the local interface 127.0.0.1
. You should edit configuration file postgresql.conf
and add listen_addresses = '*'
in the CONNECTIONS AND AUTHENTICATION
section to enable remote access to your PostgreSQL server.
sudo nano /etc/postgresql/12/main/postgresql.conf
Update listen_addresses
like given below:
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service using systemctl command:
sudo service postgresql restart
Now confirm and verify the changes typing following in terminal:
ss -nlt | grep 5432
It will show output as below:
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
At last, update pg_hba.conf
file to configure the server to accept remote connections:
# TYPE DATABASE USER ADDRESS METHOD
# The user demouser will be able access all databases from all locations using a md5 password
host all demouser 0.0.0.0/0 md5
# The user demouser will be able access only the demodb from all locations using a md5 password
host demodb demouser 0.0.0.0/0 md5
# The user demouser will be able access all databases from a trusted location (192.168.43.92) without a password
host all demouser 192.168.43.92 trust
At last you have to open the port 5432
in your firewall.
If you are using UFW to manage your firewall, and you want to allow access from the 192.168.43.0/24
subnet, run the following command:
sudo ufw allow proto tcp from 192.168.43.0/24 to any port 5432
Conclusion
You learned successfully how to install and configure PostgreSQL on your Ubuntu 20.04 system. If you would like to get more details you can visit PostgreSQL 10.4 Documentation.
Leave a Reply