# Privacera Platform master publication

###### PostgreSQL
:

This topic covers how you can configure PostgreSQL PolicySync access control using Privacera Manager. Privacera supports the following PostgreSQL implementations:

• Amazon RDS PostgreSQL

• Amazon Aurora in PostgreSQL mode

• PostgreSQL

###### Prerequisites
• Create a database in PostgreSQL and get the database name and its URL:

• Create a database user granting all privileges to fully access the database, and then get the user credentials to connect to the database.

If you choose to enable audits for PolicySync, ensure the following prerequisites are met:

###### CLI Configuration
1. SSH to the instance where Privacera is installed.

2. Run the following commands.

cd ~/privacera/privacera-manager/config
cp sample-vars/vars.policysync.postgres.yml custom-vars/
vi custom-vars/vars.policysync.postgres.yml
3. Set the properties for your specific installation. For property details and description, see the Configuration Properties section that follows.

Note

Along with the above properties, you can add custom properties that are not included by default. For more information about these properties, see PostgreSQL Connector.

4. Run the following commands.

cd ~/privacera/privacera-manager
./privacera-manager.sh update
###### Configuration Properties

JDBC configuration

Table 22. JDBC configuration

Name

Type

Default

Required

Description

POSTGRES_JDBC_URL

string

Yes

Specifies the JDBC URL for the PostgreSQL connector.

Use the following format for the JDBC string:

jdbc:postgresql://<PG_SERVER_HOST>:<PG_SERVER_PORT>

POSTGRES_JDBC_USERNAME

string

Yes

Specifies the JDBC username to use.

POSTGRES_JDBC_PASSWORD

string

Yes

Specifies the JDBC password to use.

POSTGRES_JDBC_DB

string

privacera_db

Yes

Specifies the name of the JDBC database to use.

POSTGRES_DEFAULT_USER_PASSWORD

string

Yes

Specifies the password to use when PolicySync creates new users.

POSTGRES_OWNER_ROLE

string

No

Specifies the role that owns the resources managed by PolicySync. You must ensure that this user exists as PolicySync does not create this user.

• If a value is not specified, resources are owned by the creating user. In this case, the owner of the resource will have all access to the resource.

• If a value is specified, the owner of the resource will be changed to the specified value.

The following resource types are supported:

• Database

• Schemas

• Tables

• Views

Table 23. Load keys and intervals

Name

Type

Default

Required

Description

POSTGRES_LOAD_RESOURCES_KEY

string

load_from_database_columns

No

Specifies how PolicySync loads resources from PostgreSQL. The following values are allowed:

• load_md: Load resources from PostgreSQL with a top-down resources approach, that is, it first loads the databases and then the schemas followed by tables and its columns.

• load_from_database_columns: Load resources one by one for each resource type that is, it loads all databases first, then it loads all schemas in all databases, followed by all tables in all schemas and its columns. This mode is recommended since it is faster than the load mode.

POSTGRES_RESOURCE_SYNC_INTERVAL

integer

60

No

Specifies the interval in seconds for PolicySync to wait before checking for new resources or changes to existing resources.

POSTGRES_PRINCIPAL_SYNC_INTERVAL

integer

420

No

Specifies the interval in seconds for PolicySync to wait before reconciling principals with those in the data source, such as users, groups, and roles. When differences are detected, PolicySync updates the principals in the data source accordingly.

POSTGRES_PERMISSION_SYNC_INTERVAL

integer

540

No

Specifies the interval in seconds for PolicySync to wait before reconciling Apache Ranger access control policies with those in the data source. When differences are detected, PolicySync updates the access control permissions on data source accordingly.

POSTGRES_AUDIT_SYNC_INTERVAL

integer

30

No

Specifies the interval in seconds to elapse before PolicySync retrieves access audits and saves the data in Privacera.

Resources management

Table 24. Resources management

Name

Type

Default

Required

Description

POSTGRES_MANAGE_DATABASE_LIST

string

No

Specifies a comma-separated list of database names for which PolicySync manages access control. If unset, access control is managed for all databases. If specified, use the following format. You can use wildcards. Names are case-sensitive.

An example list of databases might resemble the following: testdb1,testdb2,sales db*.

If specified, POSTGRES_IGNORE_DATABASE_LIST takes precedence over this setting.

POSTGRES_MANAGE_SCHEMA_LIST

string

No

Specifies a comma-separated list of schema names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

Use the following format when specifying a schema:

<DATABASE_NAME>.<SCHEMA_NAME>

If specified, POSTGRES_IGNORE_SCHEMA_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all schemas are managed:

<DATABASE_NAME>.*

The specified value, if any, is interpreted in the following ways:

• If unset, access control is managed for all schemas.

• If set to none no schemas are managed.

POSTGRES_MANAGE_TABLE_LIST

string

No

Specifies a comma-separated list of table names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

Use the following format when specifying a table:

<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>



If specified, POSTGRES_IGNORE_TABLE_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all matched tables are managed:

<DATABASE_NAME>.<SCHEMA_NAME>.*

The specified value, if any, is interpreted in the following ways:

• If unset, access control is managed for all tables.

• If set to none no tables are managed.

POSTGRES_IGNORE_DATABASE_LIST

string

No

Specifies a comma-separated list of database names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all databases are subject to access control.

For example:

testdb1,testdb2,sales_db*



This setting supersedes any values specified by POSTGRES_MANAGE_DATABASE_LIST.

POSTGRES_IGNORE_SCHEMA_LIST

string

No

Specifies a comma-separated list of schema names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all schemas are subject to access control.

For example:

testdb1.schema1,testdb2.schema2,sales_db*.sales*



This setting supersedes any values specified by POSTGRES_MANAGE_SCHEMA_LIST.

POSTGRES_IGNORE_TABLE_LIST

string

No

Specifies a comma-separated list of table names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all tables are subject to access control. Names are case-sensitive. Specify tables using the following format:

<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>



This setting supersedes any values specified by POSTGRES_MANAGE_TABLE_LIST.

Users/Groups/Roles management

Table 25. Users/Groups/Roles management

Name

Type

Default

Required

Description

POSTGRES_USER_NAME_REPLACE_FROM_REGEX

string

[~$&+:;=?@#|'<>.^*()_%\\\$\\\$!\\\\-\\\\/\\\\\\\\{}] No Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the POSTGRES_USER_NAME_REPLACE_TO_STRING setting. If not specified, no find and replace operation is performed. POSTGRES_USER_NAME_REPLACE_TO_STRING string _ No Specifies a string to replace the characters matched by the regex specified by the POSTGRES_USER_NAME_REPLACE_FROM_REGEX setting. If not specified, no find and replace operation is performed. POSTGRES_GROUP_NAME_REPLACE_FROM_REGEX string [~$&+:;=?@#|'<>.^*()_%\\\$\\\$!\\\\-\\\\/\\\\\\\\{}]

No

Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the POSTGRES_GROUP_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

POSTGRES_GROUP_NAME_REPLACE_TO_STRING

string

_

No

Specifies a string to replace the characters matched by the regex specified by the POSTGRES_GROUP_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

POSTGRES_ROLE_NAME_REPLACE_FROM_REGEX

string