PostgreSQL Connector

PostgreSQL is a Relational Database Management System (RDBMS) that supports different functions of SQL such as foreign keys, subqueries, and triggers.

OvalEdge uses a JDBC driver to connect to the data source, which allows users to crawl and profile data objects (Tables, Table Columns, etc.) and build Lineage.

Connector Capabilities

The following is the list of objects and data types supported by the PostgreSQL connector.

Prerequisites

The following are the prerequisites required for establishing a connection: 

1. Driver Details. 
2. Service Account Permission.
3. Configure Environment Variables (Optional). 

Driver Details

The connectivity to the PostgreSQL connector is performed via the JDBC Driver, and the versions used by the driver are given below

By default, the JDBC driver is provided, and in case the driver needs to be changed, add PostgreSQL drivers into the OvalEdge Jar path to communicate with the PostgreSQL database.

Service Account Permissions

The minimum privileges required for a service account user to crawl, profile the data, and build lineage are as follows:

Note:  By default, the service account provided for the connector will be used for any query operations. If the service account has write privileges, then Insert/Update/Delete queries can be executed.

Configure Environment Variables (Optional)

This section outlines the settings or instructions you need to be aware of before establishing a connection. If your environments have already been configured, you can skip this step.

Configure Environment Names
The Environment Names allow you to select the environment configured for the specific connector from the dropdown list in the Add Connector pop-up window.
To maintain consistency, consider crawling the same schema in both the staging (STG) and production (PROD) environments. The usual environments for crawling include PROD, STG, or Temporary, and may also encompass QA or other environments. Crawling a temporary environment is useful for schema comparisons, which can be deleted later, especially when assisting with application upgrades.

Steps to Configure the Environment 

1. Navigate to Administration > System Settings. 
2. Select the Connector tab.
3. Find the Key name “connector.environment”.
4. Enter the desired environment values (PROD, STG) in the value column. 
5. Click ✔ to save. 

Establish a Connection

To establish a connection with PostgreSQL, fill in the required fields with the relevant information in the Add Connector pop-up window:

