terraform-module-postgresql-flexible

Terraform module for Azure Database for PostgreSQL - Flexible Server

Example

provider.tf

provider "azurerm" {
  features {}
}

provider "azurerm" {
  features {}
  skip_provider_registration = true
  alias                      = "postgres_network"
  subscription_id            = var.aks_subscription_id
}

variables.tf

variable "postgres_geo_redundant_backups" {
  default = false
}

prod.tfvars

postgres_geo_redundant_backups = true

postgres.tf

module "postgresql" {

  providers = {
    azurerm.postgres_network = azurerm.postgres_network
  }
  
  source = "[email protected]:hmcts/terraform-module-postgresql-flexible?ref=master"
  env    = var.env

  product       = var.product
  component     = var.component
  business_area = "sds" # sds or cft

  # The original subnet is full, this is required to use the new subnet for new databases
  subnet_suffix = "expanded"

  pgsql_databases = [
    {
      name : "application"
    }
  ]

  pgsql_sku     = "GP_Standard_D2ds_v4"
  pgsql_version = "16"

  geo_redundant_backups = var.postgres_geo_redundant_backups

  # Changing the value of the trigger_password_reset variable will trigger Terraform to rotate the password of the pgadmin user.
  trigger_password_reset = "any value here"
  
  # The ID of the principal to be granted admin access to the database server.
  # On Jenkins it will be injected for you automatically as jenkins_AAD_objectId.
  # Otherwise change the below:
  admin_user_object_id = var.jenkins_AAD_objectId
  
  common_tags = var.common_tags
}

variables.tf

variable "aks_subscription_id" {} # provided by the Jenkins library, ADO users will need to specify this

Access to databases

VNet injection is used to restrict network access to PostgreSQL flexible servers. This means that you can't access the database directly from your local machine. Typically, you will need to set up an SSH tunnel to access the database you want to.

All developers can access non production databases with reader access.

Security cleared developers can access production DBs using just in time access and an approved business justification.

Note: access is only granted on a case by case basis, and is removed automatically

More process details to follow, it's currently being worked out.

Non production:

First time setup

1. Join either 'DTS CFT Developers' or 'DTS SDS Developers' AAD group via GitHub pull request

Bastion configuration

Ensure you have Azure CLI version 2.22.1 or later installed

Run az login

Ensure ssh extension for the Azure CLI is installed: 'az extension add --name ssh'

Run az ssh config --ip \*.platform.hmcts.net --file ~/.ssh/config

Steps to access

1. Connect to the VPN
2. Request access to the non production bastion via JIT, this will be automatically approved, and lasts for 24 hours.
3. Copy below script, update the variables (search for all references to draft-store and replace with your DB) and run it

# If you haven't logged in before you may need to login, uncomment the below line:
# az login
# this should give you a long JWT token, you will need this later on
az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv

ssh bastion-nonprod.platform.hmcts.net

export PGPASSWORD=<result-from-earlier>

# you can get this from the portal, or determine it via the inputs your pass to this module in your code
POSTGRES_HOST=rpe-draft-store-aat.postgres.database.azure.com

# this matches the `database_name` parameter you pass in the module
DB_NAME=draftstore

DB_USER="DTS\ CFT\ DB\ Access\ Reader" # read access
#DB_USER="DTS\ Platform\ Operations" # operations team administrative access

psql "sslmode=require host=${POSTGRES_HOST} dbname=${DB_NAME} user=${DB_USER}"

Note: it's also possible to tunnel the connection to your own machine and use other tools to log in, IntelliJ database tools works, pgAdmin 4 works with a workaround for the password field length limit, when creating a new connection untick the "Connect now?" option and don't set the password, save the connection, afterwards when trying to connect a newly created db connection, the password pop up will accept the long password token generated.

Tunnel version of the script

# you can get this from the portal, or determine it via the inputs your pass to this module in your code
POSTGRES_HOST=rpe-draft-store-aat.postgres.database.azure.com

ssh -N bastion-nonprod.platform.hmcts.net -L 5440:${POSTGRES_HOST}:5432
# expect no more output in this terminal you won't get an interactive prompt

# in a separate terminal run:
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)
# this matches the `database_name` parameter you pass in the module
DB_NAME=draftstore

DB_USER="DTS\ CFT\ DB\ Access\ Reader" # read access
#DB_USER="DTS\ Platform\ Operations" # operations team administrative access

psql "sslmode=require host=localhost port=5440 dbname=${DB_NAME} user=${DB_USER}"

Production

First time setup

1. Join either 'DTS CFT Developers' or 'DTS SDS Developers' AAD group via GitHub pull request
2. Request access to production via JIT, this requires SC clearance, or an approved exception. Note: after this is approved it can take some time for the other packages to show up, try logging out and back in.

Ensure you have Azure CLI version 2.22.1 or later installed

Run az login

Ensure ssh extension for the Azure CLI is installed: 'az extension add --name ssh'

