Skip to content

PostgreSQL#

This topic covers how you can configure postgreSQL PolicySync access control using Privacera Manager.

Prerequisites#

Ensure the following basic prerequisites are met:

  • Create a database in PostgreSQL. Get the database name and its URL. For more information, refer to Creating a PostgreSQL DB.
  • Create a database user granting all privileges to fully access the database. 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#

Property Mandatory Description Default Value Example
POSTGRES_V2_ENABLE Yes Property to enable/disable PostgreSQL. true
POSTGRES_JDBC_URL Yes This property is used to set the JDBC URL, which can be used to connect to the PostgreSQL server.
JDBC URL must follow below convention:
jdbc:postgresql://<POSTGRES_SERVER_HOST>:<POSTGRES_SERVER_PORT>.
Get the URL from the Prerequisites section above.		 jdbc:postgresql://example.com
POSTGRES_JDBC_DB Yes This property specifies the JDBC database that will be used to establish the initial connection to PostgreSQL. Get the URL from the Prerequisites section above.  privacera_db
POSTGRES_JDBC_USERNAME Yes This property is used to specify the JDBC username that will be used to connect to PostgreSQL.

"user1"
POSTGRES_JDBC_PASSWORD Yes This property is used to specify the JDBC password that will be used to connect to PostgreSQL.

"password"
POSTGRES_DEFAULT_USER_PASSWORD Yes Enter a password that would be set by default for new users of the PostgreSQL database.  default1
POSTGRES_OWNER_ROLE Yes This property is used to specify who owns all of the resources managed by PolicySync.
The specified role will become the owner of all managed resources and will have complete control over those resources.
We support changing the owner of a database, schema, tables, and views.

Note: If the owner role is left blank, ownership will not change, and users who create tables/views or other objects will be the owner of those objects, and PolicySync will not be able to control access to those objects.

PRIVACERA_DEFAULT_OWNER
POSTGRES_AUDIT_ENABLE Yes This property enables access audit retrieval from PostgreSQL. true  
POSTGRES_AUDIT_SQS_QUEUE_NAME

Under the **Advanced** tab, name of the SQS Queue. Get the URL from the Prerequisites section above.

 sqs_name
POSTGRES_MANAGE_DATABASE_LIST Yes This property specifies a comma-separated list of database names for which PolicySync manages access control.
If you want to manage all databases, you can skip this property.
This also accepts wildcards.
The manage database list takes precedence over the ignore database list.
For example, testdb1, testdb2, sales db*
 testdb1, testdb2, sales db*
POSTGRES_MANAGE_SCHEMA_LIST This property specifies a comma-separated list of schema FQDN for which PolicySync manages access control.
Enter the value for the property in the following:
{database_name}.{schema_name}
If the value is kept blank, then all schemas will be managed.
If the value is none, then no schemas will be managed.
If the value is specified as {database_name}.*, then all schemas will be managed.
 testdb1.schema1,testdb2.schema2,sales_db*.sales*
POSTGRES_MANAGE_TABLE_LIST This property specifies a comma-separated list of table FQDN for which PolicySync manages access control.
Enter the value for the property in the following:
{database_name}.{schema_name}.{table_name}
If the value is kept blank, then all tables will be managed.
If the value is none, then no tables will be managed.
If the value is specified as {database_name}.{schema_name}.*, then all tables will be managed.
 testdb1.schema1.table1,testdb2.schema2.view2,sales_db*.sales*.*
POSTGRES_GRANT_UPDATES Yes This property determines whether actual grant/revoke and create/update/delete queries for user/group/role must be run on snowflake. true
POSTGRES_MANAGE_USER_LIST This property specifies a comma-separated list of user names for which PolicySync manages access control.
If you want to manage all users, you can skip this property.
This also works with wildcards.
The ignore users list takes precedence over the manage users list.
For example, user1,user2,dev_user*

 user1,user2,dev_user*
POSTGRES_MANAGE_GROUP_LIST This property specifies a comma-separated list of group names for which PolicySync manages access control.
If you want to manage all groups, you can skip this property.
This also works with wildcards.
The ignore group list takes precedence over the manage groups list.
For example, group1,group2,dev_group*

 group1,group2,dev_group*
