0.Collection Job Group

The PostgreSQL Solution Collection Job Group is designed to collect high level summary information from targeted PostgreSQL Servers. This information is used by other jobs in the PostgreSQL Solution Set for further analysis and producing respective reports.

The jobs in the 0.Collection Job Group are:

• PgSQL_Configuration Job - Designed to return additional configuration settings from PostgreSQL servers
• PgSQL_SensitiveDataScan Job - Designed to discover sensitive data in PostgreSQL databases based on pre-defined or user-defined search criteria
• PgSQL_TablePrivileges Job - Designed to collect PostgreSQL table privileges from all the targeted servers.

Workflow

1. Set a Connection Profile for the 0.Collection Job Group with the permissions listed in the Recommended Configurations section. See the Connection topic for additional information.
2. For Sensitive Data Discovery Auditing – Ensure the Sensitive Data Discovery Add-On is installed on the Enterprise Auditor Console server.
3. Schedule the solution to run daily or as desired.
4. Review the reports generated by the jobs.

PgSQL_Configuration Job

The PgSQL_Configuration Job is designed to return additional configuration settings from PostgreSQL servers.

Queries for the PgSQL_Configuration Job

The PgSQL_Configuration Job uses the SQL Data Collector.

CAUTION: Do not modify the query. The query is preconfigured for this job.

The query is:

• PostgreSQL Database Sizing - Collects details about PostgreSQL databases. See the SQL Data Collector topic for additional information.

PgSQL_SensitiveDataScan Job

The PgSQL_SensitiveDataScan Job is designed to discover sensitive data in PostgreSQL databases based on pre-defined or user-defined search criteria.

Queries for the PgSQL_SensitiveDataScan Job

The PgSQL_SensitiveDataScan Job uses the SQL Data Collector.

The query is:

• PostgreSQL — Scans the PostgreSQL database for sensitive data. For configuring the SQL Data Collector, see the SQL Data Collector topic for additional information.

Configure the SensitiveDataScan Query

The PgSQL_SensitiveDataScan Job is preconfigured to run using the default settings for the Sensitive Data Collection category. Follow the steps to customize configurations.

Step 1 – Navigate to the Databases > 0.Collection > PostgreSQL > PgSQL_SensitiveDataScan > Configure node and select Queries.

Step 2 – In the Query Selection view, select the PostgreSQL query click on Query Properties. The Query Properties window appears.

Step 3 – Select the Data Source tab, and click Configure. The SQL Data Collector Wizard opens.

CAUTION: Do not make changes to other wizard pages as they have been pre-configured for this job.

Step 4 – To modify sensitive data scan options, navigate to the SQL Data Collector page. Select the desired scan options.

NOTE: The Sensitive Data Scan Settings are pre-configured for optimal performance for a high-level table scan. Configuring these settings to increase the scope of the sensitive data scan may significantly increase scan time.

Step 5 – To modify criteria, navigate to the SQL Data Collector page. By default, the Sensitive Data Scan job is configured to scan for criteria configured in the Global Criteria settings. See the Sensitive Data Criteria Editor topic for additional information.

Step 6 – PostgreSQL databases must be added to the query before they can be scanned. Navigate to the Filter page and click Connections to open the Manage Connections window.

Step 7 – In the Manage Connections window, click New Connection and add the following information:

• Is Active Checkbox — Check to include the database on the Servers Pane on the Filter page.
• Instance Label — The name of the instance
• Database System — Select PostgreSQL from the dropdown list
• Host — Name or IP address of the host where the database is located
• Port Number — Port number for the database. The default port for PostgreSQL is 5432

Exit the Manage Connections window to return to the Filter page.

Step 8 – On the Filter page, the query is configured by default to target Only select database objects. Click Retrieve. The Available database objects box will populate. The default filter will scan all PostgreSQL databases returned, excluding the listed system or default schemas and tables in red. Databases and instances can be added in the following ways:

• Select the desired database objects and click Add.
• Use the Import CSV button to import a list from a CSV file, if desired.
• Use the Add Custom Filter button to create and apply a custom filter.

