Skip to main content

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

  • Google Cloud SQL PostgreSQL

  • 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



Load keys and intervals

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

[~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}]

No

Specifies a regular expression to apply to a role name and replaces each matching character with the value specified by the POSTGRES_ROLE_NAME_REPLACE_TO_STRING setting.

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

POSTGRES_ROLE_NAME_REPLACE_TO_STRING

string

_

No

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

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

POSTGRES_USER_NAME_PERSIST_CASE_SENSITIVITY

boolean

false

No

Specifies whether PolicySync converts user names to lowercase when creating local users. If set to true, case sensitivity is preserved.

POSTGRES_GROUP_NAME_PERSIST_CASE_SENSITIVITY

boolean

false

No

Specifies whether PolicySync converts group names to lowercase when creating local groups. If set to true, case sensitivity is preserved.

POSTGRES_ROLE_NAME_PERSIST_CASE_SENSITIVITY

boolean

false

No

Specifies whether PolicySync converts role names to lowercase when creating local roles. If set to true, case sensitivity is preserved.

POSTGRES_USER_NAME_CASE_CONVERSION

string

lower

No

Specifies how user name conversions are performed. The following options are valid:

  • lower: Convert to lowercase

  • upper: Convert to uppercase

  • none: Preserve case

This setting applies only if POSTGRES_USER_NAME_PERSIST_CASE_SENSITIVITY is set to true.

POSTGRES_GROUP_NAME_CASE_CONVERSION

string

lower

No

Specifies how group name conversions are performed. The following options are valid:

  • lower: Convert to lowercase

  • upper: Convert to uppercase

  • none: Preserve case

This setting applies only if POSTGRES_GROUP_NAME_PERSIST_CASE_SENSITIVITY is set to true.

POSTGRES_ROLE_NAME_CASE_CONVERSION

string

lower

No

Specifies how role name conversions are performed. The following options are valid:

  • lower: Convert to lowercase

  • upper: Convert to uppercase

  • none: Preserve case

This setting applies only if POSTGRES_ROLE_NAME_PERSIST_CASE_SENSITIVITY is set to true.

POSTGRES_CREATE_USER

boolean

true

No

Specifies whether PolicySync creates local users for each user in Privacera.

POSTGRES_CREATE_USER_ROLE

boolean

true

No

Specifies whether PolicySync creates local roles for each user in Privacera.

POSTGRES_MANAGE_USERS

boolean

true

No

Specifies whether PolicySync maintains user membership in roles in the PostgreSQL data source.

POSTGRES_MANAGE_GROUPS

boolean

true

No

Specifies whether PolicySync creates groups from Privacera in the PostgreSQL data source.

POSTGRES_MANAGE_ROLES

boolean

true

No

Specifies whether PolicySync creates roles from Privacera in the PostgreSQL data source.

POSTGRES_MANAGE_USER_LIST

string

No

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

If not specified, PolicySync manages access control for all users.

If specified, POSTGRES_IGNORE_USER_LIST takes precedence over this setting.

An example user list might resemble the following: user1,user2,dev_user*.

POSTGRES_MANAGE_GROUP_LIST

string

No

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

An example list of projects might resemble the following: group1,group2,dev_group*.

If specified, POSTGRES_IGNORE_GROUP_LIST takes precedence over this setting.

POSTGRES_MANAGE_ROLE_LIST

string

No

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

An example list of projects might resemble the following: role1,role2,dev_role*.

If specified, POSTGRES_IGNORE_ROLE_LIST takes precedence over this setting.

POSTGRES_IGNORE_USER_LIST

string

No

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

This setting supersedes any values specified by POSTGRES_MANAGE_USER_LIST.

POSTGRES_IGNORE_GROUP_LIST

string

No

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

This setting supersedes any values specified by POSTGRES_MANAGE_GROUP_LIST.

POSTGRES_IGNORE_ROLE_LIST

string

No

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

This setting supersedes any values specified by POSTGRES_MANAGE_ROLE_LIST.

POSTGRES_USER_ROLE_PREFIX

string

priv_user_

No

Specifies the prefix that PolicySync uses when creating local users. For example, if you have a user named <USER> defined in Privacera and the role prefix is priv_user_, the local role is named priv_user_<USER>.

POSTGRES_GROUP_ROLE_PREFIX

string

priv_group_

No

Specifies the prefix that PolicySync uses when creating local roles. For example, if you have a group named etl_users defined in Privacera and the role prefix is prefix_, the local role is named prefix_etl_users.

POSTGRES_ROLE_ROLE_PREFIX

string

priv_role_

No

Specifies the prefix that PolicySync uses when creating roles from Privacera in the PostgreSQL data source.

For example, if you have a role in Privacera named finance defined in Privacera and the role prefix is role_prefix_, the local role is named role_prefix_finance.

