Set Up the Agent with Terraform for PostgreSQL

This page provisions the agent side of Database Monitoring for PostgreSQL with Terraform: an EC2 host running the Atatus Infrastructure Agent, the IAM policy that lets it authenticate, and the security group rule that lets it reach your database.

The same stack can also monitor Amazon DocumentDB from that one host. See Set Up the Agent with Terraform for DocumentDB for the DocumentDB half, and Monitoring both engines below for running them together.

Before you begin

Two prerequisites Terraform will not change for you

Both carry consequences for a running database, so they stay yours to make. Terraform checks them and tells you, and never edits them.

IAM database authentication

Required for the agent to authenticate without a password. It is a dynamic change and needs no reboot:

$ aws rds modify-db-instance --db-instance-identifier <DB_IDENTIFIER> \
    --enable-iam-database-authentication --apply-immediately

For an Aurora cluster the setting lives on the cluster:

$ aws rds modify-db-cluster --db-cluster-identifier <CLUSTER_IDENTIFIER> \
    --enable-iam-database-authentication --apply-immediately

Terraform warns at plan time when this is off on an Aurora cluster. For a DB instance it warns that it cannot tell, because the aws_db_instance data source does not expose the flag at all. Check it yourself:

$ aws rds describe-db-instances --db-instance-identifier <DB_IDENTIFIER> \
    --query 'DBInstances[0].IAMDatabaseAuthenticationEnabled'

The parameter group

Database Monitoring reads its query data from pg_stat_statements, and a few parameters control how much of it you get. Set these in your RDS or Aurora parameter group, or in postgresql.conf on a self hosted server:

The two marked static do nothing until the database is restarted. The rest take effect on the next parameter group refresh.

Check what is actually running:

SHOW shared_preload_libraries;
SHOW track_activity_query_size;
SHOW pg_stat_statements.track;

Many recent default parameter groups already include pg_stat_statements, so check before you plan any downtime.

Get the Terraform

$ git clone https://github.com/atatus/atatus-database-monitoring-sample.git
$ cd atatus-database-monitoring-sample/terraform/aws/ec2
$ cp terraform.tfvars.example terraform.tfvars

These are examples to copy into your own repository and adapt. There is no registry entry and no version to pin, so fork the directory rather than depending on it.

Store the license key

Keeping the key in Parameter Store means it never lands in user_data, in a saved plan file, or in state:

$ aws ssm put-parameter --name /atatus/license_key \
    --type SecureString --value '<YOUR_LICENSE_KEY>'

Configure

A PostgreSQL only setup, with documentdb_clusters left empty:

aws_region                  = "us-east-2"
vpc_id                      = "vpc-0123456789abcdef0"
subnet_id                   = "subnet-0123456789abcdef0"
associate_public_ip_address = true

use_managed_authentication = true

databases = {
  main = {
    identifier        = "prod-pg"
    kind              = "instance" # or "cluster" for Aurora
    security_group_id = "sg-0123456789abcdef0"
    db_name           = "postgres"
  }
}

documentdb_clusters = {}

db_username                    = "atatus"
license_key_ssm_parameter_name = "/atatus/license_key"
os_family                      = "amazon-linux"
instance_type                  = "t3.small"

If you do not know your VPC, subnet or security group, read them off the database:

$ aws rds describe-db-instances --db-instance-identifier <DB_IDENTIFIER> \
    --query 'DBInstances[0].{SG:VpcSecurityGroups[].VpcSecurityGroupId,SubnetGroup:DBSubnetGroup.DBSubnetGroupName}'
$ aws rds describe-db-subnet-groups --db-subnet-group-name <SUBNET_GROUP> \
    --query 'DBSubnetGroups[0].{VpcId:VpcId,Subnets:Subnets[].SubnetIdentifier}'

Monitoring several databases

databases is a map, and one agent host collects from every entry. There is no reason to run this stack twice for two databases in the same VPC:

databases = {
  orders = {
    identifier        = "orders-pg"
    security_group_id = "sg-0123456789abcdef0"
    kind              = "instance"
    db_name           = "orders"
  }

  analytics = {
    identifier        = "analytics-aurora"
    security_group_id = "sg-0123456789abcdef1"
    kind              = "cluster"
    db_name           = "analytics"
    username          = "atatus_ro"
    labels            = { team = "data" }
  }
}

Each database gets its own entry in the rendered config with its own aws block, so each token is signed for its own endpoint, and its own rds-db:connect policy, because the resource id differs per database. Databases that share a security group and port share one ingress rule.

