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.

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:

psql

It will grant you access to PostgreSQL instance. In order to exit, you can use:

\q

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

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:

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:

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