Vault – PostgreSQL as Backend - DevOps

Info Box

Before starting the post. I must warn you that this is a configuration that must be done at the very beginning of the Vaul configuration, or as soon as you change the storage/backend, you may lose everything you configured or created in the Vault.

Ok, but the post itself is how to configure Vault to use a PostgreSQL database as storage / backend. This provides much greater security for data, as it can involve all normal database administration, backups, high availability, etc.

I will show you how to create a Docker VM, both are simple. Initially, I created it in Docker, but then I created it in VM, as I want to explore other possibilities with PostgreSQL in the future.

Docker
VM

The first step is to create a PostgreSQL instance, I will create a Container in my Docker. Create a volume for PGData and then the container.

docker volume create --name vault_pgdata

docker run -d --name vault-postgres -p 5432:5432 -e POSTGRES_PASSWORD=1234qwer -v vault_pgdata:/var/lib/postgresql/data postgres


#######################################################################################################################

docker container ls
CONTAINER ID   IMAGE                                                   COMMAND                  CREATED          STATUS                  PORTS                                                                                  NAMES
b822935dfc78   postgres                                                "docker-entrypoint.s‚Ä¶"   35 seconds ago   Up 34 seconds           0.0.0.0:5432->5432/tcp, :::5432->5432/tcp                                              vault-postgres

Now we need to connect to PostgreSQL and create the database schema. As stated on the Vault page, this is not created automatically. But for organization, I will also create a unique user and a separate database.

docker exec -it -u root --privileged vault-postgres /bin/bash

To connect to the PostgreSQL we just created:

psql -h localhost -p 5432 -U postgres

With all this, the steps to follow are to create the vault_secrets database, the usr_vault user, assign the permissions and create the tables.

CREATE DATABASE vault_secrets;
CREATE USER usr_vault WITH PASSWORD 'efDZGo92Cco3u1Zh';

## Change to new database
\c vault_secrets;

CREATE TABLE vault_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 vault_kv_store (parent_path);

CREATE TABLE vault_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)
);

GRANT ALL PRIVILEGES ON DATABASE vault_secrets TO usr_vault;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO usr_vault;

Now in Vault, you need to change the configuration file. /etc/vault.d/vault.hcl

Comment out the default storage section and add the new section for PostgreSQL with the connection string. The IP I use here is that of the local Docker server, see https://devops-db.com/some-observations/

vi /etc/vault.d/vault.hcl

# storage "file" {
#  path = "/opt/vault/data"
#}

storage "postgresql" {
  connection_url = "postgres://usr_vault:efDZGo92Cco3u1Zh@172.21.5.76:5432/vault_secrets?sslmode=disable"
}

I created a simple VM, with only 1Gb of memory, for testing purposes. https://github.com/faustobranco/devops-db/tree/master/postgresql

Following the project standards, I set the IP to 172.21.5.158 and the DNS to postgresql.devops-db.internal/ . (https://devops-db.com/some-observations/ and https://github.com/faustobranco/devops-db/blob/master/bind9/config/devops-db.internal)

The process is simple. To do this we need to add the PostgreSQL repository to Ubuntu.
Ubuntu already comes with some PostgreSQL package available for installation, but it is not the most up-to-date, so it is best to search directly in the PostgreSQL repo.

sudo apt install curl ca-certificates

sudo install -d /usr/share/postgresql-common/pgdg

sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

We update and install the PostgreSQL package.

sudo apt update
sudo apt -y install postgresql

It is already installed and working, first, let’s access psql and change the password of the postgres user who is the server admin.

sudo -u postgres psql 

postgres=# ALTER USER postgres with encrypted password 'O8XLGuq7BoEKVVgHvU9rDvEyC6oJhELC';

Next, we will change the PostgreSQL configuration files to the first basic settings. Allow connections from any IP. Let’s change 2 files: pg_hba.conf and postgresql.conf

vi /etc/postgresql/17/main/pg_hba.conf

# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256

vi /etc/postgresql/17/main/postgresql.conf
listen_addresses = '*'

Then we restart the PostgreSQL service.

systemctl restart postgresql
systemctl status postgresql

To connect to the PostgreSQL we just created:

psql -h localhost -p 5432 -U postgres

With all this, the steps to follow are to create the vault_secrets database, the usr_vault user, assign the permissions and create the tables.

CREATE DATABASE vault_secrets;
CREATE USER usr_vault WITH PASSWORD 'efDZGo92Cco3u1Zh';

## Change to new database
\c vault_secrets;

CREATE TABLE vault_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 vault_kv_store (parent_path);

CREATE TABLE vault_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)
);

GRANT ALL PRIVILEGES ON DATABASE vault_secrets TO usr_vault;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO usr_vault;

Now in Vault, you need to change the configuration file. /etc/vault.d/vault.hcl

vi /etc/vault.d/vault.hcl

# storage "file" {
#  path = "/opt/vault/data"
#}

storage "postgresql" {
  connection_url = "postgres://usr_vault:efDZGo92Cco3u1Zh@postgresql.devops-db.internal:5432/vault_secrets?sslmode=disable"
}

If everything is ok, just restart the Vault service.

systemctl restart vault.service

Info Box

As stated at the beginning of the post, this setting resets all Vault information and settings.
So most likely you will need new Unseal code.

vault operator init

Unseal Key 1: JzLkb0XVhmW/a0Q2T1zU+Se8rLbnm0ttYEoRXDAQHDNm
Unseal Key 2: wYGlSUWWWp4VPYJGFZUtFtjXAiaWQ91GgHepFlsFQT+Y
Unseal Key 3: oYy8+SLqAZehI6ypb4zKZ5LqTy5IU/l4yRdYiaEw82lS
Unseal Key 4: 646M8DoYIz/m4D6Opl2u2U/AomGhuVhFzPf0cnS6YCS1
Unseal Key 5: 4RXiAj1p2EzA5bChNtdx+g5dXaoIusuSKQiHAf9DCIQ+

Initial Root Token: hvs.fvRPKZEHbmdv7NWDoIcNw3W8

Vault initialized with 5 key shares and a key threshold of 3. Please securely
distribute the key shares printed above. When the Vault is re-sealed,
restarted, or stopped, you must supply at least 3 of these keys to unseal it
before it can start servicing requests.

Vault does not store the generated root key. Without at least 3 keys to
reconstruct the root key, Vault will remain permanently sealed!

It is possible to generate new unseal keys, provided you have a quorum of
existing unseal keys shares. See "vault operator rekey" for more information

Let’s take a look at PostgreSQL?

psql -d vault_secrets -U usr_vault -W

Password:
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

vault_secrets=> Select * from vault_kv_store;
vault_secrets=> Select count(*) from vault_kv_store;
 count
-------
    57

Vault – PostgreSQL as Backend

Info Box

Info Box

By devops-db.com

Related Post

You Missed

Vault – Secrets Integration with Kubernetes.

Vault – File storage as secret.

Ansible – Read Vault secrets.

Terraform – Read Vault secrets