PostgreSQL

Description

The PostgreSQL connector scans a PostgreSQL instance via JDBC (pgjdbc) to discover all database roles, their privilege attributes, and role membership hierarchy. In PostgreSQL, both users and groups are represented as roles — login roles are treated as user accounts, and non-login roles are treated as groups.

System Type Classification

Field	Value
System Type	Infrastructure
Default Scan Priority	500

Version Support

OrbisID Edition	Supported
Community	No
Pro	Yes
Enterprise	Yes

PostgreSQL scanning requires a Pro or Enterprise licence.

Supported Protocol

Protocol	Port	Notes
JDBC / PostgreSQL Protocol (TCP)	5432 TCP	Default; configurable per instance

What OrbisID Discovers

Data	Source
All roles (users and groups)	pg_catalog.pg_roles
Role attributes (superuser, createdb, etc.)	pg_catalog.pg_roles
Role memberships	pg_catalog.pg_auth_members
Login capability	rolcanlogin — determines user vs group
Account expiry	rolvaliduntil

Privileged Role Detection

Roles are flagged as privileged when any of the following attributes are set:

Attribute	Meaning
rolsuper	Superuser — bypasses all permission checks
rolcreaterole	Can create, alter, and drop other roles
rolreplication	Can initiate replication
rolbypassrls	Bypasses row-level security policies

Connection Requirements

Credential Requirements

Recommended: Create a dedicated PostgreSQL role for OrbisID with read access to the system catalogue:

-- Create the scanning role
CREATE ROLE orbisid_scanner WITH LOGIN PASSWORD '<strong-password>';

-- Grant access to system catalogue views
-- pg_roles and pg_auth_members are readable by any role with LOGIN by default.
-- No additional grants are required for basic discovery.

The default PostgreSQL permissions allow any authenticated role to read pg_catalog.pg_roles and pg_catalog.pg_auth_members. No superuser privileges are required for discovery.

Credential Mapping

OrbisID Field	PostgreSQL Value
credential.username	PostgreSQL role name
credential.password	PostgreSQL password

System Attributes

Attribute	Required	Default	Description
pgDatabase	No	postgres	Database to connect to. Role discovery reads from pg_catalog, which is accessible from any database.
pgSslMode	No	prefer	SSL mode for the JDBC connection. Values: disable, allow, prefer, require, verify-ca, verify-full.

Network Requirements

Requirement	Detail
TCP port 5432	Must be accessible from the OrbisID server (or On-Premise Agent)
pg_hba.conf	Must allow the scanning user to connect from the OrbisID server IP using password authentication (md5 or scram-sha-256)

Configuration Steps

1. Create the orbisid_scanner role in PostgreSQL
2. Edit pg_hba.conf to allow the scanning role to connect from the OrbisID server:

   host    postgres    orbisid_scanner    <orbisid-ip>/32    scram-sha-256
3. Run SELECT pg_reload_conf(); or restart PostgreSQL to apply the pg_hba.conf change
4. Create a Credential in OrbisID:
   • Username: orbisid_scanner
   • Password: The scanning role's password
5. Navigate to Systems → Add System
6. Fill in the fields:

Field	Value
Name	Descriptive name (e.g., PostgreSQL – prod-db01)
Hostname / IP	PostgreSQL host
Port	5432 (or custom port)
OS Type	PostgreSQL
System Type	Infrastructure
Credential	The scanning credential

7. Optionally, set pgDatabase and pgSslMode in system attributes
8. Click Test Connection to verify connectivity and authentication
9. Click Save

SSL in Production

Use pgSslMode=require or pgSslMode=verify-full in production to encrypt credentials in transit. This requires PostgreSQL to be configured with an SSL certificate.

RDS / Aurora PostgreSQL

For Amazon RDS or Aurora PostgreSQL, set pgSslMode=require and ensure the OrbisID server (or agent) can reach the RDS endpoint on port 5432. The scanning role needs no special RDS permissions beyond standard PostgreSQL catalogue access.

Troubleshooting

Symptom	Likely Cause	Resolution
password authentication failed	Wrong password or username	Verify the credential; check the role name matches exactly (case-sensitive)
Connection refused	Port blocked or PostgreSQL not listening on the expected address	Check listen_addresses in postgresql.conf; verify port 5432 is open
pg_hba.conf: no entry	Client IP not allowed in pg_hba.conf	Add an entry permitting the OrbisID server IP for the scanning role
database "X" does not exist	Wrong database in pgDatabase attribute	Set pgDatabase to an existing database (e.g., postgres)
SSL error	SSL not enabled on the server	Set pgSslMode=disable for testing; enable SSL on PostgreSQL for production
Roles discovered but no memberships	Roles have no memberships	This is expected if no GRANT role TO role statements have been executed