Skip to content

PolicySync

Snowflake

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

Prerequisites

Ensure the following:

  • Create a Snowflake account that is accessible from the instance used for Privacera Manager installation.
  • Add users, roles to the Snowflake account and give permissions. For more information, click here.

Configuration

  1. SSH to the instance as ${USER}.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.policysync.snowflake.yml config/custom-vars/
    vi config/custom-vars/vars.policysync.snowflake.yml
    
  3. Edit the following mandatory configuration. For property details and description, click here.

    SNOWFLAKE_JDBC_URL: "<PLEASE_CHANGE>"
    SNOWFLAKE_JDBC_USERNAME: "<PLEASE_CHANGE>"
    SNOWFLAKE_JDBC_PASSWORD: "<PLEASE_CHANGE>"
    SNOWFLAKE_WAREHOUSE_TO_USE: "<PLEASE_CHANGE>"
    SNOWFLAKE_ROLE_TO_USE: "<PLEASE_CHANGE>"
    SNOWFLAKE_JDBC_DB: "<PLEASE_CHANGE>"
    SNOWFLAKE_DEFAULT_USER_PASSWORD: "<PLEASE_CHANGE>"
    SNOWFLAKE_OWNER_ROLE: "<PLEASE_CHANGE>"
    SNOWFLAKE_MANAGE_WAREHOUSE_LIST: "<PLEASE_CHANGE>"
    SNOWFLAKE_MANAGE_DATABASE_LIST: "<PLEASE_CHANGE>"
    SNOWFLAKE_MANAGE_SCHEMA_LIST: "<PLEASE_CHANGE>"
    SNOWFLAKE_MANAGE_TABLE_LIST: "<PLEASE_CHANGE>"
    SNOWFLAKE_MANAGE_VIEW_LIST: "<PLEASE_CHANGE>"
    SNOWFLAKE_MANAGE_ENTITIES: "true"
    SNOWFLAKE_GRANT_UPDATES: "true"
    SNOWFLAKE_AUDIT_SOURCE_ADVANCE_DB_NAME: "PRIVACERA_ACCESS_LOGS_DB"
    POLICYSYNC_ENABLE: "true"
    SNOWFLAKE_ENABLE: "true"
    SNOWFLAKE_ENABLE_AUDIT_SOURCE_SIMPLE: "true"
    SNOWFLAKE_ENABLE_AUDIT_SOURCE_ADVANCE: "false"
    SNOWFLAKE_MANAGE_ENTITY_PREFIX: "<PLEASE_CHANGE>"
    SNOWFLAKE_ENTITY_ROLE_PREFIX: "<PLEASE_CHANGE>"
    SNOWFLAKE_IGNORE_USER_LIST: "user1,user2,user3"
    SNOWFLAKE_IGNORE_GROUP_LIST: "group1,group2,group3"
    SNOWFLAKE_IGNORE_ROLE_LIST: "role1,role2,role3"
    SNOWFLAKE_MANAGE_USER_LIST: "user1,user2,user3"
    SNOWFLAKE_MANAGE_GROUP_LIST: "group1,group2,group3"
    SNOWFLAKE_MANAGE_ROLE_LIST: "role1,role2,role3"
    SNOWFLAKE_MANAGE_USER_FILTERBY_GROUP: "false"
    SNOWFLAKE_MANAGE_GROUPS: "false"
    SNOWFLAKE_ENABLE_ROW_FILTER: "false"
    SNOWFLAKE_ENABLE_VIEW_BASED_MASKING: "false"
    

    Note

    You can also add custom properties that are not included by default. See PolicySync.

  4. Run the following commands.

    cd ~/privacera/privacera-manager
    ./privacera-manager.sh update
    