POSTGRES_USE_NATIVE_PUBLIC_GROUP

boolean

true

No

Specifies whether PolicySync uses the PostgreSQL native public group for access grants whenever a policy refers to a public group. The default value is true.

POSTGRES_MANAGE_USER_FILTERBY_GROUP

boolean

false

No

Specifies whether to manage only the users that are members of groups specified by POSTGRES_MANAGE_GROUP_LIST. The default value is false.

POSTGRES_MANAGE_USER_FILTERBY_ROLE

boolean

false

No

Specifies whether to manage only users that are members of the roles specified by POSTGRES_MANAGE_ROLE_LIST. The default value is false.



Access control management

Table 26. Access control management

Name

Type

Default

Required

Description

POSTGRES_POLICY_NAME_SEPARATOR

string

_priv_

No

Specifies a string to use as part of the name of native row filter and masking policies.

POSTGRES_ROW_FILTER_POLICY_NAME_TEMPLATE

string

{database}{separator}{schema}{separator}{table}

No

Specifies a template for the name that PolicySync uses when creating a row filter policy. For example, given a table data from the schema schema that resides in the db database, the row filter policy name might resemble the following:

db_priv_schema_priv_data_<ROW_FILTER_ITEM_NUMBER>

POSTGRES_ENABLE_ROW_FILTER

boolean

false

No

Specifies whether to use the data source native row filter functionality. This setting is disabled by default. When enabled, you can create row filters only on tables, but not on views.

POSTGRES_ENABLE_VIEW_BASED_MASKING

boolean

true

No

Specifies whether to use secure view based masking. The default value is true.

Because PolicySync does not support native masking for PostgreSQL, enabling this setting is recommended.

POSTGRES_ENABLE_VIEW_BASED_ROW_FILTER

boolean

true

No

Specifies whether to use secure view based row filtering. The default value is true.

While PostgreSQL supports native filtering, PolicySync provides additional functionality that is not available natively. Enabling this setting is recommended.

POSTGRES_SECURE_VIEW_CREATE_FOR_ALL

boolean

true

No

Specifies whether to create secure views for all tables and views that are created by users. If enabled, PolicySync creates secure views for resources regardless of whether masking or filtering policies are enabled.

POSTGRES_MASKED_NUMBER_VALUE

integer

0

No

Specifies the default masking value for numeric column types.

POSTGRES_MASKED_TEXT_VALUE

string

<MASKED>

No

Specifies the default masking value for text and string column types.

POSTGRES_SECURE_VIEW_NAME_PREFIX

string

No

Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view name for a table named example1 is dev_example1.

POSTGRES_SECURE_VIEW_NAME_POSTFIX

string

_secure

No

Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a table named example1 is example1_dev.

POSTGRES_SECURE_VIEW_SCHEMA_NAME_PREFIX

string

No

Specifies a prefix string to apply to a secure schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view schema name for a schema named example1 is dev_example1.

POSTGRES_SECURE_VIEW_SCHEMA_NAME_POSTFIX

string

No

Specifies a postfix string to apply to a secure view schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a schema named example1 is example1_dev.

POSTGRES_SECURE_VIEW_NAME_REMOVE_SUFFIX_LIST

string

No

Specifies a suffix to remove from a table or view name. For example, if the table is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

POSTGRES_SECURE_VIEW_SCHEMA_NAME_REMOVE_SUFFIX_LIST

string

No

Specifies a suffix to remove from a schema name. For example, if a schema is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

POSTGRES_GRANT_UPDATES

boolean

true

No

Specifies whether PolicySync performs grants and revokes for access control and creates, updates, and deletes queries for users, groups, and roles. The default value is true.

POSTGRES_GRANT_UPDATES_MAX_RETRY_ATTEMPTS

integer

2

No

Specifies the maximum number of attempts that PolicySync makes to execute a grant query if it is unable to do so successfully. The default value is 2.

POSTGRES_ENABLE_DATA_ADMIN

boolean

true

No

This property is used to enable the data admin feature. With this feature enabled you can create all the policies on native tables/views, and respective grants will be made on the secure views of those native tables/views. These secure views will have row filter and masking capability. In case you need to grant permission on the native tables/views then you can select the permission you want plus data admin in the policy. Then those permissions will be granted on both the native table/view as well as its secure view.



Access audits management

Table 27. Access audits management

Name

Type

Default

Required

Description

POSTGRES_AUDIT_ENABLE

boolean

false

Yes

Specifies whether Privacera fetches access audit data from the data source.

POSTGRES_AUDIT_EXCLUDED_USERS

string

POSTGRES_JDBC_USERNAME

No

Specifies a comma separated list of users to exclude when fetching access audits. For example: "user1,user2,user3".

