setting up remote access Postgres DB on Ubuntu

Postgres database if we read from wikipedia https://en.wikipedia.org/wiki/PostgreSQL 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. Currently in our Odoo ERP development we are more often now using Postgres database as default database for our Odoo. In default installation the database is only able to remote on localhost environment, and in this article i will show to you how to setting up remote access, so other IP or client can have access to remote.

Here are the steps :

  • Allow remote access

Note : In this step you need to update file postgresql.conf and pg_hba.conf

  1. postgresql.conf

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.

2. pg_hba.conf

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

  • Allow port 5432

Allow port 5432 through the firewall by executing:

sudo systemctl restart postgresql

  • Restart Postgres

Restart Postgres to apply all the changes you have made to its configuration by running:

sudo systemctl restart postgresql

  • Create a User for remoting access

Actually with all steps above, we already finished all the procedures to allow remote access. But like our case now, we need to create one user to our purpose for remoting. Here as steps to create user and granting that user for remoting :

sudo -u postgres psql

Create user, example user with username python

CREATE USER python with PASSWORD ‘python’;

Grant access this user, in this step we will use statement ALTER ROLE

ALTER ROLE python superuser;

To view the role use the following command:

\du python

Here are the following functions are available with ALTER ROLE statement:

  • SUPERUSER | NOSUPERUSER – It is used to determine if the role is a superuser.
  • VALID UNTIL ‘timestamp’ – It is used to specify the expiry date and time of a role’s password.
  • CREATEROLE | NOCREATEROLE –It is used to provide permissions to a role for creating or modifying roles.
  • PASSWORD ‘password’ | PASSWORD NULL – It is used to change a role’s password.
  • INHERIT | NOINHERIT – It is used to determine if the inherited role has all the inherited privileges of the parent role.
  • BYPASSRLS | NOBYPASSRLS – It is used to check if a role can bypass a row-level security (RLS) policy.
  • LOGIN | NOLOGIN – As the name suggests itself, it is used to allow the role to log in.
  • CONNECTION LIMIT limit – It is used to set the number of concurrent connections that a role can make. Here -1 means the role can create an unlimited number of rows.

The following are the set of rules that must be followed while using the ALTER ROLE statement:

  • Superusers can modify the attributes for any role.
  • Only non-superusers and no-replication roles can be modified is a role has CREATE ROLE attribute to it.
  • Ordinal roles can only change their passwords.

Knowledge Base :

Cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.