--- title: "Prepairing Data Sources: PostgreSQL" url: "https://docs.noreja.com/en/article/prepairing-data-sources-postgresql" language: "en" last_updated: "2026-02-28T11:31:28.984Z" --- # 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. ```plaintext 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](https://www.postgresql.org/docs/current/runtime-config-connection.html?utm_source=chatgpt.com) **Restart or Reload the Server** ```plaintext pg_ctl restart # or systemctl restart postgresql ``` A restart is required to apply `listen_addresses` changes [netiq.com](https://www.netiq.com/documentation/cloudmanager23/ncm23_install/data/bf9b2zv.html?utm_source=chatgpt.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//main/` or `data/` directory). **Add** `**host**` **entries** for your clients, for example: ```plaintext # 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](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html?utm_source=chatgpt.com) - 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](https://www.netiq.com/documentation/cloudmanager23/ncm23_install/data/bf9b2zv.html?utm_source=chatgpt.com). ```plaintext 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): ```plaintext firewall-cmd --add-port=5432/tcp --permanent firewall-cmd --reload ``` On Windows Firewall: add inbound rule for `TCP 5432`. [bigbinary.com](https://www.bigbinary.com/blog/configure-postgresql-to-allow-remote-connection?utm_source=chatgpt.com) **Test Port Reachability** ```plaintext telnet 5432 # or PowerShell Test-NetConnection -ComputerName -Port 5432 ``` Successful connection confirms the port is open [bigbinary.com](https://www.bigbinary.com/blog/configure-postgresql-to-allow-remote-connection?utm_source=chatgpt.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: ```plaintext 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** ```plaintext CREATE ROLE tool_user WITH LOGIN PASSWORD 'StrongP@ssw0rd' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT CONNECTION LIMIT 10; ``` **Grant Database Access** ```plaintext GRANT CONNECT ON DATABASE your_db TO tool_user; ``` **Grant Schema/Table-Level Permissions** ```plaintext 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: ```plaintext CREATE ROLE reporting_grp; GRANT reporting_grp TO tool_user; ``` Future tools/users can be added to `reporting_grp` [postgresql.org](https://www.postgresql.org/docs/current/role-membership.html?utm_source=chatgpt.com). ## (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`: ```plaintext ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ``` **Require SSL in** `**pg_hba.conf**` ```plaintext hostssl all all 0.0.0.0/0 md5 ``` Only `hostssl` lines match SSL-enabled connections [postgresql.org](https://www.postgresql.org/docs/current/ssl-tcp.html?utm_source=chatgpt.com). **Reload and Test** ```plaintext pg_ctl reload psql "host= sslmode=require dbname=your_db user=tool_user" ``` ## Verify Connectivity Finally, validate end-to-end connectivity: **Connect Using psql** ```plaintext psql -h -U tool_user -d your_db -W ``` **Test Sample Query** ```plaintext 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.