Dynamic generation of credentials in OpenBao

Sergei Tikhomirov

OpenBao’s database secrets engine supports dynamic generation of credentials for various types of databases. This example demonstrates how to generate credentials for a PostgreSQL database (ADB or ADPG) and connect to it using Spark.

Prerequisites

  • Install theADH cluster with the Spark 3 service.

  • Install the ADPS cluster with the OpenBao service.

  • Install the ADB cluster.

  • Create a table in ADB:

    CREATE TABLE author(id INT NOT NULL, name TEXT NOT NULL)
WITH (appendoptimized=true)
DISTRIBUTED BY(id);
    INSERT INTO author(id, name) VALUES
(1,'Virginia Woolf'),
(2,'J.R.R. Tolkien'),
(3,'Harper Lee'),
(4,'J.D. Salinger'),
(5,'George Orwell'),
(6,'John Steinbeck'),
(7,'Margaret Mitchell'),
(8,'Alan Moore'),
(9,'Jack Kerouac'),
(10,'Ernest Hemingway');

Setup

ADB

  1. Create a user for OpenBao with an ability to grant privileges:

    CREATE ROLE openbao
WITH  CREATEEXTTABLE(protocol='gpfdist',type='readable')
      CREATEEXTTABLE(protocol='gpfdist',type='writable')
      LOGIN
      PASSWORD '123'
WITH GRANT OPTION;

  2. Grant that user the ability to create roles:

    ALTER ROLE openbao WITH CREATEROLE;

  3. Edit the pg_hba.conf file in the ADB configuration in ADCM:

    host    all  openbao       0.0.0.0/0     md5
host    all  tmp_user      0.0.0.0/0     md5

    where tmp_user is the name of the user for which OpenBao will generate a password later on.

OpenBao

This example demonstrates how to set up OpenBao using the CLI. The commands should be run on a host where OpenBao is installed.

  1. Log into OpenBao:

    $ bao login

    If you don’t specify the login method, you will be prompted to enter a token. Once you do, you will see the following message:

    Success! You are now authenticated. The token information displayed below is
already stored in the token helper. You do NOT need to run "bao login" again.
Future OpenBao requests will automatically use this token.

Key                    Value
---                    -----
token                  <your_token>
token_accessor         o5mN3ud1fz1Zmb4EIamcVhve
token_duration         1h
token_renewable        true
token_policies         ["default"]
identity_policies      []
policies               ["default"]
token_meta_username    test_user

  2. Enable the database secrets engine:

    $ bao secrets enable database

  3. Configure the PostgreSQL plugin with connection information (username and password should match the ones created in the ADB section):

    $ bao write database/config/adb \
    plugin_name="postgresql-database-plugin" \
    allowed_roles="cred-manager" \
    connection_url="postgresql://{{username}}:{{password}}@<adb-master-host>:5432/adb?sslmode=disable" \
    username="openbao" \
    password="123" \
    usename_template="tmp_user" \
    password_authentication="scram-sha-256"

    The usename_template parameter is optional, but it is a convenient way to manage the way the generated login will look like. In this example, the username is static. To explore more templating possibilities, see Username templating.

  4. Configure a role that would map a name in OpenBao to an SQL statement to execute when generating database credentials. The name of that role should match the allowed-roles value from the command above:

    $ bao write database/roles/cred-manager \
    db_name="adb" \
    creation_statements="CREATE ROLE "{{name}}" WITH CREATEEXTTABLE(protocol='gpfdist',type='readable') CREATEEXTTABLE(protocol='gpfdist',type='writable') LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';,GRANT USAGE ON SCHEMA public TO "{{name}}";,GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO "{{name}}";" \
    default_ttl="1h" \
    max_ttl="24h"

  5. Generate temporary credentials by reading from the /creds endpoint:

    $ bao read database/creds/cred-manager

    If successful, you will receive the following message:

    Key                Value
---                -----
Key                Value
---                -----
lease_id           database/creds/cred-manager/93reYpVubYhVjd6xpek7edTS
lease_duration     1h
lease_renewable    true
password           cBOb-ugyiUzuPbmySax4
username           tmp_user

Testing

The approach is similar to the one described in the ADB Spark 3 Connector usage examples article.

  1. Open spark3-shell on the host where Spark 3 is installed:

    $ sudo -u hdfs spark3-shell

  2. Define a function for obtaining database credentials from OpenBao (the code below is just for demonstration, you can make changes to it if necessary):

    def getOpenBaoCredentials(openbaoUrl: String, token: String, path: String): (String, String) = {
  val connection = new java.net.URL(s"$openbaoUrl/v1/$path").openConnection
  connection.setRequestProperty("X-Vault-Token", token)

  val response = scala.io.Source.fromInputStream(connection.getInputStream).mkString
  connection.getInputStream.close()

  // Simple manual parsing - assumes response format: {"data":{"username":"xxx","password":"yyy"}}
  val dataPart = response.substring(response.indexOf("\"data\":{") + 7)
  val username = dataPart.split("\"username\":\"")(1).split("\"")(0)
  val password = dataPart.split("\"password\":\"")(1).split("\"")(0)

  (username, password)
}

  3. Obtain the credentials from OpenBao:

    val (username, password) = getOpenBaoCredentials(
  "http://<openbao-host>:8200",
  "<token>",
  "database/creds/cred-manager"
)

  4. Specify the connector settings for reading data from the ADB author table:

    val options = Map(
   "spark.adb.url" -> "jdbc:postgresql://<adb-master-host>:5432/adb",
   "spark.adb.user" -> username,
   "spark.adb.password" -> password,
   "spark.adb.dbschema" -> "public",
   "spark.adb.dbtable" -> "author"
   )

  5. Register a DataFrame:

    val adb_author  = spark.read.format("adb").options(options).load()

  6. Check the DataFrame content:

    adb_author.show()

    The result:

    +---+-----------------+
| id|             name|
+---+-----------------+
|  4|    J.D. Salinger|
|  8|       Alan Moore|
|  5|    George Orwell|
| 10| Ernest Hemingway|
|  2|   J.R.R. Tolkien|
|  1|   Virginia Woolf|
|  7|Margaret Mitchell|
|  6|   John Steinbeck|
|  3|       Harper Lee|
|  9|     Jack Kerouac|
+---+-----------------+
Found a mistake? Seleсt text and press Ctrl+Enter to report it