Prepare PostgreSQL Database

tasks/common/prepare_db.yml provisions an application's PostgreSQL database through the PostgreSQL maintenance connection. Service-specific prepare tasks load their connection and application credentials from Vault, then supply them to this shared task.

Process

The task:

• Validates the supplied database host, port, application credentials, and database name.
• Generates a temporary internal-Root-CA client certificate, owned by root, for the PostgreSQL maintenance user.
• Waits for the database server over verify-full TLS.
• Creates the application login role and database, grants ALL privileges on tables, sequences, functions, and procedures, and makes the application role the database owner.
• Removes the temporary certificate files and clears the task facts after provisioning.

Input variables

Each calling service supplies these variables to tasks/common/prepare_db.yml:

Variable	Type	Description
cs_common_prepare_db_host	string	PostgreSQL host address.
cs_common_prepare_db_port	int	PostgreSQL port.
cs_common_prepare_db_maintenance_password	string	PostgreSQL maintenance user's password.
cs_common_prepare_db_user	string	Application database login user.
cs_common_prepare_db_password	string	Application database login password.
cs_common_prepare_db_database	string	Application database name.

Prerequisites

• The application host must already have the PostgreSQL client installed, see System Patching.
• The target PostgreSQL host's apps/postgresql/config Vault secret must include an apps_dict entry for the application database. The entry provides the application login user and password; the service-specific documentation describes the required key and default database name.