Run az ssh config --ip \*.platform.hmcts.net --file ~/.ssh/config

Steps to access

1. Request access to the database that you need via JIT, the naming convention is Database - <product> (read|write) access.
2. Wait till it's approved, you can also message in #db-self-service on slack.
3. Connect to the VPN
4. Copy below script, update the variables (search for all references to draft-store and replace with your DB), and run it

# If you haven't logged in before you may need to login, uncomment the below line:
# az login
# this should give you a long JWT token, you will need this later on
az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv

# follow the prompts to login
ssh bastion-prod.platform.hmcts.net

export PGPASSWORD=<result-from-earlier>

# you can get this from the portal, or determine it via the inputs your pass to this module in your code
POSTGRES_HOST=rpe-draft-store-prod.postgres.database.azure.com

# this matches the `database_name` parameter you pass in the module
DB_NAME=draftstore

# make sure you update the product name in the middle to your product
DB_USER="DTS\ JIT\ Access\ draft-store\ DB\ Reader\ SC" # read access
#DB_USER="DTS\ Platform\ Operations\ SC" # operations team administrative access

psql "sslmode=require host=${POSTGRES_HOST} dbname=${DB_NAME} user=${DB_USER}"
# note: some users have experienced caching issues with their AAD token:
# psql: error: FATAL:  Azure AD access token not valid for role DTS JIT Access send-letter DB Reader SC (does not contain group ID c9e865ee-bc88-40d9-a5c1-23831f0ce255)
# the fix is to clear the cache and login again: rm -rf ~/.azure && az login

Note: it's also possible to tunnel the connection to your own machine and use other tools to log in, IntelliJ database tools works, pgAdmin 4 works with a workaround for the password field length limit, when creating a new connection untick the "Connect now?" option and don't set the password, save the connection, afterwards when trying to connect a newly created db connection, the password pop up will accept the long password token generated.

Tunnel version of the script

# you can get this from the portal, or determine it via the inputs your pass to this module in your code
POSTGRES_HOST=rpe-draft-store-prod.postgres.database.azure.com

ssh bastion-prod.platform.hmcts.net -L 5440:${POSTGRES_HOST}:5432
# expect no more output in this terminal you won't get an interactive prompt

# in a separate terminal run:
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv)

# this matches the `database_name` parameter you pass in the module
DB_NAME=draftstore

# make sure you update the product name in the middle to your product
DB_USER="DTS\ JIT\ Access\ draft-store\ DB\ Reader\ SC" # read access
#DB_USER="DTS\ Platform\ Operations\ SC" # operations team administrative access

psql "sslmode=require host=localhost port=5440 dbname=${DB_NAME} user=${DB_USER}"

Providers

Name	Version
azuread	n/a
azurerm	n/a
azurerm.postgres_network	n/a
null	n/a
random	n/a
terraform	n/a

Resources

Name	Type
azurerm_log_analytics_workspace.pgsql_log_analytics_workspace	resource
azurerm_monitor_action_group.db-alerts-action-group	resource
azurerm_monitor_diagnostic_setting.pgsql_diag	resource
azurerm_monitor_metric_alert.db_alert_cpu	resource
azurerm_monitor_metric_alert.db_alert_memory	resource
azurerm_monitor_metric_alert.db_alert_storage_utilization	resource
azurerm_postgresql_flexible_server.pgsql_server	resource
azurerm_postgresql_flexible_server_active_directory_administrator.pgsql_adadmin	resource
azurerm_postgresql_flexible_server_active_directory_administrator.pgsql_principal_admin	resource
azurerm_postgresql_flexible_server_configuration.pgsql_server_config	resource
azurerm_postgresql_flexible_server_database.pg_databases	resource
azurerm_postgresql_flexible_server_firewall_rule.pg_firewall_rules	resource
azurerm_resource_group.rg	resource
null_resource.set-schema-ownership	resource
null_resource.set-user-permissions-additionaldbs	resource
random_password.password	resource
terraform_data.trigger_password_reset	resource
azuread_group.db_admin	data source
azuread_service_principal.mi_name	data source
azurerm_client_config.current	data source
azurerm_key_vault_secret.email_address	data source
azurerm_subnet.pg_subnet	data source
azurerm_subscription.current	data source

Inputs

