How to install PostgreSQL on Ubuntu 18.04

s2 0

PostgreSQL or Postgres is an open source, object relational database management system for general purposes. PostgreSQL has many advanced features that you can use to build complex web applications.

In this tutorial, we are going to show you how to install PostgreSQL on Ubuntu 18.04 and explore the basics of basic database administration.

requirements

Before proceeding with this tutorial, make sure that you are logged in as a user with sudo permissions.

Install PostgreSQL on Ubuntu

At the time of this writing, the latest version of PostgreSQL available in the official Ubuntu repositories is PostgreSQL version 10.4.

To install PostgreSQL on your Ubuntu server, do the following:

  1. Install PostgreSQL

    Update the local package index and install the PostgreSQL server along with the PostgreSQL Contrib package, which provides several additional functions for the PostgreSQL database:

    sudo apt updatesudo apt install postgresql postgresql-contrib
  2. Check the PostgreSQL installation

    Once the installation is complete, the PostgreSQL service will start automatically.

    To verify the installation we will try to connect to the PostgreSQL database server by running the psql and print out the server version:

    sudo -u postgres psql -c "SELECT version();"
psql is an interactive command line utility that you can use to interact with the PostgreSQL server.

PostgreSQL roles and authentication methods

Database access authorizations in PostgreSQL are handled according to the role concept. A role can represent a database user or a group of database users.

PostgreSQL supports several Authentication methods
. The most commonly used are:

  • Trust – With this method, the role can connect without a password as long as the one in the pg_hba.conf are fulfilled.
  • Password – A role can connect by providing a password. The passwords can be saved as scram-sha-256 md5 and password (Plain text)
  • Ident – This method is only supported for TCP / IP connections. Works by getting the operating system username of the client with an optional username mapping.
  • Peer – Like Ident, but only supported for local connections.

PostgreSQL client authentication is in the configuration file named. Are defined pg_hba.conf. By default, PostgreSQL for local connections is set to use the peer authentication method.

the postgres The user is automatically created when you install PostgreSQL. This user is the superuser for the PostgreSQL instance and corresponds to the MySQL root user.

To log in to the PostgreSQL server as a postgres user, you must first switch to the postgres user and then you can use the. access a PostgreSQL command prompt psql Utility:

sudo su - postgrespsql

From here you can interact with your PostgreSQL instance. To exit the PostgreSQL shell, type:

q

You can also access the PostgreSQL command prompt without changing users sudo
Command:

sudo -u postgres psql

the postgres user is normally only used by the local host and it is recommended that you do not set the password for this user.

Create PostgreSQL roles and databases

You can create new roles from the command line by typing the createuser Command. Only superuser and roles with CREATEROLE Authorization can create new roles.

In the following example we are creating a new role called john a database called johndb and grant permissions on the database.

  1. Create a new PostgreSQL role

    The following command creates a new role called “john”:

    sudo su - postgres -c "createuser john"
  2. Create a new PostgreSQL database

    Create a new database called “johndb” with the createdb Command:

    sudo su - postgres -c "createdb johndb"
  3. Grant privileges

    How to grant permissions for the john Users in the database we created in the previous step 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 the PostgreSQL server

By default, the PostgreSQL server only listens on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server, open the configuration file postgresql.conf and add listen_addresses = '*' by doing CONNECTIONS AND AUTHENTICATION Section.

sudo vim /etc/postgresql/10/main/postgresql.conf
/etc/postgresql/10/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 with:

sudo service postgresql restart

Confirm the changes with ss Utility:

ss -nlt | grep 5432
LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

As you can see from the output above, the PostgreSQL server listens on all interfaces (0.0.0.0).

The final step is to configure the server to accept remote connections by using the pg_hba.conf File.

Below are some examples that show different use cases:

/etc/postgresql/10/main/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane will be able to access all databases from all locations using a md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane will be able to access only the janedb from all locations using a md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

Conclusion

You learned how to install and configure PostgreSQL on your Ubuntu 18.04 server.

Consult the PostgreSQL 10.4 documentation
for more information on this.

If you have any questions please leave a comment below.