PostgreSQL storage backend

The PostgreSQL storage backend is used to persist OpenBao's data in a PostgreSQL server or cluster.

tip

High Availability – the PostgreSQL storage backend supports High Availability. Requires PostgreSQL 9.5 or later.

tip

Production Ready – the PostgreSQL backend is considered production ready and supports paginated lists and transactional storage.

storage "postgresql" {
  connection_url = "postgres://user123:secret123!@localhost:5432/openbao"
}

warning

Note: The PostgreSQL storage backend plugin will attempt to use SSL when connecting to the database. If SSL is not enabled the connection_url will need to be configured to disable SSL. See the documentation below to disable SSL.

postgresql parameters

• connection_url (string: <required>) – Specifies the connection string to use to authenticate and connect to PostgreSQL. The connection URL can also be set using the BAO_PG_CONNECTION_URL environment variable. A full list of supported parameters can be found in the pgx library and PostgreSQL connection string documentation. For example connection string URLs, see the examples section below.

• table (string: "openbao_kv_store") – Specifies the name of the table in which to write OpenBao data. OpenBao will attempt to create it if missing and skip_create_table=false (the default).

• max_idle_connections (int) - Default not set. Sets the maximum number of connections in the idle connection pool. See golang docs on SetMaxIdleConns for more information. Requires OpenBao 1.2 or later.

• max_parallel (string: "128") – Specifies the maximum number of concurrent requests to PostgreSQL.

• ha_enabled (string: "true|false") – Default not enabled, requires PostgreSQL 9.5 or later.

• ha_table (string: "openbao_ha_locks") – Specifies the name of the table to use for storing High Availability information. OpenBao will attempt to create it if missing and skip_create_table=false (the default).

• upsert_function (string: "openbao_kv_put") – Specifies the name of the function to execute for upsert capabilities on PostgreSQL versions earlier than 9.5. This function must already exist. See above documentation.

• skip_create_table (string: "true|false", default "false") - When enabled, will not attempt to automatically create database tables if missing. Requires PostgreSQL 9.5 or later. Set to true if the database user does not have the required permissions; otherwise, OpenBao will fail to start.

postgresql examples

Custom SSL verification

This example shows connecting to a PostgreSQL cluster using full SSL verification (recommended).

storage "postgresql" {
  connection_url = "postgres://user:pass@localhost:5432/database?sslmode=verify-full"
}

To disable SSL verification (not recommended), replace verify-full with disable:

storage "postgresql" {
  connection_url = "postgres://user:pass@localhost:5432/database?sslmode=disable"
}

Manually creating tables

OpenBao will attempt to automatically create tables compatible with PostgreSQL 9.5 or later. However, to manually create tables, use the following schemas:

CREATE TABLE openbao_kv_store (
  parent_path TEXT COLLATE "C" NOT NULL,
  path        TEXT COLLATE "C",
  key         TEXT COLLATE "C",
  value       BYTEA,
  CONSTRAINT pkey PRIMARY KEY (path, key)
);

CREATE INDEX parent_path_idx ON openbao_kv_store (parent_path);

Store for HAEnabled backend

CREATE TABLE openbao_ha_locks (
  ha_key                                      TEXT COLLATE "C" NOT NULL,
  ha_identity                                 TEXT COLLATE "C" NOT NULL,
  ha_value                                    TEXT COLLATE "C",
  valid_until                                 TIMESTAMP WITH TIME ZONE NOT NULL,
  CONSTRAINT ha_key PRIMARY KEY (ha_key)
);

If you're using a version of PostgreSQL prior to 9.5, create the following function:

CREATE FUNCTION openbao_kv_put(_parent_path TEXT, _path TEXT, _key TEXT, _value BYTEA) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE openbao_kv_store
          SET (parent_path, path, key, value) = (_parent_path, _path, _key, _value)
          WHERE _path = path AND key = _key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO openbao_kv_store (parent_path, path, key, value)
              VALUES (_parent_path, _path, _key, _value);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;