Name	Description	Type	Default	Required
action_group_name	The name of the Action Group to create.	string	"db_alerts_action_group_name"	no
admin_user_object_id	The ID of the principal to be granted admin access to the database server, should be the principal running this normally. If you are using Jenkins pass through the variable 'jenkins_AAD_objectId'.	any	null	no
alert_frequency	The frequency of the alert check.	string	"PT5M"	no
alert_severity	The severity level of the alert (1=Critical, 2=Warning ...).	number	1	no
alert_window_size	The period over which the metric is evaluated.	string	"PT15M"	no
auto_grow_enabled	Specifies whether the storage auto grow for PostgreSQL Flexible Server is enabled? Defaults to false.	bool	false	no
backup_retention_days	Backup retention period in days for the PGSql instance. Valid values are between 7 & 35 days	number	35	no
business_area	business_area name - sds or cft.	any	n/a	yes
charset	Specifies the Charset for the Azure PostgreSQL Flexible Server Database, which needs to be a valid PostgreSQL Charset.	string	"utf8"	no
collation	Specifies the Collation for the Azure PostgreSQL Flexible Server Database, which needs to be a valid PostgreSQL Collation.	string	"en_GB.utf8"	no
common_tags	Common tag to be applied to resources.	map(string)	n/a	yes
component	https://hmcts.github.io/glossary/#component	string	n/a	yes
cpu_threshold	Average CPU utilisation threshold	number	80	no
create_mode	The creation mode which can be used to restore or replicate existing servers	string	"Default"	no
email_address_key	Email address key in azure Key Vault.	string	""	no
email_address_key_vault_id	Email address Key Vault Id.	string	""	no
email_receivers	A map of email receivers, with keys as names and values as email addresses.	map(string)	{}	no
enable_qpi	Enables Query Performance Insight. Creates Log Analytics workspace and diagnostic setting needed	bool	false	no
enable_read_only_group_access	Enables read only group support for accessing the database	bool	true	no
enable_schema_ownership	Enables the schema ownership script. Change this to true if you want to use the script. Defaults to false	bool	false	no
env	Environment value.	string	n/a	yes
force_schema_ownership_trigger	Update this to a new value to force the schema ownership script to run again.	string	""	no
force_user_permissions_trigger	Update this to a new value to force the user permissions script to run again	string	""	no
geo_redundant_backups	Enable geo-redundant backups for the PGSql instance.	bool	false	no
high_availability	Overrides the automatic selection of high availability mode for the PostgreSQL Flexible Server. Generally you shouldn't set this yourself.	bool	null	no
kv_name	Update this with the name of the key vault that stores the single server secrets. Defaults to product-env.	string	""	no
kv_subscription	Update this with the name of the subscription where the single server key vault is. Defaults to DCD-CNP-DEV.	string	"DCD-CNP-DEV"	no
location	Target Azure location to deploy the resource	string	"UK South"	no
memory_threshold	Average memory utilisation threshold	number	80	no
name	The default name will be product+component+env, you can override the product+component part by setting this	string	""	no
pass_secret_name	Update this with the name of the secret that stores the single server password. Defaults to product-componenet-POSTGRES-PASS.	string	""	no
pgsql_admin_username	Admin username	string	"pgadmin"	no
pgsql_databases	Databases for the pgsql instance.	list(object({ name : string, collation : optional(string), charset : optional(string) }))	n/a	yes
pgsql_delegated_subnet_id	PGSql delegated subnet id.	string	""	no
pgsql_firewall_rules	Postgres firewall rules	list(object({ name : string, start_ip_address : string, end_ip_address : string }))	[]	no
pgsql_server_configuration	Postgres server configuration	list(object({ name : string, value : string }))	[ { "name": "backslash_quote", "value": "on" } ]	no
pgsql_sku	The PGSql flexible server instance sku	string	"GP_Standard_D2s_v3"	no
pgsql_storage_mb	Max storage allowed for the PGSql Flexibile instance	number	65536	no
pgsql_storage_tier	The storage tier, this should be left as null but may need to be overriden to allow increased storage.	string	null	no
pgsql_version	The PGSql flexible server instance version.	string	n/a	yes
product	https://hmcts.github.io/glossary/#product	string	n/a	yes
public_access	Specifies whether or not public access is allowed for this PostgreSQL Flexible Server. Defaults to false.	bool	false	no
resource_group_name	Name of existing resource group to deploy resources into	string	null	no
restore_time	The point in time to restore. Only used when create mode is set to PointInTimeRestore	any	null	no
sms_receivers	A map of SMS receivers, with keys as names and values as maps containing country code and phone number.	map(object({ country_code = string phone_number = string }))	{}	no
source_server_id	Source server ID for point in time restore. Only used when create mode is set to PointInTimeRestore	any	null	no
storage_threshold	Average storage utilisation threshold	number	80	no
subnet_suffix	Suffix to append to the subnet name, the originally created one used by this module is full in a number of environments.	string	null	no
trigger_password_reset	Setting this to a different value, e.g. '1' will trigger terraform to rotate the password.	string	""	no
user_secret_name	Update this with the name of the secret that stores the single server username. Defaults to product-componenet-POSTGRES-USER.	string	""	no
webhook_receivers	A map of webhook receivers, with keys as names and values as URLs.	map(string)	{}	no

Outputs

Name	Description
fqdn	n/a
instance_id	n/a
password	n/a
resource_group_location	n/a
resource_group_name	n/a
username	n/a

Contributing

We use pre-commit hooks for validating the terraform format and maintaining the documentation automatically. Install it with:

$ brew install pre-commit terraform-docs
$ pre-commit install

If you add a new hook make sure to run it against all files:

$ pre-commit run --all-files