1. Log into the OvalEdge application.
2. Navigate to the Administration module in the left menu and select the Connectors sub-module. The Connectors Information page will then be displayed.
3. Click on the ‘+’ New Connector. The Add Connector pop-up window is displayed.
4. Select the connection type as PostgreSQL. The Add Connector with PostgreSQL details is displayed.
   
   

    

   Fields	Details
   Connector Type	The selected connection type ‘PostgreSQL’ is displayed by default.  If necessary, you can change the connector type by selecting the desired connector type from the Connector Type dropdown. Based on the selected connector type, the associated fields for that type will be displayed.
   Credential Manager	Select the option from the drop-down list, where you want to save your credentials: OE Credential Manager: SQL Server connection is configured with the basic Username and Password of the service account in real-time when OvalEdge establishes a connection to the SQL Server. Users need to add the credentials manually if the database option is selected. HashiCorp: The credentials are stored in the HashiCorp database server and fetched from HashiCorp to OvalEdge.   AWS Secrets Manager: The credentials are stored in the AWS Secrets Manager database server and fetched from the AWS Secrets Manager to OvalEdge. Azure Key Vault: For more information, click here. For more information on Credential Manager, refer to Credential Manager
   License Add-Ons	All the connectors will have a Base Connector License by default that allows you to crawl and profile to obtain the metadata and statistical information from a data source.  OvalEdge supports various License Add-Ons based on the connector’s functionality requirements. Auto Lineage: Auto Lineage license enables the automatic construction of data object lineage for a connector with the Lineage feature. Data Quality: Data Quality license allows identification, reporting, and resolution of data quality issues for a connector with data quality support, utilizing DQ Rules/functions, Anomaly detection, Reports, and more.
   Connector Name*	Enter a Connection name for PostgreSQL. Users can specify a connection name to identify the PostgreSQL in OvalEdge Example: PostgreSQL_db
   Connector Environment	The environment dropdown menu allows you to select the environment configured for the connector from the dropdown list. For example, PROD, or STG (based on the configured items in the OvalEdge configuration for the connector.environment). The purpose of the environment field is to help you identify which connector is connecting what type of system environment (Production, STG, or QA).  Note: The steps to set up environment variables are explained in the Configure Environment Variables (Optional).
   Server*	Enter the fully qualified server name or IP address related to the PostgreSQL database.
   Port*	By default, the port number related to the PostgreSQL database is displayed. If needed, the port number can be modified. Note: The default port is 5432. If the port number changes, enter the updated port number.
   Database*	Enter the name of the database that users want to crawl.
   Driver*	By default, the driver details associated with the PostgreSQL database are displayed, and it is non-editable.
   Username*	A username is required to connect to the Oracle server. Enter the Service Account Name established to access the PostgreSQL environment.  Note: Sometimes, this field is autofill filled by the web browser with the current OvalEdge user login. Please enter the PostgreSQL Service Account name.
   Password*	Enter the Password to access the PostgreSQL Server.
   Connection String	A connection string configures the PostgreSQL connection object. Key-value pairs specify PostgreSQL connections in a connection string.  Set the Connection string toggle button to automatically get the details from the credentials provided. Alternatively, you can manually enter the string. Example: jdbc:postgresql://{server}:5432/{sid}
   Plugin Server	Enter the Server Name if the connection is running as the plugin.
   Plugin Port	Enter the port number on which the plugin is running.
   Default Governance Roles*	You can select a specific user or a  team from the governance roles (Steward, Custodian, Owner, GovernanceRole 4*, GovernanceRole 5*, GovernanceRole 6*) that gets assigned for managing the data asset.  Note: The drop-down list displays all the configurable roles (single user or a team) as per the configurations made in the OvalEdge Security | Governance Roles section.
   Admin Roles*	Select the required admin roles for this connector. Integration Admins: To add Integration Admin Roles, search for or select one or more roles from the Integration Admin options, then click the Apply button. The responsibilities of the Integration Admin include configuring crawling and profiling settings for the connector, as well as deleting connectors, schemas, or data objects. Security and Governance Admins: To add roles for security and governance admins, search for or select one or more roles from the list, then click the Apply button. The responsibilities of the Security and Governance Admin include:  Configuring role permissions for the connector and its associated data objects. Adding admins to set permissions for roles on the connector and its associated data objects. Updating governance roles. Creating custom fields. Developing Service Request templates for the connector. Creating Approval workflows for the templates.
   No of archive objects*	It is the number of last modifications made in the metadata data of a dataset at Remote/source. By default, the number of archive objects is set to disable mode. Click on the Archive toggle button and enter the number of objects you wish to archive. For example, if a user updates the count as 4, the connection is crawled. It will provide the last 4 changes that occurred in the remote/source of the connector. Users can observe these changes in the ‘version’ column of the ‘Metadata Changes’ module in the Advanced Tools.
   Select Bridge	With the OvalEdge Bridge component, any cloud-hosted server can connect with any on-premise or public cloud data sources without modifying firewall rules. A bridge provides real-time control that makes it easy to manage data movement between any source and destination. For more information, refer to Bridge Overview

   
   
   
5. Save & Validate Connector details.
   1. Click on the ‘Validate’ to validate the connector details.
   2. Click 'Save' to save the connector.
   3. Click ‘Save & Configure' for Connectors needing settings configuration.

Connection Validation Errors 

Note: If you have any issues creating a connection, please contact your assigned OvalEdge Customer Success Management (CSM) team.

Connector Settings 

Once the connection is established successfully, various settings are provided to fetch and analyze the information from the data source.  The connection settings include Crawler, Profiler, Data Access, Query Policies, Access Instruction, Business Glossary Settings, and Notification.

Note: For more information, refer to the  Connector Settings.

The Crawling of Schema(s)

You can use the Crawl/Profile option, which allows you to select the specific schemas for the following operations: crawl, profile, crawl & profile, or profile unprofiled. The defined run date and time are displayed to set for any scheduled crawlers and profilers. 

1. Navigate to the Connectors page, and click the Crawl/Profile option.
2. Select the required Schema(s).
3. Click on the Run button that gathers all metadata from the connected source into the OvalEdge Data Catalog.

Note: For more information on Scheduling, refer to Scheduling Connector.

Additional Information

The Microsoft JDBC Driver 7.4 supports the JDBC 4.2 and 4.3 (partially) specifications and includes three JAR class libraries in the installation package as follows:

How much does the driver cost?
The Microsoft JDBC Driver for PostgreSQL is available at no additional charge.