PostgreSQL :- Permit Network Access

Background

It is time to allow network access to our PostgreSQL server.

Outline

  1. Server
    • Configuration ( Files )
      • postgresql.conf
        • IP Interface ( Listen address )
        • Port Number
          • Default :- 5432
      • pg_hba.conf ( PostgreSQL Client Authentication Configuration File )
        • Rules
    • Firewall
      • Host Firewall
      • Network Firewall
    • Reload Configuration
      • pg_hba configuration
        • Either of these options
          • Command Line Shell
            • psql
          • Function
            • pg_reload_conf
  2. Review Configuration

Steps

Server

Configuration ( Files )

postgresql.conf

IP Interface ( Listen address )
Code

Specify IP Interface that should be opened.

# what IP address(es) to listen on;
listen_addresses = '*'
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)

Explanation
  1. Please use
    • This allows us to use all network interfaces in the box
Network Port
Code
port = 5432				# (change requires restart)
Explanation
  1. Please keep 5432 
    • This is the default network port
  2. To better hide
    • Please use  a different network port

pg_hba.conf

PostgreSQL Client Authentication Configuration File
Default
# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

Our addition
#dadeniji 2019.08.08 03:18 pm
host    all             all             192.128.1.0/24            trust
Explanation
  1. We are allowing  :-
    • Type => host
      • Host Rule
    • Database => all
      • Access to all databases
    • USER => all
      • Access to all principals
    • Address => 192.168.1.0/24
      • Access to network address 192.168.1.1 thru 192.168.1.254
    • Method => Trust

Firewall

Firewall ( Host )

Please relax host related firewall rules

Firewall ( Network )

Please work with network Firewall rule to effect perimeter network rules

Reload Configuration

pg_hba.conf

Outline

Please try one of these options :-

  1. Command Line Shell
    • pg_ctl
    • psql

Tasks

PostgreSQL – utility – pg_ctl
Syntax

pg_ctl reload

OS :- Sample / OS Windows

pg_ctl reload -D ..\data

PostgreSQL – psql – command
Syntax
psql -c "select pg_reload_conf()"
Sample
psql -U postgres -U postgres -c "select pg_reload_conf()"

Review

Review configuration

pg_hba.conf

Version – version 10 and above
SQL

select *
from   pg_hba_file_rules 

Output

pg_hba_file_rules.01.20190808.0427PM.PNG

Version – up to version 9.6
SQL

select
	regexp_split_to_table
	(
	    pg_read_file
	    (
	       'pg_hba.conf'
	    )
	   , E'\\n'
	)
Output

pg_read_file.01.20190808.0432PM..PNG

References

A whole lot of help….

  1. PostgreSQL
    • Functions and Operators
      • String Functions and Operators
        Link
    • System Catalog
      • pg_hba_file_rules
        Link
  2. nixCraft
    • How Do I Enable remote access to PostgreSQL database server?
      Link
  3.  Heatware.net
    • PostgreSQL: How to reload config settings without restarting database
      Link
  4. Stack Exchange
    • Database Administrators
      • How do I query the running pg_hba configuration?
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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