Allow Remote Connection to PostgreSQL / no pg_hba.conf entry for host ***.**.**.*

-

 When you install PostgreSQL, by default connection to the database using TCP/IP is not allowed.

When you try to connect from a client to a remote PostgreSQL database using psql command, you might get a psql: could not connect to server: Connection refused error message.

In the following example, from a client machine, we are trying to connect to a PostgreSQL database that is running on 66.249.75.34 server. As you see from the output, it clearly says that the remote PostgreSQL database is not accepting connection.

# psql -U postgres -h 66.249.75.34
psql: could not connect to server: Connection refused
        Is the server running on host "66.249.75.34" and accepting
        TCP/IP connections on port 5432?

To enable TCP/IP connection for PostgreSQL database, you need to follow the two steps mentioned below.

1. Modify pg_hba.conf to Add Client Authentication Record

On the PostgreSQL database server, by default, you’ll notice the following records towards the end of the /var/lib/pgsql/data/pg_hba.conf. As indicated below, it accepts connections only from the localhost.

# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               ident

Add the following line to the pg_hba.conf server. This will allow connection from 192.0.2.255 IP address (This is the client in our example). If you want to allow connection from multiple client machines on a specific network, specify the network address here in the CIDR-address format.

# vi  /var/lib/pgsql/data/pg_hba.conf
host    all             all            192.0.2.255/24              scram-sha-256

Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# vi  /var/lib/pgsql/data/pg_hba.conf
host    all             all             0.0.0.0/0               scram-sha-256

The following are various client authentication record formats supported in the pg_hba.conf file. We are using the #2 format from this list:

  • local database user authentication-method [authentication-option]
  • host database user CIDR-address authentication-method [authentication-option]
  • hostssl database user CIDR-address authentication-method [authentication-option]
  • hostnossl database user CIDR-address authentication-method [authentication-option]

Instead of “CIDR-address” format, you can also specify the IP address and the network mask in separate fields using the following record format:

  • host database user IP-address IP-mask authentication-method [authentication-option]
  • hostssl database user IP-address IP-mask authentication-method [authentication-option]
  • hostnossl database user IP-address IP-mask authentication-method [authentication-option]

2. Change the Listen Address in postgresql.conf

On the PostgreSQL database server, by default, the listen address will be localhost in the postgresql.conf file as shown below.

# grep listen /var/lib/pgsql/data/postgresql.conf
listen_addresses = 'localhost'

Modify this line and give *. If you have multiple interfaces on the server, you can also specify a specific interface to be listened.

# grep listen /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'

3. Test the Remote Connection

Now, log in to the client machine 66.249.75.34, and perform the psql remote connection to the PostgreSQL database server (66.249.75.34) as shown below. This time, it should work.

# psql -U postgres -h 66.249.75.34
psql (13.4, server 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#

These steps should enable remote TCP/IP connections to the PostgreSQL database server from the specified client machine.

Comments

Post a Comment

Popular posts from this blog

Installing the Certbot Let’s Encrypt Client for NGINX on Amazon Linux 2

-
Image
Installing the Certbot Let’s Encrypt Client The certbot package is not available through the package manager by default. You will need to enable the  EPEL  repository to install Certbot. sudo yum install epel-release sudo yum install certbot-nginx Set Up NGINX certbot  can automatically configure  NGINX  for SSL/TLS. It looks for and modifies the server block in your  NGINX  configuration that contains a server_name directive with the domain name you’re requesting a certificate for. In our example, the domain is  www.mydomain.com . Assuming you’re starting with a fresh NGINX install, use a text editor to create a file in the  /etc/nginx/conf.d  directory named  domain_name.conf  (so in our example, www.mydomain.com.conf). Specify your domain name (and variants, if any) with the  server_name  directive: server { listen 80 default_server; listen [::]:80 default_server; root /var/www/html; server_name myd...
Read more

Deploy Nuxt.js app using Apache 2

-
  Deploy your dynamic Nuxt.js app Nuxt.js is an amazing server side rendering application framework for Vue.js. In this article we will see how to deploy Nuxt.js application to AWS instance in universal app mode. Where in Nuxt.js you can choose between Universal, Static Generated or Single Page application. Before we start deploying our dynamic app, if you want just to deploy a static website, you can run this command: npm run generate and it will generate the files and put them in  dist  folder which you can upload to any server you want. But this will not work for universal apps. 1. Build your app To build a ssr app you need to run the following command: npm run build The generated files will not be in the  dist  folder as for the static app, but you can find all generated files in  .nuxt  folder which will be the entry point. 2. Upload to the server There are two ways to do this upload your whole application with the source code or just upload the n...
Read more

Automate PostgreSql Backup with Cron

-
  Create The  Postgres Password File The file  .pgpass  in a user’s home directory can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named  %APPDATA%\postgresql\pgpass.conf  (where  %APPDATA%  refers to the Application Data subdirectory in the user’s profile). Alternatively, the password file to use can be specified using the connection parameter passfile or the environment variable  PGPASSFILE . Let’s see if the  postgres user  already has a password: sudo -u postgres psql select * from pg_authid; If  rolpassword  is empty, set the password: ALTER USER postgres PASSWORD 'mypassword'; \q Create  .pgpass vim ~/.pgpass # Add the following line # hostname:port:database:username:password localhost:*:*:postgres:[password] Add Environment variable  PGPASSFILE export PGPASSFILE= '/root/.pgpass' vim /etc/environment ...
Read more