PostgreSQL

PostgreSQL is an open-source relational database management system.

Coverage for PostgreSQL

Category	Details
Supported versions	PostgreSQL, versions 14, 15, 16, 17 (self-managed, on-premise or in a cloud VM) Amazon Aurora with PostgreSQL compatibility Amazon RDS for PostgreSQL Azure Database for PostgreSQL Azure Database for PostgreSQL - Flexible Server Google AlloyDB for PostgreSQL Google Cloud SQL for PostgreSQL
Supported validation methods	Vector validation Fast record validation Full record validation Interval validation Key validation Custom validation

Supported data types in PostgreSQL

Character	Datatypes
Character	bpchar char text varchar
Numeric	bigint bigserial decimal double precision integer numeric real serial smallint smallserial
Date & Time	date time with time zone time without time zone timestamp with time zone timestamp without time zone
Rowid	ROWID UROWID
Boolean data	boolean
Monetary	money
UUID	uuid
Network address	cidr inet macaddr macaddr8

Connecting Validata to PostgreSQL

Validata connects to PostgreSQL databases using a connection profile that specifies your PostgreSQL server endpoint and authentication credentials. For information about connection profiles, see Managing connection profiles.

Validata uses standard JDBC connectivity to connect to PostgreSQL and supports username and password authentication.

Prerequisites

Before creating a PostgreSQL connection profile, ensure the following requirements are met:

A PostgreSQL server instance accessible from the Validata server.
Network connectivity to the PostgreSQL server on the configured port (default 5432).
A PostgreSQL user account with appropriate database permissions.

The PostgreSQL user account must have the following privileges on the databases and tables being validated:

SELECT — Allows read access to table data.
USAGE — Allows access to schemas containing the tables to be validated.

Ensure the PostgreSQL server's pg_hba.conf file is configured to allow connections from the Validata server's IP address using the appropriate authentication method.

PostgreSQL connection profile parameters

When you create a connection profile for PostgreSQL, you configure the parameters described in this section.

The following image shows the connection profile configuration for PostgreSQL:

The following table describes each connection profile parameter for PostgreSQL:

Parameter	Required	Description	Example value
Connection Profile Name	Yes	A unique name that identifies this connection profile.	acme_postgres_prod_profile
Endpoint	Yes	The external data system type. Select `PostgreSQL`.	PostgreSQL
Host Name	Yes	The hostname or IP address of the PostgreSQL server.	postgres-prod.acme.internal
Port	Yes	The PostgreSQL server port. The default port is 5432.	5432
Database Name	Yes	The name of the PostgreSQL database that Validata connects to.	acme_analytics_db
Username	Yes	The PostgreSQL username with SELECT and USAGE privileges on the target database.	validata_user12
Password	Yes	The password for the PostgreSQL user. Validata stores this value securely.	(stored securely)
Connection URL (JDBC)	Yes	The JDBC connection URL for PostgreSQL. You can include additional parameters for SSL, timeouts, and other connection settings.	jdbc:postgresql://postgres-prod.acme.internal:5432/acme_analytics_db

Configuring a PostgreSQL connection profile

To configure a PostgreSQL connection profile:

Set Endpoint to PostgreSQL.
Enter the Host Name or IP address of your PostgreSQL server.
Enter the Port number (default is 5432).
Enter the Database Name to connect to.
Enter the Username and Password for authentication.
Enter the Connection URL (JDBC) with appropriate parameters.

JDBC connection URL formats

The JDBC connection URL specifies how Validata connects to the PostgreSQL server. You can use different URL formats depending on your security and configuration requirements.

The following table describes the available JDBC connection URL formats:

Connection type	URL format	Example
Standard	jdbc:postgresql://<HOST>:<PORT>/<DATABASE_NAME>	jdbc:postgresql://192.168.1.200:5432/VALIDATA_DB

The following table describes common JDBC connection URL parameters:

Parameter	Description
connectTimeout	The timeout value in seconds for establishing a connection. Use this parameter to avoid long waits when the server is unreachable.
ApplicationName	The application name reported to the PostgreSQL server. This value appears in `pg_stat_activity` and server logs, which is useful for monitoring and troubleshooting.
currentSchema	Sets the default schema for the connection. Use this parameter when the target tables are not in the `public` schema.

Best practices

Follow these recommendations when configuring PostgreSQL connection profiles:

Assign read-only privileges (SELECT, USAGE) to the PostgreSQL user account used for Validata validation. Avoid granting broader permissions than necessary.
Store credentials securely in a vault or secrets manager. Do not hardcode credentials in configuration files or scripts.
Regularly rotate credentials and enforce strong password policies for PostgreSQL user accounts.
Ensure the PostgreSQL server's pg_hba.conf file allows connections from the Validata server's IP address using the appropriate authentication method (such as md5 or scram-sha-256).
Use the ApplicationName parameter to identify Validata connections in PostgreSQL monitoring tools and logs.
Set appropriate connectTimeout values to prevent long waits when network issues occur.