POSTGRES_AUDIT_SOURCE

string

sqs

No

Specifies the source for audit information. The following values are supported:

  • sqs

  • gcp_pgaudit

The default value is: sqs



AWS SQS Postgres audit properties

Table 28. AWS SQS Postgres audit properties

Name

Type

Default

Required

Description

POSTGRES_AWS_ACCESS_KEY

string

No

Specifies the Amazon Web Services (AWS) access key that PolicySync uses to create an IAM client role to access the SQS queue to retrieve access audit information.

Specify this only if your deployment machine lacks an IAM role with the necessary permissions.

POSTGRES_AWS_SECRET_KEY

string

No

Specifies the Amazon Web Services (AWS) secret key that PolicySync uses to create an IAM client role to access the SQS queue to retrieve access audit information.

Specify this only if your deployment machine lacks an IAM role with the necessary permissions.

POSTGRES_AWS_REGION

string

POSTGRES_AUDIT_SQS_QUEUE_REGION

No

Specifies the Amazon Web Services (AWS) SQS queue region.

POSTGRES_AUDIT_SQS_QUEUE_REGION

string

us-east-1

No

Specifies the Amazon Web Services (AWS) SQS queue region.

POSTGRES_AWS_SQS_QUEUE_ENDPOINT

string

No

Specifies the SQS endpoint URL on Amazon Web Services (AWS). You must specify this value if you use a private VPC in your AWS account that is not available on the Internet.

POSTGRES_AWS_SQS_QUEUE_NAME

string

POSTGRES_AUDIT_SQS_QUEUE_NAME

No

Specifies the Amazon Web Services (AWS) SQS queue name that PolicySync uses to retrieve access audit information.

POSTGRES_AWS_SQS_QUEUE_MAX_POLL_MESSAGES

integer

100

No

Specifies the number of messages to retrieve from the SQS queue at one time for audit information.



GCP PostgreSQL audit properties

Table 29. GCP PostgreSQL audit properties

Name

Type

Default

Required

Description

POSTGRES_GCP_AUDIT_SOURCE_INSTANCE_ID

string

No

Specifies the Google Cloud Platform SQL instance ID for the PostgreSQL server. PolicySync uses this instance ID for retrieving access audit information.

The instance ID must be provided in the following formation:

<PROJECT_ID>:<DB_INSTANCE_ID>

POSTGRES_OAUTH_PRIVATE_KEY_FILE_NAME

string

policysync-postgres-gcp-audit-service-account.json

No

Specifies the name of the JSON file that contains your service account credentials. This setting applies only to PostgreSQL on Google Cloud Platform.



Accessing PostgreSQL Audits in GCP

Prerequisites

Ensure the following prerequisites are met:

Configuration

  1. In GCP:

    1. Run the following commands on Google Cloud's shell (gcloud) by providing GCP_PROJECT_ID and INSTANCE_NAME.

      gcloud sql instances patch  {INSTANCE_NAME} --database-flags=cloudsql.enable_pgaudit=on,pgaudit.log=all --project {GCP_PROJECT_ID}

    2. Run a SQL command using a compatible psql client to create the pgAudit extension.

      CREATEEXTENSIONpgaudit;

    3. Create a service account and private key JSON file, which will be used by PolicySync to pull access audits. See Setting up authentication and edit the following fields:

      • Service account name: Enter any user-defined name. For example, policysync-postgres-gcp-audit-service-account.

      • Select a role: Select Private Logs Viewer role.

      • Create new key: Create a service account key and download the JSON file in the custom-vars folder.

  2. In Privacera Manager:

    Add the following properties in vars.policysync.postgres.yml file:

    POSTGRES_AUDIT_SOURCE:"gcp_pgaudit"POSTGRES_GCP_AUDIT_SOURCE_INSTANCE_ID:"<PLEASE_CHANGE>"POSTGRES_OAUTH_PRIVATE_KEY_FILE_NAME:"<PLEASE_CHANGE>"

    Property

    Mandatory

    Description

    Default Value

    Example

    POSTGRES_AUDIT_SOURCE

    Yes

    Supported audit sources are sqs and gcp_pgaudit. Default is set to sqs.

    gcp_pgaudit

    POSTGRES_GCP_AUDIT_SOURCE_INSTANCE_ID

    Yes

    This property is used to specify the GCP Cloud SQL instance id for the PostgreSQL server, which will be used to retrieve access audits.

    The value for this instance id must be in the format:

    project_id:db_instance_id.

    demo-project:postgres-demo-server

    POSTGRES_OAUTH_PRIVATE_KEY_FILE_NAME

    Yes

    This property is used to specify the name of the JSON file containing the service account credential that was downloaded from the Google service account keys section.

    policysync-postgres-gcp-audit-service-account.json