PostgreSQL

Automate the fulfillment of data subject access, deletion, opt-out, and identifier retrieval requests in PostgreSQL.

Configuration

Create a Connection object with an Postgres ConnectorType to instantiate a connection. Once the object has been created and the credentials have been stored, place the object in the RMAgentConfig connections array.

Example Configuration

{
    "name": "Contacts DB",
    "uuid": "e887952b-4bde-4344-bd1d-c9a46805ebed",
    "capabilities": ["privacy/access", "privacy/delete", "privacy/optout", "privacy/identifiers"],
    "mode": "live",
    "connector_type": "Postgres",
    "queries": {
        "identifiers": {
            "phone_number": [
                "SELECT phone FROM customers WHERE email=%(email)s"
            ]
        },
        "access": ["SELECT * FROM contacts WHERE email = %(email)s"],
        "delete": ["DELETE FROM contacts WHERE email = %(email)s"],
        "optout": ["UPDATE contacts SET optout = true WHERE email = %(email)s"]
    },
    "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail.postgres"
}

Credential Creation

The Postgres connection authenticates with your server using username and password. Create a new secret in you credentials manager with the following key/value pairs:

{
    "user": "<DB username>",
    "password": "<DB password>",
    "host": "<server domain name or IP address>",
    "port": "<port, e.g. 5432>",
    "dbname": "<DB, e.g. BikeStores>"
}

Query Syntax and Parameter Binding

The Queries object allows query strings to be dynamically formatted with identifiers. Identifiers are passed individually to queries and are bound to the variables in the operation. Variables are specified using the pyformat parameter style, e.g. ...WHERE email=%(email)s.

Best Practices

For ease of maintainability and readability, it is recommended that the various queries be stored procedures. This allows for the underlying queries to be modified in Postgres without needing to modify the Agent configuration, and for the query lists to be easily readable, especially in the case of complex joins.