macbook pro near iphone and apple fruit

Setting up PostgreSQL on Linux

/ DevOps / By

Introduction

PostgreSQL is an advanced enterprise-grade open source relational database system. In this post, I’ll show you how to set it up on a Linux server.

Install Postgres

To install postgres on Ubuntu or Debian like operating systems, the command is:

sudo apt update 
sudo apt install postgresql postgresql-contrib

Start Postgresql service

The postgres service should start automatically. If it doesn’t, start it using

sudo systemctl start postgresql

Access PostgreSQL shell

psql is a terminal based front-end or client to PostgreSQL. You can use it to type commands and queries to PostgreSQL. In addition, psql includes meta commands and shell-like features that allow you to write scripts and automate database related tasks.

Template Databases

PostgreSQL will, by default create a few system databases called template0, template1 and postgres when it is first initialised. template1 is the template from which all new databases are created. Any data you add to template1 will be copied over into any subsequent databases you create. This feature can be useful if all your databases need to have the same or a similar base of information when they are created. These databases don’t have any special status beyond being templates from which all new databases are created.

The postgres database is a copy of template1 and is meant as a default database for users to connect to.

The command below will connect you to the initial postgres database:

sudo -u postgres psql -d postgres

psql needs to connect to something . Once you’re connected to an initial database on the server, you can view a list of all the other databases you can also connect to, but until you connect to that first database like postgres, you can’t fetch that list of databases.

Create Database

To create a new database, type the following into psql:

CREATE DATABASE your_database_name;

This creates a new blank database with the name you assigned to it.

Create Database User

Create a user account to access the database with:

CREATE USER your_username WITH PASSWORD 'your_password';

This command creates a new user account on the postgres server.

Grant Permissions

Give your user permissions on the database you just created

GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

This assigns full access priviledges on the specified database to the user account you type in. Granting all priviledges includes the ability to create, connect, and perform any operation within the target database.

Exit psql

That’s all you need to do to set up a database. To exit out of psql, type the following:

\q

Configure connection settings

To allow clients to connect to the database server, you want to configure a few things. First, configure an IP address that the PostgreSQL server should listen on or in other words, configure the address that clients should expect the postgres server to be on. Edit the /etc/postgresql/{version}/main/postgresql.conf file where {version} is your version of postgresql and change the listen_address line to something like this:

listen_address= 'localhost,192.168.200.2'

Replace the IP in this example with the IP of your postgres server.

You can set the port, the number of connections, replication and other settings in the postgresql.conf file.

Next, configure host authentication parameters by editing the /etc/postgresql/{version}/main/pg_hba.conf file. In PostgreSQL, pg_hba stands for PostgreSQL Host-Based Authentication. This file is used to manage client authentication rules. It specifies which hosts are allowed to connect to the PostgreSQL server, which databases they can access, and the authentication methods they must use.

Each line in the pg_hba.conf file represents a host, a database and the authentication method to be employed. If you want to allow all devices on a subnet to connect to your database, you can add a configuration like this:

TYPE        DATABASE      USER     ADDRESS               METHOD
host         all          all     192.168.200.0/24      md5/scram-sha-256

This will allow any device or client in the 192.168.200.0 subnet to access any database on this server using the scram-sha-256 authentication method. scram-sha-256 is one of many password-based authentication methods and currently thought to be the most secure. Unfortunately scram-sha-256 only works on newer versions of postgres. If you have to support an older version of postgres, you can use the md5 method.

Next, create a firewall rule in the PostgreSQL server to allow connections from remote hosts in the subnet you configured in the pg_hba.conf file. Using UFW, you can do this by running:

sudo ufw allow from 192.168.200.0/24 to any port 5432

This creates an entry in the iptables firewall to allow incoming connections to the database from any client in the subnet.

Test Connection

To test that your connection works, connect to the database server using psql or another Postgres Client. In psql the command to connect is:

psql -h db_hostname -d postgres -U db_username

If you set up everything correctly, you should be prompted to enter the db user’s password.

Postgres Connection Troubleshooting

At this point, clients should be able to connect to your database. If you’re running into trouble connecting, try the following tips:

  • Check that UFW is allowing connections in
  • Check connectivity with Telnet: telnet hostname 5432
  • Check the connection settings defined in /etc/postgresql/{version}/main/postgresql.conf
  • For clues to what could be causing problems, check the postgres logs for errors:
    • cat /var/log/postgresql/postgresql-14-main.log

Conclusion

In this post, you learned about the process of installing and configuring the PostgreSQL server for basic usage. Additionally, the post covered how to connect to the server using the psql tool. The steps I shared here are steps to setup a basic Postgres install. Refer to the documentation for more information about it.