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.
Functionality |
Supported Data Objects |
Crawler |
Tables Columns Relationships Views Stored Procedures Functions Triggers Roles Users Permissions Triggers Usage statistics |
Profiler |
Table Profiling: Row Count, Columns Count, View Sample Data View Profiling: Row Count, Columns Count, View Sample Data Column Profiling: Min, Max, Null Count, Distinct, Top 50 Values Full Profiling Sample Profiling |
Lineage |
Table Lineage Column Lineage Lineage Sources: Stored Procedures, Functions, Triggers, Views, SQL Queries (from Query Sheet), Query Logs. |
Query |
Select Joins within Database Aggregations Group By Order By |
Prerequisites
The following are the prerequisites required for establishing a connection:
- Driver Details.
- Service Account Permission.
- 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
Driver |
Version |
Details |
---|---|---|
JDBC Driver |
42.2.14 |
https://mvnrepository.com/artifact/org.postgresql/postgresql/ Note: The latest version is 42.2.23 |
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:
Operation |
Minimum Access Permission |
|
Connection Validation |
READ |
|
Crawling |
Tables & Views |
SELECT |
Procedures & Functions Source Code |
SELECT |
|
Relationships |
REFERENCES |
|
View Source Code |
OWNERSHIP |
|
Profiling |
SELECT |
|
Lineage |
SELECT |
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
- Navigate to Administration > System Settings.
- Select the Connector tab.
- Find the Key name “connector.environment”.
- Enter the desired environment values (PROD, STG) in the value column.
- 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:
- Log into the OvalEdge application.
- Navigate to the Administration module in the left menu and select the Connectors sub-module. The Connectors Information page will then be displayed.
- Click on the ‘+’ New Connector. The Add Connector pop-up window is displayed.
- 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
- Save & Validate Connector details.
- Click on the ‘Validate’ to validate the connector details.
- Click 'Save' to save the connector.
- Click ‘Save & Configure' for Connectors needing settings configuration.
Notes:
- * (asterisk) indicates the mandatory field required to create a connection. Once the connection is validated and saved, it will be displayed on the Connectors home page.
- You can either save the connection details first, or you can validate the connection first and then save it.
Connection Validation Errors
Sl.No |
Error Message(s) |
Description |
1 |
Host Name Incorrect |
The provided database instance URL is not valid. |
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.
Connection Settings |
Description |
---|---|
Crawler |
Crawler settings are configured to connect to a data source and collect and catalog all the data elements in the form of metadata. |
Profiler |
It is the process of gathering statistics and informative summaries about the connected data source(s). Statistics can help assess the data source's quality before using it in an analysis. Profiling is always optional; crawling can be run without profiling. |
Data Access |
The Data Access Authorization is included in the crawler-specific connector settings to ensure that the right user is accessing the query sheet and queries in the data catalog. Here the system validates the user credentials and allows that particular user to access the query sheet and queries in the data catalog. |
Query Policies |
It restricts the use of the selected query types based on your role. |
Access Instruction |
Access Instruction allows the data owner to instruct others on using the objects in the application. |
Business Glossary Settings |
The Business Glossary setting provides flexibility and control over how they view and manage term association within the context of a business glossary at the connector level. |
Notification |
The Enable/Disable Metadata Change Notifications option is used to set the change notification about the metadata changes of the data objects.
|
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.
- Navigate to the Connectors page, and click the Crawl/Profile option.
- Select the required Schema(s).
- 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
What should I know when upgrading my driver?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:
JAR |
JDBC Specification |
JDK Version |
---|---|---|
https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.14/PostgreSQL-442.2.14.jar |
JDBC 42.2.x |
JDK 8.0 |
How much does the driver cost?
The Microsoft JDBC Driver for PostgreSQL is available at no additional charge.