PostgreSQL is a worldwide supported open-source database. With over 30 years of service, PostgreSQL, thanks to the various add-ons available, is famous for its robustness, scalability, and performance.
First, connect to your server via an SSH connection. If you haven’t done so yet, following our guide is recommended to connect securely with SSH. In case of a local server, go to the next step and open the terminal of your server.
Installation of PostgreSQL
To proceed with the installation of PostgreSQL, use the apt package manager. Make sure to update the cache:
$ sudo apt update && sudo apt install postgresql postgresql-contrib
Ensure that the server is running using the systemctl start command:
sudo systemctl start postgresql.service
Now that you completed the install of Postgres on Ubuntu, the services will start automatically. As a first step, verify the installation by connecting to the PostgreSQL database with psql.
The psql is a command line utility that is used to interact with the PostgreSQL server. This will print the server version:
sudo -u postgres psql -c "SELECT version();"
To log in to PostgreSQL as a postgre user you can use the psql. This can be done as shown below:
sudo su – postgres
To use PostgreSQL type in:
It will grant you access to PostgreSQL instance. In order to exit, you can use:
Managing users and permissions
As previously mentioned, PostgreSQL assumes that every user present in its service is also present in the system. In order to create a new user, first add it to the system via adduser:
$ sudo adduser tutorial
In the following example a user called “tutorial” is being created, without specifying other information, such as the name or the phone number. Once completed, the PostgreSQL user can be created. by first logging in with the postgres user:
$ sudo -i -u postgres
$ createuser --interactive
After creating the user, set a password, logging in through the superuser postgres and altering the user you just created:
$ psql postgres=# ALTER USER tutorial PASSWORD 'password'; ALTER ROLE;
A database for the newly created user might also be created by running the command:
$ createdb tutorial
Once completed, log out and try to log in with the newly created user:
$ sudo -i -u tutorial
Enabling access to external clients
In some cases, it may be necessary to allow access by external clients (for example your machine) to one or more databases on the server. Normally, PostgreSQL is not configured to accept connections that are not local, so alter some configuration files.
The first file to be modified is the main configuration file, in
Make sure to edit the following line from:
listen_addresses = 'localhost'
listen_addresses = '*'
In this way you inform PostgreSQL to expect incoming connections from any network interface.
Make sure to save your change. Now move on to editing the file that manages the server access rules in /etc/postgresql/10/main/pg_hba.conf, by adding the following line:
host tutorial tutorial 0.0.0.0/0 md5
This modification allows the “tutorial” user to access his database from any host. In particular , the fields indicated are:
- Host: the type of connection you are accepting. Possible values are:· local: non-network connections from the system
· host: any type of TCP / IP connection
· hostssl: TCP / IP connections only under SSL protocol
· hostnossl: TCP / IP connections NOT under SSL protocol
- Tutorial: name of database to allow access, use “all” to refer to all databases
- Tutorial: username to grant access
- 0.0.0.0/0: IP address, in your case you are authorizing access to any IPv4 address
- md5: accepted authentication methodology, some of the most important are:
· md5: password authentication
· trust: accepts unconditionally the connection unconditionally
· peer: uses the system username to connect only to the database of the same name.
For further information access the address https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html or the official PostgreSQL document.
Once ended, restart the service to apply all the changes:
$ sudo systemctl restart postgresql