Step 9 – Navigate to the Summary page, click Finish to save any setting modifications or click Cancel if no changes were made. Then click OK to close the Query Properties window.

The PgSQL_SensitiveDataScan Job is now ready to run with the customized settings.

Anaylsis Tasks for the PsgSQL_SensitiveDataScan Job

Navigate to the Databases > 0.Collection > PostgreSQL > PgSQL_SensitiveDataScan > Configure node and select Analysis to view the analysis tasks.

CAUTION: Do not modify or deselect the selected analysis tasks. The analysis tasks are preconfigured for this job.

The default analysis tasks are:

• Bring SA_SQL_Instances to View — Displays the SA_SQL_Instances table
• PostgreSQL SDD Matches View — Bring the PostgreSQL SDD Matches View to the SA console
• PostgreSQL SDD Match Hits View — Bring the PostgreSQL SDD Match Hits View to the SA console
• PostgreSQL SDD AIC Import — Imports PostgreSQL SDD into the AIC

PgSQL_TablePrivileges Job

The PgSQL_TablePrivileges job is designed to collect PostgreSQL table privileges from all the targeted servers.

Queries for the PgSQL_TablePrivileges Job

The PgSQL_TablePrivileges Job uses the SQL Data Collector for queries.

CAUTION: Do not modify the query. The query is preconfigured for this job.

The query is:

• Table Privileges - Returns table privileges from all the targeted servers.

Analysis Task for the PgSQL_TablePrivileges Job

Navigate to the Databases > 0.Collection > PostgreSQL > PgSQL_TablePrivileges > Configure node and select Analysis to view the analysis tasks.

CAUTION: Do not modify or deselect the selected analysis task. The analysis task is preconfigured for this job.

The default analysis task is:

• AIC Import - PostgreSQL Permissions – Imports PostgreSQL permissions to the AIC.
• AIC Import - Databases – Imports PostgreSQL database and schema nodes to the AIC.

PostgreSQL Solution

Data privacy and security is quickly evolving to be on equal footing with traditional security measures focused on the network, hardware, or software the data is contained within. Organizations aligning to concepts like Data-Centric Audit and Protection (DCAP) as defined by Gartner, or the requirements of strict compliance regulations like EU GDPR, are looking to implement processes that help them understand where sensitive data is stored, who or what is leveraging their privileges to access the data, and how each database has been configured.

As part of Netwrix comprehensive Data Access Governance suite for structured and unstructured data, Enterprise Auditor for PostgreSQL automates the process of understanding where PostgreSQL databases exist and provides an overview of the PostgreSQL environment in order to answer questions around data access:

• Who has access to your data?
• Where is sensitive data being stored?

With visibility into every corner of PostgreSQL, organizations can proactively highlight and prioritize risks to sensitive data. Additionally, organizations can automate manual, time-consuming, and expensive processes associated with compliance, security, and operations to easily adhere to best practices that keep PostgreSQL Server safe and operational.

Supported Platforms

• Open Source PostgreSQL 9x through 12x
• Enterprise DB PostgreSQL (10x trhough 12x)
• Amazon AWS Aurora PostgreSQL Engine (all versions supported by Amazon AWS)
• Azure PostgreSQL (9.6)

Requirements, Permissions, and Ports

See the Target PostgreSQL Requirements, Permissions, and Ports topic for additional information.

Sensitive Data Discovery Considerations

The Sensitive Data Discovery Add-On must be installed on the Enterprise Auditor Console server, which enables Sensitive Data criteria for scans. If running Sensitive Data Discovery (SDD) scans, it will be necessary to increase the minimum amount of RAM. Each thread requires a minimum of 2 additional GB of RAM per host. For example, if the job is configured to scan 8 hosts at a time , then an extra 16 GB of RAM are required (8x2=16).

By default, the job is configured to use 10 threads, which can be adjusted based on available resources on the Enterprise Auditor server.

NOTE: The Sensitive Data Discovery Add-on installation package installs the appropriate JDK (Java) version on the server. The JDK deployed is prepackaged and does not require any configuration; it has been preconfigured to work with Enterprise Auditor and should never be customized through Java. It will not conflict with other JDKs or Java Runtimes in the same environment.

