Database Management (PostgreSQL)

Relevant source files

The following files were used as context for generating this wiki page:

• charts/agh2/templates/db/postgres-init-configmap.yml
• charts/agh3/templates/db/postgres-init-configmap.yml
• charts/agh2/values.yaml
• charts/agh3/values.yaml
• charts/agh2/Chart.yaml
• charts/agh3/Chart.yaml
• charts/agh2/templates/captain/captain-deployment.yml
• charts/agh3/templates/captain/captain-deployment.yml
• charts/agh2/templates/validations/index.yaml

The agh2 and agh3 Helm charts provide robust management for PostgreSQL databases, which serve as the primary persistence layer for various application modules. The system is designed with flexibility, allowing deployment of an internal, containerized PostgreSQL instance or connection to an external, user-managed database.

A key feature is the automated database and role initialization. When an internal database is deployed, a ConfigMap containing initialization scripts is created. These scripts dynamically generate databases and user roles for each enabled application module (e.g., Captain, Core, Exploit-Manager), ensuring a seamless setup process. This automation simplifies deployment and configuration, reducing manual intervention.

Architecture and Deployment

The Helm charts support two primary modes for database connectivity: internal and external. The mode is determined by the db.connection.type value.

Sources: charts/agh2/values.yaml:104-107, charts/agh3/values.yaml:153-156

Internal Database Deployment

When db.connection.type is set to internal, the chart deploys a PostgreSQL instance as a sub-chart dependency.

• AGH2 depends on a custom leukocyte-lab/postgresql image which includes the pgroonga extension.
• AGH3 depends on the standard bitnami/postgresql chart.

This relationship is defined in the Chart.yaml file for each respective chart. The internal deployment is enabled by setting db.internal.enabled and postgresql.enabled to true.

Sources: charts/agh2/Chart.yaml:22-25, charts/agh3/Chart.yaml:23-26, charts/agh2/values.yaml:126, charts/agh3/values.yaml:171

The following diagram illustrates the dependency for an internal deployment.

External Database Connection

When db.connection.type is set to external, the chart does not deploy a PostgreSQL instance. Instead, it configures the application modules to connect to a pre-existing database specified by db.connection.host and db.connection.port. Validation is in place to ensure these fields are provided when using an external connection.

Sources: charts/agh2/values.yaml:109-115, charts/agh3/values.yaml:158-164, charts/agh2/templates/validations/index.yaml:40-49

Configuration

Database configuration is managed through the values.yaml file. The primary sections are db for connection settings and postgresql for the internal instance configuration.

General Connection Settings (db.connection)

These settings apply to both internal and external database configurations.

Parameter	Description	AGH2 Default	AGH3 Default
type	Deployment type (internal or external).	internal	internal
driver	The database driver to use.	postgresql	postgresql
host	Hostname for external DB. Ignored for internal.	database.example.com	database.example.com
port	Port for external DB. Ignored for internal.	5432	5432
user	Default database user.	argushack	argushack
password	Default database password.	""	""
options.disableSSL	Whether to disable SSL for the connection.	true	true

Sources: charts/agh2/values.yaml:103-124, charts/agh3/values.yaml:152-169

Internal PostgreSQL Settings (postgresql)

These settings are applicable only when db.connection.type is internal.

Parameter	Description	AGH2 Default	AGH3 Default
enabled	Enables the deployment of the internal PostgreSQL sub-chart.	true	true
fullnameOverride	Overrides the full name of the sub-chart release.	db	db
image.repository	The container image repository for PostgreSQL.	leukocyte-lab/postgresql	docker/bitnamilegacy/postgresql
image.tag	The container image tag.	v0.1.2...	15.7.0
auth.existingSecret	Name of the secret to store credentials.	agh-db-secret	agh-db-secret
auth.username	The initial database user to create.	argushack	argushack
auth.password	Password for the initial user.	""	""
auth.postgresPassword	Password for the postgres superuser.	""	""
primary.extendedConfiguration	Additional configuration lines for postgresql.conf.	max_connections = 32768 shared_buffers = 4GB	max_connections = 32768 shared_buffers = 4GB
primary.initdb.enabled	Enables the database initialization script.	true	true
primary.initdb.scriptsConfigMap	Name of the ConfigMap for init scripts.	db-init-scripts	db-init-scripts

Sources: charts/agh2/values.yaml:150-192, charts/agh3/values.yaml:220-262

Database Initialization (initdb)

When postgresql.primary.initdb.enabled is true, a ConfigMap is created to initialize the database. This ConfigMap contains a shell script (init.sh) that runs during the PostgreSQL container's startup.

Sources: charts/agh2/templates/db/postgres-init-configmap.yml:1-5, charts/agh3/templates/db/postgres-init-configmap.yml:1-5

The initialization process follows these steps:

1. Generate SQL Script (init.sql): A temporary SQL script is created to provision roles and databases.
2. Create Global Role: If db.connection.user and db.connection.password are set, a primary login role is created.
3. Iterate Modules: The script iterates through all top-level modules in values.yaml (e.g., captain, core).
4. Create Module-Specific Roles and Databases: For each enabled module that has a secret.db configuration, the script generates CREATE ROLE and CREATE DATABASE statements. The database is created with the module-specific user as the owner.
5. (AGH2 Only) Install Extensions: If db.helpers.init.dbs.installPgroongaExtensions is true, the pgroonga extension is created in each new database.
6. (AGH2 Only) Load Attack Datasets: If db.helpers.init.attack.enabled is true, the script downloads and imports the attack.sql dataset.
7. Execute Scripts: The generated init.sql (and attack.sql for AGH2) are executed using psql.

The following diagram shows the flow of the init.sh script.

Example SQL Generation

The template dynamically generates SQL statements. For example, creating a database for the captain module looks like this in the template:

{{- if $module.secret.db.name }}

-- Create DB {{ $module.secret.db.name }}
CREATE DATABASE "{{ $module.secret.db.name }}" WITH 
  OWNER = '{{ $user }}'
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1;

{{- end }}

This would render into the following SQL for the captain module:

-- Create DB captain-db
CREATE DATABASE "captain-db" WITH 
  OWNER = 'agh-captain'
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1;

Sources: charts/agh2/values.yaml:298-301, charts/agh3/templates/db/postgres-init-configmap.yml:50-57

Application Integration

Application modules (like Captain) are configured to wait for the database to be ready before starting. This is achieved using an initContainer. The initContainer runs a command that polls the database until it is accessible.

The main application container then receives the database connection string (DB_CONN_STR or PG_CONN_STR) as an environment variable from a Kubernetes secret.

Sources: charts/agh2/templates/captain/captain-deployment.yml:25-36, charts/agh3/templates/captain/captain-deployment.yml:76-87

Pod Startup Sequence

The sequence diagram below illustrates how an application pod (e.g., captain) waits for PostgreSQL to be ready.

Summary

The PostgreSQL management within the helm-charts repository offers a powerful and automated system for database provisioning. By supporting both internal and external deployments, it provides flexibility for different environments. The automated initdb process is a cornerstone of this system, dynamically creating all necessary roles and databases for the application's modules, which greatly simplifies the deployment and operational overhead. The integration with application pods via initContainers ensures a resilient startup sequence, making the entire stack robust and reliable.