Setting up a remote Postgres database server on Ubuntu 18.04

Introduction

Postgres is a powerful relational database management system, it can handle large workloads from a single machine to that of a data center. It is highly scalable and widely popular. In this article, we will be learning how to set up a remote Postgres database server to use for your projects. This article setup will allow Postgres connection from any IP address and will not cover specific/authorized IP connection.

Prerequisites

  • Familiarity with the command line interface
  • An Ubuntu server, you can quickly provision one from DigitalOcean or any cloud provider
  • A lot of patience
  • Postgres installed on a local machine

Installing Postgres

In this step, you will be installing Postgres on your server. The first thing to do is SSH into your server by running:

ssh [email protected]_ip

Then input your relevant user password or SSH key password if any. Next, update your server packages and dependencies by running:

sudo apt-get update

When that is done, install Postgres by running:

sudo apt-get install postgresql postgresql-contrib

This will install Postgres along with its associated dependencies. When the process is complete, switch the user to postgres to be able to execute Postgres commands with Postgres default user by running:

su - postgres

The server user will be switched from root to postgres. You can access the Postgres shell by running:

psql

You will be shown something similar to this:

[email protected]:~$ psql psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)) Type "help" for help postgres=#

Create user

In this step, you will be creating a new user that will be used to access your Postgres database remotely. To create a new user, exit the Postgres shell by executing:

q

While still being logged in as postgres run the following command to create a new user:

createuser --interactive --pwprompt

A prompt will be shown to you asking you to input your desired user role, name, password, and if you want the user to be a superuser. Here is an example:

Enter name of role to add: cleopatra Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) y

I named my user role cleopatra and I made my user a superuser. A superuser is a user that has all the privileges available on a Postgres instance. Next, we will be assigning cleopatra to a database. To do this, run the following command:

createdb -O cleopatra egypt

This command above will create a new database named egypt and assign cleopatra to be the database user.

Allow remote access

In this step, we will look at how to configure Postgres to accept external connections. To begin, open the configuration file with your preferred editor:

nano /etc/postgresql/10/main/postgresql.conf

Look for this line in the file:

#listen_addresses = 'localhost'

Uncomment, and change the value to '*', this will allow Postgres connections from anyone.

listen_addresses = '*'

Save and exit the file. Next, modify pg_hba.conf to also allow connections from everyone. Open the file with your preferred editor:

nano /etc/postgresql/10/main/pg_hba.conf

Modify this section:

# IPv4 local connections: host all all 127.0.0.1/32 md5

To this:

# IPv4 local connections: host all all 0.0.0.0/0 md5

This file stores the client authentication, each record specifies an IP address range, database name, username, and authentication method. In our case, we are granting all database users access to all databases with any IP address range, thus, letting any IP address connect. Save and exit the file. Next, allow port 5432 through the firewall by executing:

sudo ufw allow 5432/tcp

Finally, restart Postgres to apply all the changes you have made to its configuration by running:

sudo systemctl restart postgresql

Connect to Postgres remotely

In this step, you will be connecting to your server from an external machine. Connect to the remote Postgres database by running:

psql -h {server_ip} -d egypt -U cleopatra

Where {server_ip} is your server IP address, you will get a prompt to type your user password, if the credentials match you’ll be logged into the Postgres shell for cleopatra and database egypt.

Create a new table and name it pharaohs by executing the following in the Postgres shell:

create table pharaohs(name text);

Next, add a record to the pharaohs table, you will be adding Tutankhamun as a string to the table by running the following in the Postgres shell:

insert into pharaohs (name) values ('Tutankhamun');

Next, we will be accessing our database using a GUI (Graphical User Interface) tool like tablePlus which enables you to visualize data away from the command line interface to see if we can find the records we created. Open TablePlus and click on Create a new connection.

Source: us.suanoncolosence.com

Select Postgres from the dropdown

Input credentials

If your credentials are correct, you will be shown a GUI panel to view your database records in which you will find the table created and the record we added to it.

As shown in the image, we can see the pharaohs table we created earlier and the record we added to it. Our remote database is ready!

Conclusion

We have seen how to configure a Postgres database server for remote access. With this knowledge, you can set up a database server for your next project. In production, there are some security measures you will have to keep in mind. For example, only allowing the specified IP address and not allowing root access to your server, you can learn how to set this up in this article.

— Update: 19-03-2023 — us.suanoncolosence.com found an additional article How To Configure PostgreSQL to Allow Remote Connections from the website tecadmin.net for the keyword how to connect to remote postgresql database in linux.

An open-source, object-based relational database PostgreSQL, provides the user with the implementation of SQL and is commonly hosted on Linux. With PostgreSQL users can expand the system by defining self-data types, functions, and operators.

Via @: us.suanoncolosence.com

PostgreSQL is used by many large companies to save and store their data for various applications and it supports various programming interfaces as well as videos, texts, and images. In this article we’ll first go through the installation of PostgreSQL on ubuntu 20.04 then we’ll configure it to allow remote connection

Prerequsities

This article assumes that you already have running a PostgreSQL server on your system. If not, use one of the below links to install the PostgreSQL database server on your system.

  1. Installing PostgreSQL on Ubuntu 20.04
  2. Installing PostgreSQL on CentOS 8

Find Configuration File

In order to install PostgreSQL on our system we need to update our repository and for that execute the below command:

sudo -u postgres psql -c "SHOW config_file;"  
 config_file ----------------------------------------- /etc/postgresql/13/main/postgresql.conf (1 row)

You need to change the listening address in the postgresql.conf configuration file showing in the command output. Also, you need to edit “pg_hba.conf” in the same directory to allow remote access.

Configure PostgreSQL to Allow Remote Connections

In order to allow all the IP addresses to connect to the PostgreSQL server, we need to configure the file and make some changes, for that you have located the configuration file in the previous step.

  1. Configuring postgresql.conf:

    2. Now we need to open the file and make some changes in order to allow a remote connection. To open the file you’ve to use the keyword “nano” or you can run the command in the terminal that is provided below:

    sudo nano /etc/postgresql/13/main/postgresql.conf

    This command will open this file and in it, you need to search “listen_addresses” and add the following line.

    #listen_addresses = 'localhost' listen_addresses = '*'
    Change Listen Address in PostgreSQL

    In order to allow the users that we want to be connected to the database then we need to make changes in the “pg_hba.conf” file. This file will be available under the same directory as above.

    Now open the file using the command provided below:

    sudo nano /etc/postgresql/13/main/pg_hba.conf

    In the file you’ve to add the following lines in file:

    # TYPE DATABASE	USER	ADDRESS	METHOD host all	all 0.0.0.0/0 md5 host all all :/0 md5
    Allow Remote Hosts in PostgreSQL

    Save the configuration file and close it.

  2. Restart Service :

    3. Now, restart the database service to apply changes by executing the below-mentioned command:

    sudo systemctl restart postgresql

    Now simply open the port “5432” in the firewall and you’re all set to see all the databases and you can bond from whichever ip address to the server of PostgreSQL:

    sudo ufw allow 5432

That’s it. Your PostgreSQL database server is accessible from remote hosts.

Conclusion

PostgreSQL database is default set to bond with localhost which restricts the other IP address and host to connect or have the access to the PostgreSQL server. In this article, we guided you through the configuration of PostgreSQL to allow remote connection so that other IPs can bond to the server. In this way, other hosts can easily see the list of databases and connect to the PostgreSQL server remotely.

Source: https://blog.logrocket.com/setting-up-a-remote-postgres-database-server-on-ubuntu-18-04/

Article post on: us.suanoncolosence.com

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *