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

Mount StorageBox to the server for backup

-
  Quickly  solution Install cifs-utils: apt-get install cifs-utils Adjust and add the following lines to   crontab -e   : # reboot # This one should be moved to /etc/fstab @reboot /sbin/mount.cifs -o user=uXXXXXXXX,pass=xxxxxXXXXXX//uXXXXXX.your-storagebox.be/backup /mnt/storagebox_jobsites_backup/ SAMBA/CIFS You can mount your Storage Box via Samba/CIFS. You can use the following UNC path. If you are using your main account, the share name is  backup . If you are using a sub-account, you must use the username of the sub-account as the username and share name. Linux/Unix: //<username>.your-storagebox.de/<share_name> Windows \\<username>.your-storagebox.de\<share_name> If you use a FritzBox Router from AVM, you need to deactivate the NetBIOS filter for Samba/CIFS to work. Please check the AVM knowledge base for more information.  https://en.avm.de/service/knowledge-base/dok/FRITZ-Box-7590/835_Shared-files-and-printers-o...
Read more

psql: error: connection to server at "localhost" (127.0.0.1), port 5433 failed: ERROR: failed to authenticate with backend using SCRAM DETAIL: valid password not found

-
Image
ERROR psql: error: connection to server at "localhost" (127.0.0.1), port 5433 failed: ERROR: failed to authenticate with backend using SCRAM DETAIL: valid password not found Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authentication between client and Pgpool-II Authentication between Pgpool-II and PostgreSQL servers Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. Solution 1 If PostgreSQL servers require  MD5  or  SCRAM  authentication for some user’s authentication but the password for that user is not present in  pool_passwd , then enabling  allow_clear_text_frontend_auth  will allow the  Pgpool-II  to use clear-text-password authentication with user to get the password in plain text form from the user ...
Read more

Keeping Your SSH Connections Alive: Configuring ServerAliveInterval and ServerAliveCountMax

-
Image
Maintaining a stable SSH connection is essential when working on remote servers. Disconnections due to inactivity can interrupt workflows and be quite frustrating. Fortunately, you can configure SSH to send periodic keep-alive messages, preventing your connection from timing out. Step-by-Step Guide 1. Open the SSH Client Configuration File Use your preferred text editor to modify the SSH configuration file. For example, with  vim : sudo vim /etc/ssh/ssh_config If OpenSSH is installed via Homebrew, the configuration file might be located at: /usr/ local /etc/ssh/ssh_config 2. Modify the Configuration Add the following lines after the  Host *  directive to apply these settings to all SSH connections: ServerAliveInterval 240 ServerAliveCountMax 10 ServerAliveInterval : Specifies the interval (in seconds) at which the client sends keep-alive messages to the server. In this case, every 240 seconds. ServerAliveCountMax : Limits the number of keep-alive messages sent w...
Read more