Skip to main content

Privacera Documentation

Connect PostgreSQL to PrivaceraCloud

This topic describes how to connect PostgreSQL application to PrivaceraCloud.

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:

Procedure
  1. Go the Setting > Applications.

  2. In the Applications screen, select PostgreSQL.

  3. Enter the application Name and Description, and then click Save.

  4. Click the toggle button to enable Access Management for PostgreSQL.

  5. In the BASIC tab, enter the values in the given fields and click Save. For property details and description, see table below:

    Note

    Make sure that the other properties are advanced and should be modified in consultation with Privacera.

    Basic fields

    Table 22. Basic fields

    Field 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 default database

    string

    privacera_db

    Yes

    Specifies the name of the JDBC database to use.

    Default password for new postgres user

    string

    Yes

    Specifies the password to use when PolicySync creates new users.

    Postgres resource owner

    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

    Databases to set access control policies

    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, Databases to ignore while setting access control policies takes precedence over this setting.

    Enable policy enforcements and user/group/role management

    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.

    Enable access audits

    boolean

    false

    Yes

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

    Audit source for postgres

    string

    sqs

    No

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

    • sqs

    • gcp_pgaudit

    • azure_audit

    The default value is: sqs

    AWS access key to connect to sqs queue for access audits

    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.

    AWS secret access key to connect to sqs queue for access audits

    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.

    AWS region of sqs queue

    string

    POSTGRES_AUDIT_SQS_QUEUE_REGION

    No

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

    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.

    GCP CloudSQL postgres 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>
    

    Client id to connect to azure for access audit

    string

    In case of Audit source for postgresset to azure_audit

    Set the client id which will be used to access audits.

    Tenant id to connect to azure for access audits

    string

    In case of Audit source for postgresset to azure_audit

    Set the tenant id which will be used to access audits.

    Client secret value to connect to azure for access audits

    string

    In case of Audit source for postgresset to azure_audit

    Set the client secret value which will be used to access audits.

    Workspace id to connect to azure for access audits

    string

    In case of Audit source for postgresset to azure_audit

    Set the workspace id which will be used to access audits.



    Advanced fields

    Table 23. Advanced fields

    Field name

    Type

    Default

    Required

    Description

    Schemas to set access control policies

    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, Schemas to ignore while setting access control policies 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.

    Tables to set access control policies

    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, 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.

    Databases to ignore while setting access control policies

    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 Databases to set access control policies.

    Schemas to ignore while setting access control policies

    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 Schemas to set access control policies.

    Regex to find special characters in user names

    string

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

    No

    Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the String to replace with the special characters found in user names setting.

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

    String to replace with the special characters found in user names

    string

    _

    No

    Specifies a string to replace the characters matched by the regex specified by the Regex to find special characters in user names setting.

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

    Regex to find special characters in group names

    string

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

    No

    Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the String to replace with the special characters found in group names setting.

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

    String to replace with the special characters found in group names

    string

    _

    No

    Specifies a string to replace the characters matched by the regex specified by the Regex to find special characters in group names setting.

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

    Regex to find special characters in role names

    string

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

    No

    Specifies a regular expression to apply to a role name and replaces each matching character with the value specified by the String to replace with the special characters found in role names setting.

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

    String to replace with the special characters found in role names

    string

    _

    No

    Specifies a string to replace the characters matched by the regex specified by the Regex to find special characters in role names setting.

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

    Persist case sensitivity of user names

    boolean

    false

    No

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

    Persist case sensitivity of group names

    boolean

    false

    No

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

    Persist case sensitivity of role names

    boolean

    false

    No

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

    Create users in postgres by policysync

    boolean

    true

    No

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

    Create user roles in postgres by policysync

    boolean

    true

    No

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

    Manage users from portal

    boolean

    true

    No

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

    Manage groups from portal

    boolean

    true

    No

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

    Manage roles from portal

    boolean

    true

    No

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

    Users to set access control policies

    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, Users to be ignored by access control policies takes precedence over this setting.

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

    Groups to set access control policies

    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, Groups be ignored by access control policies takes precedence over this setting.

    Roles to set access control policies

    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, Roles be ignored by access control policies takes precedence over this setting.

    Users to be ignored by access control policies

    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 Users to set access control policies.

    Groups be ignored by access control policies

    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 Groups to set access control policies.

    Roles be ignored by access control policies

    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 Roles to set access control policies.

    Prefix of postgres roles for portal users

    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>.

    Prefix of postgres roles for portal groups

    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.

    Prefix of postgres roles for portal roles

    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.

    Use postgres native public group for public group access policies

    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.

    Set access control policies only on the users from managed groups

    boolean

    false

    No

    Specifies whether to manage only the users that are members of groups specified by Groups to set access control policies. The default value is false.

    Set access control policies only on the users/groups from managed roles

    boolean

    false

    No

    Specifies whether to manage only users that are members of the roles specified by Roles to set access control policies. The default value is false.

    Enforce postgres native 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.

    Enforce masking policies using secure views

    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.

    Enforce row filter policies using secure views

    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.

    Create secure view for all tables/views

    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.

    Default masked value for numeric datatype columns

    integer

    0

    No

    Specifies the default masking value for numeric column types.

    Default masked value for text/varchar datatype columns

    string

    <MASKED>

    No

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

    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.

    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.

    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.

    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.

    Enable dataadmin

    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.

    Users to exclude when fetching access audits

    string

    POSTGRES_JDBC_USERNAME

    No

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



    Custom fields

    Table 24. Custom fields

    Canonical name

    Type

    Default

    Description

    load.resources

    string

    load_from_database_columns

    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.

    sync.interval.sec

    integer

    60

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

    sync.serviceuser.interval.sec

    integer

    420

    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.

    sync.servicepolicy.interval.sec

    integer

    540

    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.

    audit.interval.sec

    integer

    30

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

    ignore.table.list

    string

    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 Tables to set access control policies.

    user.name.case.conversion

    string

    lower

    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 Persist case sensitivity of user names is set to true.

    group.name.case.conversion

    string

    lower

    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 Persist case sensitivity of group names is set to true.

    role.name.case.conversion

    string

    lower

    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 Persist case sensitivity of role names is set to true.

    policy.name.separator

    string

    _priv_

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

    row.filter.policy.name.template

    string

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

    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>
    

    secure.view.name.remove.suffix.list

    string

    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.

    secure.view.schema.name.remove.suffix.list

    string

    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.

    perform.grant.updates.max.retry.attempts

    integer

    2

    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.

    aws.sqs.queue.endpoint

    string

    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.

    aws.sqs.queue.max.poll.messages

    integer

    100

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



  6. In the ADVANCED tab, you can add custom properties.

  7. Using the IMPORT PROPERTIES button, you can browse and import application properties.