Prepairing Data Sources: PostgreSQL

Preparing PostgreSQL for connectivity is a critical prerequisite for stable and secure process analytics. Proper server configuration—covering network access, authentication rules, role-based permissions, and optional SSL encryption—ensures that external tools like Noreja can reliably access data without compromising security or performance. A well-prepared PostgreSQL setup reduces integration friction, prevents connectivity issues, and establishes a robust foundation for scalable, production-grade analytics workflows.

Overview

Below is a comprehensive checklist for PostgreSQL administrators to prepare a server for the connectivity to Noreja. It covers server-side configuration, authentication, network and firewall rules, role setup, SSL, and testing. Citations reference the official PostgreSQL documentation and other trusted sources. To allow external tools to connect, you must enable TCP/IP listening, configure client authentication rules, open firewall ports, create dedicated roles/users with least-privilege access, optionally enforce SSL encryption, and verify the setup. Each step ensures secure, reliable connectivity.

Enable Remote TCP/IP Connections

By default, PostgreSQL listens only on the local loopback. To accept external connections:

Edit postgresql.conf

Locate listen_addresses (default localhost) and change it to allow desired interfaces.

listen_addresses = '*'      # listen on all IPv4 interfaces
# or
listen_addresses = '0.0.0.0' # equivalent, all IPv4 addresses

Confirm or set the TCP port (port = 5432 by default). postgresql.org

Restart or Reload the Server

pg_ctl restart
# or
systemctl restart postgresql

A restart is required to apply listen_addresses changes netiq.com.

Configure Client Authentication (`pg_hba.conf`)

Client authentication rules live in pg_hba.conf. Each line specifies who can connect, from where, and how.

Open pg_hba.conf (typically in /etc/postgresql/<version>/main/ or data/ directory).

Add host entries for your clients, for example:

# Allow any user from 192.168.1.0/24 with password (md5) authentication
host    all     all     192.168.1.0/24    md5

Use hostssl to require SSL-encrypted connections. postgresql.org
Restrict CIDR ranges to just the needed client subnets for security.

Reload Configuration This avoids a full restart and immediately applies pg_hba.conf changes netiq.com.

pg_ctl reload
# or
SELECT pg_reload_conf();

Open Firewall / Network ACLs

Ensure your network allows traffic on PostgreSQL’s port:

Allow TCP Port 5432

On Linux (firewalld):

firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload

On Windows Firewall: add inbound rule for TCP 5432. bigbinary.com

Test Port Reachability

telnet <server_ip> 5432
# or PowerShell
Test-NetConnection -ComputerName <server_ip> -Port 5432

Successful connection confirms the port is open bigbinary.com.

Select Authentication Mode

PostgreSQL supports several authentication methods:

Password-based (md5, scram-sha-256) – common for third-party tools.
Peer – local Unix user matching database role.
GSSAPI/SSPI – for Kerberos/Active Directory.
Cert – client-certificate authentication over SSL.

Configure methods per-host in pg_hba.conf. For example, to require SCRAM for all external hosts:

host    all     all     0.0.0.0/0    scram-sha-256

Create Roles and Grant Permissions

Follow least-privilege principles when provisioning an external-tool account:

Create a Login Role

CREATE ROLE tool_user
  WITH LOGIN
  PASSWORD 'StrongP@ssw0rd'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  CONNECTION LIMIT 10;

Grant Database Access

GRANT CONNECT ON DATABASE your_db TO tool_user;

Grant Schema/Table-Level Permissions

GRANT USAGE ON SCHEMA public TO tool_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tool_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO tool_user;

Adjust for INSERT/UPDATE/EXECUTE as required.

Role Membership (Optional)
Use group roles for permission management:

CREATE ROLE reporting_grp;
GRANT reporting_grp TO tool_user;

Future tools/users can be added to reporting_grp postgresql.org.

(Optional) Enforce SSL Encryption

To secure data in transit:

Install a Server Certificate

Place your PEM certificate/key in the data directory.

In postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'

Require SSL in pg_hba.conf

hostssl   all   all   0.0.0.0/0   md5

Only hostssl lines match SSL-enabled connections postgresql.org.

Reload and Test

pg_ctl reload
psql "host=<server> sslmode=require dbname=your_db user=tool_user"

Verify Connectivity

Finally, validate end-to-end connectivity:

Connect Using psql

psql -h <server_ip> -U tool_user -d your_db -W

Test Sample Query

SELECT now(), count(*) FROM information_schema.tables;

Check Server Logs
Look for successful authentication entries in postgresql.log.

By following these steps—enabling network listening, securing client authentication, opening firewall ports, creating dedicated roles, enforcing encryption, and thorough testing—DBAs will ensure external tools can connect securely and reliably to PostgreSQL instances.