PostgreSQL Database Discovery

Tripl-i automatically discovers PostgreSQL instances and databases on Linux servers during SSH scans, providing complete visibility into your PostgreSQL infrastructure alongside other discovered assets.

Overview

When PostgreSQL is detected on a scanned Linux server, the scanner automatically gathers instance configuration, database inventory, active connections, replication status, and installed extensions. This data creates DatabaseInstance and Database CIs with full dependency mapping.

Key Benefits

• Complete Database Inventory - Discover all PostgreSQL instances and databases across your infrastructure
• Dependency Mapping - Identify which applications and servers connect to your databases
• Replication Visibility - Detect primary/standby configurations and streaming replication status
• Extension Tracking - Know which extensions (PostGIS, pg_stat_statements, etc.) are installed per database
• Vulnerability Management - CPE generation enables CVE tracking for PostgreSQL versions
• Capacity Planning - Track database sizes and connection counts

How It Works

Discovery Flow

Step	Action
1	Linux server is scanned via SSH
2	PostgreSQL detected via running processes (postgres), listening port (5432), or installed binary (psql)
3	Scanner authenticates to PostgreSQL (OS auth or provided credentials)
4	Instance configuration, database inventory, connections, replication, and extensions collected
5	DatabaseInstance and Database CIs created
6	Relationships mapped between instances, databases, and connected servers/workloads

Automatic Triggering

Database discovery is automatically triggered when:

• A Linux server is scanned via SSH
• A running postgres process is detected, or port 5432 is listening, or psql binary is found
• No additional configuration is required for servers where the SSH account has sudo access

Authentication

Default: OS Authentication (No Extra Credentials)

The scanner uses peer authentication via the postgres OS user:

sudo -u postgres psql

This works on most Linux installations where:

• The SSH scanning account has sudo access
• PostgreSQL is configured with default peer authentication in pg_hba.conf

No separate database credentials are required in this case.

Optional: Password Authentication

If sudo access is not available, you can configure PostgreSQL credentials in the Scanner GUI:

1. Go to Credentials tab
2. Select protocol: PostgreSQL
3. Enter Username and Password
4. Set Port (default: 5432)
5. Set IP Range to target the database server(s)

The scanner uses the PGPASSWORD environment variable for authentication, keeping passwords out of process listings.

Authentication Priority

Priority	Method	When Used
1	Provided credentials (PGPASSWORD)	When PostgreSQL credentials are configured
2	sudo -u postgres psql (peer auth)	When SSH user has sudo access
3	psql (current user)	Fallback if peer auth is configured for SSH user

Required PostgreSQL Permissions

For full discovery, the database user needs:

Permission	Purpose
CONNECT on databases	Access each database for extension discovery
pg_read_all_settings role	Read all configuration parameters
pg_read_all_stats role	Read activity and replication statistics

The default postgres superuser (via peer auth) has all these permissions automatically.

Data Collected

Instance Information

Data Point	Description
Version	PostgreSQL version (e.g., 16.1)
Port	Listening port (default 5432)
Data Directory	Location of database cluster files
Max Connections	Maximum allowed connections
Shared Buffers	Shared memory buffer size
Work Memory	Per-operation memory allocation
WAL Level	Write-ahead log level (replica, logical, minimal)
Archive Mode	Whether WAL archiving is enabled
Listen Addresses	Network interfaces accepting connections
SSL	Whether SSL is enabled
Is Replica	Whether this instance is a streaming replica
CPE	Common Platform Enumeration for vulnerability matching

Database Information

Data Point	Description
Database Name	Name of the database
Owner	Database owner role
Encoding	Character encoding (e.g., UTF8)
Collation	Default collation
Size	Total database size in MB
Tablespace	Default tablespace
Connection Limit	Max connections for this database (-1 = unlimited)
Is Template	Whether this is a template database
Frozen XID Age	Transaction ID age (vacuum health indicator)
Extensions	Installed extensions with versions

Connection Information

Data Point	Description
Client Address	IP address of connecting client
Client Hostname	Resolved hostname of client
Application Name	Application identifier (e.g., "pgAdmin", "Django")
Username	PostgreSQL role used for connection
Database	Target database
State	Connection state (active, idle, idle in transaction)
Connection Count	Number of connections from this client

Replication Information

Data Point	Description
Is Replica	Whether this instance is a standby server
Primary Host	Hostname/IP of the primary server (for replicas)

CI Types Created

DatabaseInstance

Represents a PostgreSQL instance (cluster) running on a server.

• Naming Convention: ServerName:Port (e.g., db-server-01:5432)
• Database Engine: postgresql

Database

Represents an individual database within a PostgreSQL instance.

• Naming Convention: ServerName:Port/DatabaseName (e.g., db-server-01:5432/production)

Relationships Created

Relationship	Source	Target	Description
Runs On	DatabaseInstance	Server	Instance runs on server
Part Of	Database	DatabaseInstance	Database belongs to instance
Uses Database	Server/Workload	DatabaseInstance	Client connects to database
Linked To	DatabaseInstance	DatabaseInstance	Replication partner

Cross-Boundary Dependencies

When a Kubernetes workload connects to a PostgreSQL server, the connection is resolved to the Workload CI:

K8s Workload (api-service) ──Uses Database──> DatabaseInstance (db-server-01:5432)

Vulnerability Management

CPE Generation

Format: cpe:2.3:a:postgresql:postgresql:{version}:*:*:*:*:*:*:*

Examples:

• PostgreSQL 16.1: cpe:2.3:a:postgresql:postgresql:16.1:*:*:*:*:*:*:*
• PostgreSQL 15.4: cpe:2.3:a:postgresql:postgresql:15.4:*:*:*:*:*:*:*

Best Practices

Security

• Use peer authentication when possible (no password storage needed)
• If using password auth, configure credentials per IP range (not wildcard)
• Monitor databases with archive_mode = off for disaster recovery gaps
• Review pg_hba.conf rules discovered in the scan for overly permissive access

Monitoring Indicators

Use discovered data to identify issues:

• High Frozen XID Age (>200 million) - autovacuum may be struggling
• Replicas with lag - streaming replication health issues
• Databases at connection limit - capacity constraints
• Missing extensions (e.g., pg_stat_statements not installed) - observability gaps

Troubleshooting

Issue	Cause	Solution
PostgreSQL not discovered	Process not running or different port	Check pg_isready on the target
Cannot connect	Peer auth not configured for scanner user	Configure PostgreSQL credentials in Scanner GUI
Missing databases	Insufficient permissions	Use postgres superuser or grant pg_read_all_stats
No connection data	pg_stat_activity access denied	Grant pg_read_all_stats role
No extension data	Cannot connect to individual databases	Grant CONNECT on all databases

Related Documentation

• SQL Server Database Discovery
• MySQL/MariaDB Database Discovery
• Oracle Database Discovery
• Credential Management