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

Component	Requirement
Database	An existing RDS PostgreSQL instance or Aurora PostgreSQL cluster. Terraform does not create one.
Atatus Infra Agent	4.3.0 or higher for IAM authentication. Installed for you from the tar archive.
Terraform	1.5 or higher, with `hashicorp/aws` 5.x
AWS credentials	Able to create IAM roles, an EC2 instance, and security group rules
Network	A subnet with a route out: a NAT gateway, or a public IP

Terraform reads AWS credentials from the environment, so select a profile with AWS_PROFILE rather than a command line flag. If AWS_ACCESS_KEY_ID is exported it takes priority over AWS_PROFILE, so run unset AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY AWS_SESSION_TOKEN first.

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

--apply-immediately applies every pending modification on that database, not only this one. Anything queued for the next maintenance window is applied now too, and some of those changes reboot the instance. Check what is pending first with aws rds describe-db-instances --db-instance-identifier <ID> --query 'DBInstances[0].PendingModifiedValues', or leave the flag off and let this apply in the next window.

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:

Parameter	Value	Apply	Purpose
`shared_preload_libraries`	`pg_stat_statements`	static	Required. Without it there are no query metrics at all.
`track_activity_query_size`	`4096`	static	Captures longer SQL text. At the default, longer statements are truncated and cannot be explained.
`pg_stat_statements.track`	`all`	dynamic	Includes statements run inside functions and procedures.
`pg_stat_statements.max`	`10000`	dynamic	Retains more normalized queries. Worth raising on a busy database.
`pg_stat_statements.track_utility`	`off`	dynamic	Skips `PREPARE` and `EXPLAIN` noise.
`track_io_timing`	`on`	dynamic	Optional. Adds block read and write timings to query metrics.

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.

If you do need to change it, that reboot restarts your database, not the agent. Every open connection is dropped while it comes back, usually for a minute or two. On Multi AZ it fails over to the standby unless you pass --no-force-failover. On Aurora, reboot the writer, which triggers a failover. Do it in a maintenance window.

This is the failure that looks like success. CREATE EXTENSION pg_stat_statements succeeds whether or not the library is loaded, so a parameter group edited without a reboot gives you an agent that connects, reports metrics, and shows an empty query list, with nothing saying why. The agent host checks the running value at boot and writes a notice to its journal if it is missing.

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}'

The agent host needs outbound access to download the agent and reach Atatus. A private subnet needs a NAT gateway. A public subnet with only an internet gateway needs a public address, so set associate_public_ip_address = true. With neither, the host boots, fails to install, and never retries, because its boot script runs only once.

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.

The master password is a real credential. It is read at boot, used once, and never written to disk, but the host holds it in memory while it runs. If that is not acceptable in your environment, create the role by hand instead. This is a convenience, not a security improvement.

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;"

GRANT rds_iam is a one way switch for the role it is applied to. Once a role holds it, that role can never authenticate with a password again, and the only way back is to drop and recreate it. Never grant it to your master user.

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.

Without that function you get query metrics but no execution plans, and the UI reports schema "atatus" does not exist. The automatic path above creates it for you in the database it connects to; for additional databases in the same cluster, run this in each one:

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;

Keep the role if you plan to redeploy. It holds rds_iam, dropping it is the only way to undo that grant, and you would have to recreate and re grant on the next run.

​Before you begin

​Two prerequisites Terraform will not change for you

​IAM database authentication

​The parameter group

​Get the Terraform

​Store the license key

​Configure

​Monitoring several databases

​Create the database role

​Automatically, at first boot

​By hand

​Apply

​Verify

​Monitoring both engines from one host

​Removing it

​Related pages

Before you begin

Two prerequisites Terraform will not change for you

IAM database authentication

The parameter group

Get the Terraform

Store the license key

Configure

Monitoring several databases

Create the database role

Automatically, at first boot

By hand

Apply

Verify

Monitoring both engines from one host

Removing it

Related pages