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
Once the installation is completed, the service will be immediately available. Check the log file in /var/log/postgresql/postgresql-10-main.log just to make sure
# tail /var/log/postgresql/postgresql-10-main.log
...LOG: database system is ready to accept connections
...
If everything works correctly, the above text will be in the last lines of the log file.
Verifying installation
To verify the successful installation of PostgreSQL, use the command line client called psql. First, use the default user installed by the service. To access the database, the authentication process of the default configuration of PostgreSQL uses the same system users..
To log in with the default user , from the command line, run
Now, start the client and use the \l command to view the list of the databases available :
$ sudo -i -u postgres
$ psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \q
postgres@Ubuntu-18-PSQ:~$ exit
Once completed,exit the client with the command \q and return to the original user by typing ‘exit’.
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
Adding user `tutorial' ...
Adding new group `tutorial' (1000) ...
Adding new user `tutorial' (1000) with group `tutorial' ...
Creating home directory `/home/tutorial' ...
Copying files from `/etc/skel' ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
Changing the user information for tutorial
Enter the new value, or press ENTER for the default
Full Name []:
Room Number []:
Work Phone []:
Home Phone []:
Other []:
Is the information correct? [Y/n] y
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
Enter name of role to add: tutorial
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
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:
Once completed, log out and try to log in with the newly created user:
$ sudo -i -u tutorial
tutorial@Ubuntu-18-PSQ:~$ psql
psql (10.9 (Ubuntu 10.9-0ubuntu0.18.04.1))
Type "help" for help.
tutorial=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
tutorial | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
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 /etc/postgresql/10/main/postgresql.conf.
Make sure to edit the following line from:
listen_addresses = 'localhost'
With:
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