POSTGRES_MANAGE_ROLE_LIST This property specifies a comma-separated list of role names for which PolicySync manages access control.
If you want to manage all roles, you can skip this property.
This also accepts wildcards.
The manage role list takes precedence over the ignore role list.
For example, role1,role2,dev_role*
 role1,role2,dev_role*
POSTGRES_IGNORE_USER_LIST This property specifies a comma-separated list of user names for which PolicySync does not manage access control.
If you do not want to ignore any users, you can leave this property blank.
This also accepts wildcards.
This takes precedence over the list of users to manage.
For example, user1,user2,dev_user* 		user1,user2,dev_user*
POSTGRES_IGNORE_GROUP_LIST This property specifies a comma-separated list of group names for which PolicySync does not manage access control.
If you do not want to ignore any groups, you can leave this property blank.
This also accepts wildcards.
This takes precedence over the list of manage groups.
For example, group1,group2,dev_group* 		group1,group2,dev_group*
POSTGRES_IGNORE_ROLE_LIST This property specifies a comma-separated list of role names for which PolicySync does not manage access control.
If you do not want to ignore any roles, you can leave this property blank.
This also works with wildcards.
This takes precedence over the manage roles list.
For example, role1,role2,dev role* 		role1,role2,dev_role*
POSTGRES_MANAGE_USER_FILTERBY_GROUP Set this property to true if you only want to manage users who belong to the groups specified in the POSTGRES_MANAGE_GROUP_LIST property. false false
POSTGRES_MANAGE_GROUPS Use this property to create roles in PostgreSQL when groups are retrieved from Ranger. true
POSTGRES_ENABLE_ROW_FILTER If you want to enable native row filter functionality, set this property to true. This is not recommended because native row filters can only be created on tables and not on views. false true/false
POSTGRES_ENABLE_VIEW_BASED_MASKING

Set this property to true, if you want to enable secure view-based masking in PostgreSQL PolicySync.

Note:- PostgreSQL does not support native masking. It is recommended to use view-based masking.

 true true/false

Accessing Cross Account SQS Queue for PostgreSQL Audits#

Prerequisites

Ensure the following prerequisites are met:

  • Access to AWS account with EC2 instance where Privacera Manager is configured.
  • Access to AWS account where SQS Queue is configured.

Configuration

  1. Get the ARN of the account where the EC2 instance is running.

    1. Open the Amazon EC2 console at https://console.aws.amazon.com/ec2/.

    2. In the navigation pane, choose Instances.

    3. Search for your instance and select it.

    4. In the Security tab, click the link in the IAM Role.

    5. Copy the ARN of the IAM Role.

  2. Get the ARN of the account where the SQS Queue instance is configured.

    1. Open the Amazon SQS console at https://console.aws.amazon.com/sqs/.

    2. From the left navigation pane, choose Queues. From the queue list, select the queue that you created.

    3. In the Details section, copy the ARN of the queue.

  3. Add the policy in the AWS SQS account to grant permissions to the AWS EC2 account.

    1. Open the Amazon SQS console at https://console.aws.amazon.com/sqs/.

    2. In the navigation pane, choose Queues.

    3. Choose a queue and choose Edit.

    4. Scroll to the Access policy section.

    5. Add the access policy statements in the input box.

      {
"Version":"2012-10-17",
"Id":"PolicyAllowSQS",
"Statement":[
    {
        "Sid":"StmtAllowSQS",
        "Effect":"Allow",
        "Principal":{
            "AWS":"${EC2_INSTANCE_ROLE_ARN}"
        },
        "Action":[
            "sqs:DeleteMessage",
            "sqs:GetQueueUrl",
            "sqs:ListDeadLetterSourceQueues",
            "sqs:ReceiveMessage",
            "sqs:GetQueueAttributes"
        ],
        "Resource":"${SQS_QUEUE_ARN}"
    }
]
}

    6. When you finish configuring the access policy, choose Save.

    7. After saving, copy the SQS queue URL in the Details section.

  4. Add the SQS queue URL.

    Run the following command.

    cd ~/privacera/privacera-manager/
vi config/custom-vars/vars.policysync.postgres.yml

    Add the URL in the following property.

    POSTGRES_AUDIT_SQS_QUEUE_NAME: "${SQS_QUEUE_URL}"

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.

      CREATE EXTENSION pgaudit;

    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