Location

The Structured Sensitive Data Discovery License is required to run the PostgreSQL Solution. It can be installed from theEnterprise Auditor Instant Job Wizard. Once it has been installed into the Jobs tree, navigate to the solution: Jobs > Databases > PostgreSQL.

The 0.Collection Job Group performs sensitive data discovery and collects information on database configurations on the target hosts. The other job groups analyze and report on the data collected by the 0.Collection Job Group.

The Database Solution license includes all supported database platforms supported by Enterprise Auditor. Additionally, the Sensitive Data Discovery Add-On enables the solution to search database content for sensitive data.

Job Groups

The Enterprise Auditor PosgreSQL Solution Set is a set of pre-configured audit jobs and reports that provides visibility into PostgreSQL Sensitive Data.

The job groups in the PostgreSQL Solution are:

• 0.Collection Job Group - Designed to collect high level summary information from targeted PostgreSQL Servers. This information is used by other jobs in the PostgreSQL Solution Set for further analysis and producing respective reports
• Configuration > PgSQL_DatabaseSizing Job - Designed to provide insight into details about the PostgreSQL environment and potential vulnerabilities related to instance configuration settings
• Sensitive Data Job Group - Designed to provide insight into where sensitive data exists and who has access to it across all the targeted PostgreSQL databases

Configuration > PgSQL_DatabaseSizing Job

The Configuration Job Group is designed to provide insight into details about the PostgreSQL environment and potential vulnerabilities related to instance configuration settings.

The job in the Configuration Job Groups is:

• PgSQL_DatabaseSizing Job - Provides details about PostgreSQL databases and overall database size

Analysis Tasks for the PgSQL_DatabaseSizing Job

Navigate to the Jobs > Databases > PostgreSQL > Configuration > PgSQL_DatabaseSizing > Configure node and select Analysis to view the analysis tasks.

CAUTION: Do not modify or deselect the selected analysis tasks. The analysis tasks are preconfigured for this job.

The default analysis tasks are:

• Database Sizing Details - Provides details about PostgreSQL databases and sizing
• Database Sizing Summary - Summarizes PostgreSQL database sizing by host

In addition to the tables and views created the analysis task, the PgSQL_DatabaseSizing Job produces the following pre-configured report.

Report	Description	Default Tags	Report Elements
Database Sizing	This report highlights the size of databases in PostgreSQL	None	This report is comprised of three elements: - Bar Chart – Displays top databases by size (MB) - Bar Chart – Displays sizes by host (GB) - Table – Provides database details

Recommended Configuration for the PostgreSQL Solution

The jobs in the PostgreSQL Solution has been configured to inherit down from the PostgreSQL > Settings node. However, it is best practice to assign the host list and the Connection Profile at the data collection level, 0.Collection Job Group. Once these are assigned to the job group, it can be run directly or scheduled.

Dependencies

• .Active Directory Inventory Job Group run successfully
• For Sensitive Data Discovery Auditing – Sensitive Data Discovery Add-On installed on the Enterprise Auditor Console server
• For AWS RDS and Aurora instances, right-click a job in the PostgreSQL > 0.Collection folder and open the properties window. Select the Performance tab and ensure that the Skip Hosts that do not respond to PINGcheckbox is not selected.

Targeted Host(s)

• The 0.Collection Job Group must be set to run against a custom host list containing the PostgreSQL database instances / clusters.
• For AWS RDS instances, specify the endpoint when creating a host list. This value may change after saving the list if the instance is part of a cluster.

Connection Profile

The SQL Data Collector requires a specific set of permissions. For the PostgreSQL Solution, the credentials configured in the Connection Profile must be able to access the PostgreSQL Database. See the Connection topic for additional information on permissions and creating a SQL custom connection profile.

The Connection Profile is set to Use the Default Profile, as configured at the global settings level. However, since this may not be the Connection Profile with the necessary permissions for the assigned hosts, click the radio button for the Select one of the following user defined profiles option and select the appropriate Connection Profile drop-down menu.

