PostgreSQL storage backend
Note: The PostgreSQL Storage Backend is in early preview. Use at your own risk. Some breaking changes may occur prior to GA.
The PostgreSQL storage backend is used to persist OpenBao's data in a PostgreSQL server or cluster.
-
High Availability – the PostgreSQL storage backend supports high availability. Requires PostgreSQL 9.5 or later.
-
Community Supported – the PostgreSQL storage backend is supported by the community. While it has undergone review by HashiCorp employees, they may not be as knowledgeable about the technology. If you encounter problems with them, you may be referred to the original author.
storage "postgresql" {
connection_url = "postgres://user123:secret123!@localhost:5432/openbao"
}
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 theBAO_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 andskip_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 andskip_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 totrue
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;