Validation

  1. Install snowsql if it's not already installed.

    mkdir -p ~/privacera/downloads
    cd ~/privacera/downloads
    wget https://privacera.s3.amazonaws.com/public/pm-demo-data/snowflake/install_snowsql.sh -O install_snowsql.sh
    chmod +x install_snowsql.sh
    ./install_snowsql.sh
    
  2. Download the script for access check.

    wget https://privacera.s3.amazonaws.com/public/pm-demo-data/snowflake/snowflake_access_check.sh -O snowflake_access_check.sh
    chmod +x snowflake_access_check.sh
    
  3. Run downloaded script. For example,./snowflake_access_check.sh testsnowflake.prod.us-west-2.aws emily welcome123

    ./snowflake_access_check.sh ${SNOWFLAKE_ACCOUNT} ${USERNAME} ${PASSWORD}
    
  4. Verify access/denied results by logging in with the Privacera Portal user credentials.

    Navigate to Privacera Portal > Access Management > Audit. Now, access to Snowflake will be shown as Allowed.

Redshift

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

Configuration

  1. SSH to the instance as ${USER}.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.policysync.redshift.yml config/custom-vars/
    vi config/custom-vars/vars.policysync.redshift.yml
    
  3. Edit the following configuration. It is recommended to use the new properties over the old properties. It offers improved performance and stability. The new properties are enabled by using REDSHIFT_V2_ENABLE property, whereas the old properties by using REDSHIFT_ENABLE property. For property details and description, click here.

    REDSHIFT_V2_ENABLE: "true"
    REDSHIFT_JDBC_URL: "<PLEASE_CHANGE>"
    REDSHIFT_JDBC_DB: "privacera_db"
    REDSHIFT_JDBC_USERNAME: "<PLEASE_CHANGE>"
    REDSHIFT_JDBC_PASSWORD: "<PLEASE_CHANGE>"
    REDSHIFT_DEFAULT_USER_PASSWORD: "<PLEASE_CHANGE>"
    REDSHIFT_OWNER_ROLE: "<PLEASE_CHANGE>"
    REDSHIFT_AUDIT_ENABLE: "true"
    REDSHIFT_MANAGE_DATABASE_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_SCHEMA_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_TABLE_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_ENTITIES: "true"
    REDSHIFT_GRANT_UPDATES: "true"
    REDSHIFT_MANAGE_ENTITY_PREFIX: ""
    REDSHIFT_ENTITY_ROLE_PREFIX: ""
    REDSHIFT_IGNORE_USER_LIST: "user1,user2,user3"
    REDSHIFT_IGNORE_GROUP_LIST: "group1,group2,group3"
    REDSHIFT_IGNORE_ROLE_LIST: "role1,role2,role3"
    REDSHIFT_MANAGE_USER_LIST: "user1,user2,user3"
    REDSHIFT_MANAGE_GROUP_LIST: "group1,group2,group3"
    REDSHIFT_MANAGE_ROLE_LIST: "role1,role2,role3"
    REDSHIFT_MANAGE_USER_FILTERBY_GROUP: "false"
    REDSHIFT_MANAGE_GROUPS: "false"
    REDSHIFT_ENABLE_VIEW_BASED_MASKING: "true"
    
    REDSHIFT_ENABLE: "true"
    REDSHIFT_JDBC_URL: "<PLEASE_CHANGE>"
    REDSHIFT_JDBC_DB: "privacera_db"
    REDSHIFT_JDBC_USERNAME: "<PLEASE_CHANGE>"
    REDSHIFT_JDBC_PASSWORD: "<PLEASE_CHANGE>"
    REDSHIFT_DEFAULT_USER_PASSWORD: "<PLEASE_CHANGE>"
    REDSHIFT_OWNER_ROLE: "<PLEASE_CHANGE>"
    REDSHIFT_AUDIT_ENABLE: "true"
    REDSHIFT_MANAGE_DATABASE_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_SCHEMA_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_TABLE_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_VIEW_LIST: "<PLEASE_CHANGE>"
    REDSHIFT_MANAGE_ENTITIES: "true"
    REDSHIFT_GRANT_UPDATES: "true"
    REDSHIFT_MANAGE_ENTITY_PREFIX: ""
    REDSHIFT_ENTITY_ROLE_PREFIX: ""
    REDSHIFT_IGNORE_USER_LIST: "user1,user2,user3"
    REDSHIFT_IGNORE_GROUP_LIST: "group1,group2,group3"
    REDSHIFT_IGNORE_ROLE_LIST: "role1,role2,role3"
    REDSHIFT_MANAGE_USER_LIST: "user1,user2,user3"
    REDSHIFT_MANAGE_GROUP_LIST: "group1,group2,group3"
    REDSHIFT_MANAGE_ROLE_LIST: "role1,role2,role3"
    REDSHIFT_MANAGE_USER_FILTERBY_GROUP: "false"
    REDSHIFT_MANAGE_GROUPS: "false"
    REDSHIFT_ENABLE_ROW_FILTER: "false"
    REDSHIFT_ENABLE_VIEW_BASED_MASKING: "true"
    

    Note

    • When switching from the REDSHIFT_ENABLE property to REDSHIFT_V2_ENABLE property, delete the Kubernetes pod or Docker container of the existing deployed version.

    • You can also add custom properties that are not included by default. See PolicySync.

  4. Run the following commands.

    cd ~/privacera/privacera-manager/
    ./privacera-manager.sh update
    