The map key names the database in the UI, which matters once one agent reports several of them.

Create the database role

The agent authenticates as a PostgreSQL role that has to exist before it starts. There are two ways to create it.

Automatically, at first boot

Point the entry at an SSM parameter holding the database master password and the agent host creates the role itself. The host is inside the VPC, so it reaches a database with no public access, which your laptop cannot:

$ aws ssm put-parameter --name /atatus/pg_master_password \
    --type SecureString --value '<MASTER_PASSWORD>'

databases = {
  main = {
    identifier        = "prod-pg"
    kind              = "instance"
    security_group_id = "sg-0123456789abcdef0"

    bootstrap_master_password_ssm_parameter_name = "/atatus/pg_master_password"
    bootstrap_master_username                    = "postgres"
  }
}

The host runs CREATE ROLE, GRANT pg_monitor, CREATE EXTENSION pg_stat_statements, GRANT rds_iam, and creates the atatus schema and the atatus.explain_statement() function that execution plans need. Every statement is guarded, so a rebuilt host re-runs all of it without error, and the rds_iam grant is skipped on a server that has no such role.

By hand

Connect as your master user and run the bootstrap SQL once per cluster:

$ psql "host=<ENDPOINT> port=5432 dbname=postgres user=<MASTER> sslmode=require" \
    -v ON_ERROR_STOP=1 \
    -v atatus_password="$(openssl rand -base64 24)" \
    -f ../../_bootstrap/postgres-iam-setup.sql
$ psql -c "GRANT rds_iam TO atatus;" -c "ALTER USER atatus PASSWORD NULL;"

That script also creates the atatus schema and the atatus.explain_statement() function, which is what the agent calls to collect execution plans. Both are per database, so run it in every database you want plans from.

CREATE SCHEMA IF NOT EXISTS atatus;
GRANT USAGE ON SCHEMA atatus TO atatus;
GRANT USAGE ON SCHEMA public TO atatus;

CREATE OR REPLACE FUNCTION atatus.explain_statement(l_query TEXT, OUT explain JSON)
RETURNS SETOF JSON AS $$
DECLARE curs REFCURSOR; plan JSON;
BEGIN
  OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
  FETCH curs INTO plan;
  CLOSE curs;
  RETURN QUERY SELECT plan;
END;
$$ LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT SECURITY DEFINER;

REVOKE ALL ON FUNCTION atatus.explain_statement(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION atatus.explain_statement(TEXT) TO atatus;

If your database has no public access, your laptop cannot reach it. Tunnel through the agent host with Session Manager, or use the automatic path above:

$ aws ssm start-session --target <INSTANCE_ID> \
    --document-name AWS-StartPortForwardingSessionToRemoteHost \
    --parameters '{"host":["<ENDPOINT>"],"portNumber":["5432"],"localPortNumber":["15432"]}'

Apply

$ terraform init
$ terraform plan
$ terraform apply

Verify

$ terraform output monitored_target_count
$ terraform output -raw ssm_start_session_command
$ terraform output -raw verification_command

Run the session command to open a shell on the host, then paste the verification command inside it. It checks that the agent is running, reports the installed version, shows the integration in the logs, and mints a token to prove the host can log in.

To read the boot script's own account of what happened:

$ sudo journalctl -t atatus-bootstrap --no-pager

Monitoring both engines from one host

The agent reads conf.d/postgresql.d/ and conf.d/mongodb.d/ independently, so one machine covers PostgreSQL and Amazon DocumentDB together. Add a documentdb_clusters map alongside databases:

use_managed_authentication            = true
documentdb_use_managed_authentication = true

databases = {
  main = {
    identifier        = "prod-pg"
    kind              = "instance"
    security_group_id = "sg-0123456789abcdef0"
  }
}

documentdb_clusters = {
  prod = {
    cluster_identifier = "docdb-prod"
    security_group_id  = "sg-0123456789abcdef1"
  }
}

There is no second agent, no second host and no second license. At least one of the two maps must be non empty; a plan time precondition rejects a configuration that would build a host monitoring nothing.

The DocumentDB side has its own prerequisites, and its IAM works by a different mechanism. See Set Up the Agent with Terraform for DocumentDB.

Removing it

$ terraform destroy

Your database is untouched. The only change to it was an ingress rule on its security group, which is removed. The atatus role and the SSM parameters are not Terraform's and stay behind:

DROP USER atatus;

Related pages

• Set Up the Agent with Terraform for DocumentDB
• Managed Authentication for AWS RDS and Aurora PostgreSQL
• Database Monitoring for AWS RDS PostgreSQL