Schedule Frequency

Daily

Run Order

The 0.Collection Job Group must be run first before running the other jobs and job groups.

RECOMMENDED: Run the solution at the top level: PostgreSQL Job Group

Query Configuration

This solution is designed to be run with the default query configurations. However, the PostgreSQL_SensitiveDataScan Job query can be customized as needed. See the Configure the SensitiveDataScan Query topic for additional information.

Analysis Configuration

This solution should be run with the default analysis configurations. These analysis tasks are preconfigured and should not be modified or deselected!

Disabling obsolete or run-desired jobs allows the solution to run more efficiently. To disable a job or job group, right-click on the item and select Disable Job.

RECOMMENDED: Do not delete any jobs. Instead, jobs should be disabled.

Sensitive Data Job Group

The Sensitive Data Job Group is designed to provide insight into where sensitive data exists and who has access to it across all the targeted PostgreSQL databases.

The job in the Sensitive Data Job Group is:

• PgSQL_SensitiveData Job - Designed to provide information on all the sensitive data that was discovered in the targeted PostgreSQL servers based on the selected scan criteria
• PgSQL_SensitiveDataPermissions Job - Designed to provide information on all types of permissions on database objects containing sensitive data across all the targeted PostgreSQL servers based on the selected scan criteria.

PgSQL_SensitiveData Job

The PsSQL_SensitiveData Job is designed to provide information on all the sensitive data that was discovered in the targeted PostgreSQL servers based on the selected scan criteria.

Analysis Tasks for the PgSQL_SensitiveData Job

Navigate to the Jobs > Databases > PostgreSQL > Sensitive Data > PgSQL_SensitiveData > Configure node and select Analysis to view the analysis tasks.

CAUTION: Do not modify or deselect the selected analysis tasks. The analysis tasks are preconfigured for this job.

The default analysis tasks are:

• Sensitive Data Details - Returns details around sensitive data in PostgreSQL
• Database Summary - Summarizes sensitive data in PostgreSQL by database
• Enterprise Summary - Summarizes PostgreSQL sensitive data for the organization

In addition to the tables and views created the analysis task, the PgSQL_SensitiveData Job produces the following pre-configured report.

Report	Description	Default Tags	Report Elements
Enterprise Summary	This report shows a summary of the criteria matches found in the Enterprise	None	This report is comprised of two elements: - Bar Chart – Displays exceptions by match count - Table – Provides exception details
Sensitive Data Overview	This report highlights objects which contain sensitive data crtieria.	Sensitive Data	This report is comprised of three elements: - Bar Chart - Displays top databases by Sensitive Data Hits - Table - Provides information on databases with sensitive data - Table - Provides details on sensitive data

PgSQL_SensitiveDataPermissions Job

The PgSQL_SensitiveDataPermissions Job is designed to provide information on all types of permissions on database objects containing sensitive data across all the targeted PostgreSQL servers based on the selected scan criteria.

Analysis Tasks for the PgSQL_SensitiveData Job

Navigate to the Jobs > Databases > PostgreSQL > Sensitive Data > PgSQL_SensitiveDataPermissions > Configure node and select Analysis to view the analysis tasks.

CAUTION: Do not modify or deselect the selected analysis tasks. The analysis tasks are preconfigured for this job.

The default analysis tasks are:

• Sensitive Data Permission Details – Creates the PgSQL_SensitiveDataPermissions_Details table accessible under the job’s Results node
• Sensitive Data Permissions Database Summary – Creates the PgSQL_SensitiveDataPermissions_DatabaseSummary table accessible under the job’s Results node

In addition to the tables and views created by the analysis tasks, the PgSQL_SensitiveDataPermissions Job produces the following pre-configured report.

Report	Description	Default Tags	Report Elements
Sensitive Data Permissions	This report highlights sensitive data permissions in the audited environment.	None	This report is comprised of three elements: - Bar Chart – Displays top databases by permission count - Table – Provides details on database permission summary - Table – Provides details on sensitive data permission details