Limitations with Dynamic Masking and Row Filter

  • Updating Group/Role will not update Dynamic Row Filter or Dynamic Masking.

  • In case of dynamic view, you must have Usage permission on both VIEW Schema as well as Table Schema.

  • Assuming row filter is enabled, you have cleared RocksDB cache and policysync is up, you will not be able to disable the row filter.

Redshift Spectrum

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

For Redshift Spectrum, Privacera currently supports Access Control only on the following:

  • Create Database
  • Usage Schema

Configuration

The steps to configure Redshift Spectrum is similar to Redshift provided above.

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:

Configuration

  1. SSH to the instance as USER.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.policysync.postgres.yml config/custom-vars/
    vi config/custom-vars/vars.policysync.postgres.yml
    
  3. Modify the following properties. For property details and description, click here.

    POSTGRES_ENABLE: "true"
    POSTGRES_V2_ENABLE: "true"
    POSTGRES_JDBC_URL: "<PLEASE_CHANGE>"
    POSTGRES_JDBC_DB: "privacera_db"
    POSTGRES_JDBC_USERNAME: "<PLEASE_CHANGE>"
    POSTGRES_JDBC_PASSWORD: "<PLEASE_CHANGE>"
    POSTGRES_DEFAULT_USER_PASSWORD: "<PLEASE_CHANGE>"
    POSTGRES_OWNER_ROLE: "<PLEASE_CHANGE>"
    POSTGRES_AUDIT_ENABLE: "<PLEASE_CHANGE>"
    POSTGRES_AUDIT_SQS_QUEUE_NAME: "<PLEASE_CHANGE>"
    POSTGRES_MANAGE_DATABASE_LIST: "<PLEASE_CHANGE>"
    POSTGRES_MANAGE_SCHEMA_LIST: "<PLEASE_CHANGE>"
    POSTGRES_MANAGE_TABLE_LIST: "<PLEASE_CHANGE>"
    POSTGRES_MANAGE_VIEW_LIST: "<PLEASE_CHANGE>"
    POSTGRES_IGNORE_USER_LIST: "user1,user2,user3"
    POSTGRES_IGNORE_GROUP_LIST: "group1,group2,group3"
    POSTGRES_IGNORE_ROLE_LIST: "role1,role2,role3"
    POSTGRES_MANAGE_USER_LIST: "user1,user2,user3"
    POSTGRES_MANAGE_GROUP_LIST: "group1,group2,group3"
    POSTGRES_MANAGE_ROLE_LIST: "role1,role2,role3"
    POSTGRES_MANAGE_USER_FILTERBY_GROUP: "false"
    POSTGRES_MANAGE_GROUPS: "false"
    POSTGRES_ENABLE_ROW_FILTER: "false"
    POSTGRES_ENABLE_VIEW_BASED_MASKING: "true"
    


    Note

    You can add custom properties that are not included by default. See PolicySync.

  4. Run the following commands.

    cd ~/privacera/privacera-manager
    ./privacera-manager.sh update
    

Accessing Cross Account SQS Queue for Postgres 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}"
    

Databricks SQL

This topic shows how to configure access control in Databricks SQL.

Prerequisites

