PostgreSQL Dynamic Secrets

Once you have installed the DSV Engine, you can use DSV to create dynamic secrets.

Base Secret

Base secret data defines how to establish a connection with a PostgreSQL server. All values are required and will be used to build a connection string in a URL format. A type must be set in attributes of a base secret. For PostgreSQL, the type field in attributes should always be postgres.

Create a file named postgres_base.json, substituting your values:

{
  "host": "your.host",
  "port": 5432,
  "database": "postgres",
  "username": "postgres",
  "password": "myp@ssword"
}

Create a secret using the CLI at a path of your choosing:

dsv secret create \
    --path db:postgresql:root \
    --data @postgres_base.json \
    --attributes '{"type": "postgres"}'

Dynamic Secret

A dynamic secret will be linked to the base secret. One base secret can have many dynamic secrets linked to it.

Create a file named postgres_dynamic1.json, substituting your values:

{
  "linkConfig": {
    "linkType": "dynamic",
    "linkedSecret": "db:postgresql:root"
  },
  "grantPermissions": {
    "what": "ALL PRIVILEGES",
    "where": "postgres"
  },
  "pool": "pool1",
  "ttl": 1000,
  "userPrefix": "usr"
}

Create a dynamic secret using the CLI at a path of your choosing:

dsv secret create --path db:postgresql:dynamic1 --attributes @postgres_dynamic1.json

when creating a dynamic secret the data field should be empty.

Attributes description:

1. linkConfig: denotes that it is a dynamic secret with a link to a base secret:

   • linkType: should always be dynamic
   • linkedSecret: sets a path to base secret

2. grantPermissions: defines access privileges

   • what: a specific privilege, e.g. SELECT, INSERT, UPDATE, DELETE
   • where: a database object, e.g. a table name, a view name, a database name
3. pool: a pool name to use
4. ttl: a number of seconds before the engine automatically deletes new credentials, must be set at or above 900
5. userPrefix: an optional field that defines a prefix for a new username

To create a new user, the CREATE USER command is used.

To assign privileges, the GRANT command is used.

GRANT <"what"> ON <"where"> TO <"username">;

Sending a PostgreSQL Task to Engine

Read the PostgreSQL dynamic secret. A randomly chosen engine in the engine pool should receive the task and perform it. The engine attempts to create a PostgreSQL account and reports back success or failure. On success, the user also receives the new working credentials. As long as there is at least one running engine in a given pool, an engine will receive a PostgreSQL account revocation task and delete the account once its TTL expires.

List PostgreSQL Base Secrets

To find all base secrets that are related to PostgreSQL run:

dsv secret search --query "postgres" --search-field "attributes.type"

List Dynamic Secrets

To find all dynamic secrets that are linked to a specific base secret run:

dsv secret search --query "db:postgresql:root" --search-links

Read Dynamic Secret Attributes

Using the secret read CLI command to read a dynamic secret will initiate a creation of a new credentials. To read a dynamic secret use the secret describe CLI command instead.

Example:

dsv secret describe db:postgresql:dynamic1

The secret describe does not return the secret data field, but for dynamic secrets it is always empty.

Third Party Reference

For server configuration details, refer to Postgresql documentation.