Ensure the following prerequisite is met:

  • Create an endpoint in Databricks SQL with a user having admin privileges. For more information, refer to Create an endpoint in Databricks SQL.

  • As you configure the endpoint using the link provided above, get the following values:

    • Host URL
    • JDBC URL
    • JDBC password
    • Database List

Configuration

  1. SSH to the instance as USER.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.policysync.databricks.sql.analytics.yml config/custom-vars/
    vi config/custom-vars/vars.policysync.databricks.sql.analytics.yml
    
  3. Edit the following properties. For property details and description, click here.

    DATABRICKS_SQL_ANALYTICS_ENABLE: "true"
    DATABRICKS_SQL_ANALYTICS_V2_ENABLE: "false"
    DATABRICKS_SQL_ANALYTICS_JDBC_URL: "<PLEASE_CHANGE>"
    DATABRICKS_SQL_ANALYTICS_JDBC_DB: "default"
    DATABRICKS_SQL_ANALYTICS_JDBC_USERNAME: "<PLEASE_CHANGE>"
    DATABRICKS_SQL_ANALYTICS_JDBC_PASSWORD: "<PLEASE_CHANGE>"
    DATABRICKS_SQL_ANALYTICS_HOST_URL: "<PLEASE_CHANGE>"
    DATABRICKS_SQL_ANALYTICS_OWNER_ROLE: "{{ DATABRICKS_SQL_ANALYTICS_JDBC_USERNAME }}"
    DATABRICKS_SQL_ANALYTICS_MANAGE_DATABASE_LIST: "<PLEASE_CHANGE>"
    DATABRICKS_SQL_ANALYTICS_MANAGE_ENTITIES: "false"
    DATABRICKS_SQL_ANALYTICS_GRANT_UPDATES: "false"
    DATABRICKS_SQL_ANALYTICS_MANAGE_ENTITY_PREFIX: ""
    DATABRICKS_SQL_ANALYTICS_ENTITY_ROLE_PREFIX: "priv_"
    DATABRICKS_SQL_ANALYTICS_USE_HIVE_ACCESS_POLICIES: "false"
    DATABRICKS_SQL_ANALYTICS_IGNORE_USER_LIST: "user1,user2,user3"
    DATABRICKS_SQL_ANALYTICS_IGNORE_GROUP_LIST: "group1,group2,group3"
    DATABRICKS_SQL_ANALYTICS_IGNORE_ROLE_LIST: "role1,role2,role3"
    DATABRICKS_SQL_ANALYTICS_MANAGE_USER_LIST: "user1,user2,user3"
    DATABRICKS_SQL_ANALYTICS_MANAGE_GROUP_LIST: "group1,group2,group3"
    DATABRICKS_SQL_ANALYTICS_MANAGE_ROLE_LIST: "role1,role2,role3"
    DATABRICKS_SQL_ANALYTICS_MANAGE_USER_FILTERBY_GROUP: "false"
    DATABRICKS_SQL_ANALYTICS_MANAGE_GROUPS: "true"
    DATABRICKS_SQL_ANALYTICS_ENABLE_VIEW_BASED_ROW_FILTER: "true"
    DATABRICKS_SQL_ANALYTICS_ENABLE_VIEW_BASED_MASKING: "true"
    

    Note

    • When switching from DATABRICKS_SQL_ANALYTICS_ENABLE property to DATABRICKS_SQL_ANALYTICS_V2_ENABLE property, delete the Kubernetes pod or Docker container of the existing deployed version.

    • You can also add custom properties that are not included by default. See PolicySync.

  4. Run the following commands.

    cd ~/privacera/privacera-manager
    ./privacera-manager.sh update
    

RocksDB

This topic shows how to configure RocksDB to tune the performance settings for policysync.

Configuration

  1. SSH to the instance as USER.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.policysync.rocksdb.tuning.yml config/custom-vars/
    vi custom-vars/vars.policysync.rocksdb.tuning.yml
    
  3. Edit the properties as required. For more information on the properties, click here.

  4. Run the following commands.

    cd ~/privacera/privacera-manager
    ./privacera-manager.sh update