PoWA 5.3.0

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick PoWA 5.3.0 on Ubuntu 24.04 as the software version and select your Region. Then choose Launch through EC2.

Choose m5.large, your key pair, the subnet and a security group as described above. A t3.medium is enough for PoWA analysing itself and one or two quiet servers.

PoWA's repository lives on the root volume. It grows with the number of distinct queries and the retention you keep (seven days by default), so give it room.

To launch with the AWS CLI instead, replace the values in angle brackets:

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --block-device-mappings 'DeviceName=/dev/sda1,Ebs={VolumeSize=30,VolumeType=gp3}' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=powa}]'

Version SSH user

PoWA 5.3.0 on Ubuntu 24.04 ubuntu

ssh -i <your-key.pem> ubuntu@<public-ip>

First boot sets the host name and generates the certificate and the cookie secret. It creates this instance's PostgreSQL cluster, the PoWA repository and the powa_ui role, and then starts powa-web and nginx:

systemctl show -p UnitFileState --value powa-firstboot.service
systemctl is-active postgresql@18-main powa-web nginx
sudo test -f /var/lib/sac/data/powa-firstboot.done && echo FIRSTBOOT_DONE

Confirm that the first-boot service is enabled, the three services are active and the first-boot marker is present:

enabled active active active FIRSTBOOT_DONE

Only SSH and nginx listen on the network. powa-web and PostgreSQL listen on localhost only. Check the listeners:

sudo ss -lntpH | awk '{print $4, $6}' | sed -E 's/users:\(\("([^"]+)".*/\1/' | grep -v '127.0.0.5' | sort -u

0.0.0.0:22 sshd 0.0.0.0:443 nginx 0.0.0.0:80 nginx 127.0.0.1:5432 postgres 127.0.0.1:8888 python3 [::]:22 sshd [::]:443 nginx [::]:80 nginx

A health check for a load balancer or uptime monitor answers without a sign-in:

curl -sk https://127.0.0.1/healthz

ok

The credentials file is readable by root only. It holds the URL, the user name and the password for this instance. Print it in your own session:

sudo cat /root/powa-credentials.txt

# PoWA 5.3.0 - generated on first boot by powa-firstboot.service
# These credentials are unique to this instance; none was baked into the image. Keep this file secret. POWA_URL=https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/ POWA_ADMIN_USERNAME=powa_ui POWA_ADMIN_PASSWORD=<POWA_ADMIN_PASSWORD>
#
# Open POWA_URL in a browser (self-signed certificate minted on this instance's first boot; port 80 redirects to https) # and sign in with POWA_ADMIN_USERNAME / POWA_ADMIN_PASSWORD (Server: main). powa-web signs in AS that PostgreSQL role: # it is a member of powa_admin and can read statistics, but is not a superuser. The postgres superuser has no password # (local peer login only:
sudo -u postgres psql).
# Populate the charts in a minute:
sudo powa-demo-workload Self-test:
sudo powa-selftest
# Change the served name:
sudo powa-set-hostname <your-domain>
# Outbound connections from PostgreSQL and powa-web are blocked except loopback; the guide shows the allow-list.
# User guide: https://secureanycloud.com/stacks/powa-5-3-0/

The URL uses the instance's public DNS name from EC2 instance metadata. Opening https://<public-ip>/ redirects you to that name. To check the file without showing the password, run:

sudo stat -c '%a %U:%G %n' /root/powa-credentials.txt
sudo grep -E '^POWA_(URL|ADMIN_USERNAME)=' /root/powa-credentials.txt | sed -E 's#https://[^/]+/#https://<host>/#'

600 root:root /root/powa-credentials.txt POWA_URL=https://<host>/ POWA_ADMIN_USERNAME=powa_ui

The image includes a self-test. Through nginx, it signs in as powa_ui and checks the pages, the request guards, powa-web's privileges, both localhost listeners and the outbound block. It leaves nothing behind:

sudo powa-selftest

SELFTEST-OK https 301 + healthz + anonymous / 302 -> /login/ + anonymous data 302/403 + sign-in page 403 form + powa_ui sign-in 302 -> /, user/password/server cookies Secure/HttpOnly/SameSite=Lax + servers list <local> + overview 200 + chart config changes 200 + next=//evil -> / + cross-site 403 + cross-origin POST 403 + sign-out 302 + wrong password refused (403, no cookie) + 444 unknown host + powa-web uid powaweb, no capabilities, 127.0.0.1:8888 only + PostgreSQL 127.0.0.1:5432 only + metadata 000 inside powa-web, PostgreSQL and nginx and internet 000 inside powa-web and PostgreSQL (controls 200/200) + config guard OK + postgres no password, powa_ui not superuser, no predefined role can log in

Open the URL from the credentials file. Your browser warns about the self-signed certificate the first time. Accept it for this host (Step 13 replaces it with your own).

Sign in with powa_ui and the password. Leave Server on main, which is this instance's own PostgreSQL.

After you sign in, the All servers page opens. The <local> entry is this instance's PostgreSQL 18, already being analysed.

The background worker takes a snapshot every minute. Count the snapshots and statements PoWA has recorded for the local server (server id 0):

sudo -u postgres psql -w -tAd powa -c "SELECT count(*) > 0 AS has_snapshots FROM powa_snapshot_metas WHERE srvid = 0"
sudo -u postgres psql -w -tAc "SELECT name, setting FROM pg_settings WHERE name IN ('powa.frequency','powa.retention','pg_qualstats.sample_rate') ORDER BY name"

t pg_qualstats.sample_rate|1 powa.frequency|60000 powa.retention|10080

The policy is a snapshot every minute (60000 ms), seven days of history (10080 minutes) and every predicate sampled. It lives in /etc/postgresql/18/main/conf.d/10-sac-powa.conf (Step 12).

A new instance has little to chart. PoWA plots the difference between snapshots, and a quiet server produces nearly identical ones.

The image ships a tool that creates a small powa_demo database, runs a real pgbench workload against it for 45 seconds and takes a snapshot on each side:

sudo powa-demo-workload

Pass a number of seconds for a longer run (sudo powa-demo-workload 120). When you are done, remove the demo database with sudo powa-demo-workload --drop. Reload the dashboard afterwards, and the charts show data.

Open the <local> server to see its overview. It charts query runtime and rate, block access split into shared buffer hits, operating system cache hits and real disk reads, connection activity and more, over the selected time range.

The tabs move between WALs, JIT, Background Writer / Checkpointer, Archiver / Replication, Database activity, Database Objects, System resources, SLRU and Wait Events. The time range control at the top right applies to every chart.

Choose a database (for example powa_demo) and scroll to Details for all queries. It lists every statement PoWA has seen, ranked, with calls, total and average time, blocks read, hit and dirtied, temporary blocks, I/O time and WAL volume. Select a statement to see the same metrics over time for that one query, with the predicates it filters on.

Wait events for all queries attributes lock waits to the statements that suffered them.

Index suggestions takes the predicates pg_qualstats captured and proposes indexes. It uses hypopg to cost each one against the real planner without building it. Choose Optimize this database to run it.

PoWA records every database in this instance's PostgreSQL. To get per-query predicates and index suggestions for a database, create the two extensions in it:

sudo -u postgres createdb orders
sudo -u postgres psql -d orders -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements' -c 'CREATE EXTENSION IF NOT EXISTS hypopg'

The predicates and index suggestions read PostgreSQL's column statistics (pg_statistic), which only a superuser can read by default. powa_ui is not a superuser. First boot therefore grants PoWA's administrators (powa_admin, which powa_ui belongs to) read access to pg_statistic in template1, postgres and powa. Every database you create afterwards inherits it.

A database created from template0 or restored from a dump does not inherit the grant. In that database, run sudo -u postgres psql -d <database> -c 'GRANT SELECT ON pg_catalog.pg_statistic TO powa_admin'.

PoWA can analyse other PostgreSQL servers in remote mode, for example Amazon EC2 or on-premises servers you can install extensions on. The statistics extensions run on the remote server. The powa-collector daemon on this instance snapshots it and stores the history in this repository.

Amazon RDS and Aurora do not offer the powa extension, so they cannot be added.

Follow the PoWA remote setup documentation. On this image, three things are specific.

Part 1. Allow the remote server's address. powa-web and PostgreSQL can reach localhost only. Add one IPAddressAllow= line per server to the shipped, empty allow-list drop-in of each service that has to reach it, then reload:

sudo tee -a /etc/systemd/system/powa-web.service.d/60-sac-egress-allow.conf <<'EOF' IPAddressAllow=<remote-server-ip>/32 EOF
sudo systemctl daemon-reload && sudo systemctl restart powa-web

Never allow 169.254.169.254 or fd00:ec2::254 (the instance metadata service). Check what each service can reach:

systemctl show -p IPAddressAllow --value powa-web
systemctl show -p IPAddressAllow --value postgresql@18-main

127.0.0.0/8 ::1/128 127.0.0.0/8 ::1/128

Part 2. Register the server in the repository. Do this as the PostgreSQL superuser over the local socket. Use a role on the remote server that has the pg_read_all_stats privilege. Its password is stored in the repository's powa_servers table, which only postgres and PoWA's administrators can read:

sudo -u postgres psql -d powa <<'SQL' SELECT powa_register_server(hostname => '<remote-server-ip>', port => 5432, alias => 'orders-db', username => 'powa_collector', password => '<your-server-password>', dbname => 'powa'); SQL

Part 3. Run powa-collector under the same block. powa-collector is a small Python program published on PyPI. It is not in Ubuntu's or the PostgreSQL project's package repositories. Install it in a virtual environment and configure it against this repository as the documentation describes, for example:

sudo apt-get install -y python3-venv
sudo python3 -m venv /opt/powa-collector
sudo /opt/powa-collector/bin/pip install powa-collector printf '{"repository": {"dsn": "postgresql:///powa"}, "debug": false}\n' | sudo tee /etc/powa-collector.conf

Run it as postgres, because it reaches this repository over the local socket. Give its service the same outbound rules as powa-web, so it too can reach only localhost and the servers you name:

sudo tee /etc/systemd/system/powa-collector.service <<'EOF' [Unit] Description=PoWA collector After=postgresql@18-main.service [Service] User=postgres ExecStart=/opt/powa-collector/bin/powa-collector.py Restart=on-failure IPAddressDeny=any IPAddressAllow=localhost IPAddressAllow=<remote-server-ip>/32 [Install] WantedBy=multi-user.target EOF
sudo systemctl daemon-reload && sudo systemctl enable --now powa-collector

/etc/postgresql/18/main/conf.d/10-sac-powa.conf sets powa.frequency (1 minute), powa.retention (7 days) and pg_qualstats.sample_rate (1, every predicate). These values are generous for one instance and a few servers. Relax them when you add busy servers.

The configuration tree is owned by root and is readable only by PostgreSQL. Write your changes as a separate root-owned file, then reload:

printf "powa.retention = '3d'\npg_qualstats.sample_rate = 0.1\n" > /tmp/20-local.conf
sudo install -o root -g postgres -m 0644 /tmp/20-local.conf /etc/postgresql/18/main/conf.d/20-local.conf && rm /tmp/20-local.conf
sudo systemctl reload postgresql@18-main

• In your DNS, point <your-domain> (an A record) at the instance's public IP address. An Elastic IP keeps that address stable.
• Tell the instance to use the domain. This sets the name nginx accepts and its request guards, updates the credentials file and generates a new self-signed certificate for the domain:

sudo powa-set-hostname <your-domain>

• For a certificate your browser trusts, put the certificate chain for <your-domain> in /etc/ssl/powa/powa.crt and its private key in /etc/ssl/powa/powa.key (root only, mode 600). Then run sudo systemctl reload nginx. Alternatively, put the instance behind an Application Load Balancer with an AWS Certificate Manager certificate that forwards to port 443 on the instance.

PostgreSQL listens on localhost because the repository holds the text of every query PoWA has seen. The safe way to use a desktop client is an SSH tunnel, which needs no change on the instance. Open the tunnel:

ssh -i <your-key.pem> -L 5432:127.0.0.1:5432 ubuntu@<public-ip>

Then connect to 127.0.0.1:5432, database powa, as powa_ui with the password from Step 4.

If clients in your VPC must connect directly, open 5432 to them only, and only with TLS:

• Put a certificate and key for the server in /etc/postgresql/18/main/, owned by root (key root:postgres mode 640).
• Add a root-owned conf.d file (as in Step 12) with listen_addresses = '127.0.0.1,<private-ip>', ssl = on, ssl_cert_file and ssl_key_file.
• Add a hostssl all <role> <your-vpc-cidr> scram-sha-256 line to /etc/postgresql/18/main/pg_hba.conf for a role with only the rights it needs, never postgres.
• Add IPAddressAllow=<your-vpc-cidr> to /etc/systemd/system/postgresql@18-main.service.d/60-sac-egress-allow.conf, run sudo systemctl daemon-reload and restart PostgreSQL.
• In the security group, allow TCP 5432 from <your-vpc-cidr> only. Never open 5432 to 0.0.0.0/0.

powa-web and PostgreSQL can reach localhost only. nginx is blocked from the instance metadata service and the special-purpose ranges:

for u in powa-web postgresql@18-main; do printf '%s deny=%s allow=%s\n' "$u" "$(systemctl show -p IPAddressDeny --value "$u" | tr ' ' '\n' | grep -cE '^(0\.0\.0\.0/0|::/0)$')" "$(systemctl show -p IPAddressAllow --value "$u")"; done systemctl show -p IPAddressDeny --value nginx | tr ' ' '\n' | sort | tr '\n' ' '; echo

powa-web deny=2 allow=127.0.0.0/8 ::1/128 postgresql@18-main deny=2 allow=127.0.0.0/8 ::1/128 168.63.129.16/32 169.254.169.254/32 192.0.0.0/24 198.18.0.0/15 224.0.0.0/4 240.0.0.0/4 fd00:ec2::254/128

deny=2 means everything (0.0.0.0/0 and ::/0) is denied apart from the allowed addresses. The block is two-way, so nothing outside the instance can reach powa-web or PostgreSQL directly either. It applies to the whole service, so every connection powa-web opens and every process PostgreSQL starts is blocked too.

powa-web runs as powaweb with no Linux capabilities and cannot gain any:

P=$(systemctl show -p MainPID --value powa-web) ps -o user= -p "$P" grep -E '^(CapEff|CapBnd|CapAmb|NoNewPrivs)' /proc/"$P"/status

powaweb CapEff: 0000000000000000 CapBnd: 0000000000000000 CapAmb: 0000000000000000 NoNewPrivs: 1

PostgreSQL accepts only two kinds of sign-in. The postgres superuser connects over the local socket by peer authentication (root's maintenance and PoWA's background worker), and powa_ui connects from localhost with a password. postgres has no password, and powa_ui is not a superuser:

sudo -u postgres psql -w -tA -c "SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules ORDER BY rule_number"
sudo -u postgres psql -w -tA -c "SELECT rolname, rolsuper, rolpassword IS NULL AS no_password FROM pg_authid WHERE rolname IN ('postgres','powa_ui') ORDER BY rolname"
sudo -u postgres psql -w -tA -d powa -c "SELECT has_table_privilege('powa_ui', 'pg_catalog.pg_statistic', 'SELECT'), has_table_privilege('powa_ui', 'public.pg_track_settings_history', 'SELECT')"

local|{all}|{postgres}||peer host|{all}|{powa_ui}|127.0.0.1|scram-sha-256 postgres|t|t powa_ui|f|f t|t

powa_ui reads PoWA's repository, the configuration-change history (pg_track_settings) and the column statistics behind the predicates and index suggestions (Step 10). It is not a superuser, so it cannot change your databases or run programs on the server.

Signed-out callers are sent to the sign-in page. nginx refuses a forced snapshot that another web site's page sends, and a post from a foreign Origin. A request for a host name the instance does not serve gets no answer at all:

H=$(sudo sed -n 's#^POWA_URL=https://\([^/]*\)/$#\1#p' /root/powa-credentials.txt)
curl -sk -o /dev/null -w 'signed-out overview: %{http_code}\n' --resolve "$H:443:127.0.0.1" -H 'Sec-Fetch-Site: same-origin' "https://$H/server/0/overview/"
curl -sk -o /dev/null -w 'cross-site forced snapshot: %{http_code}\n' --resolve "$H:443:127.0.0.1" -X POST -H 'Sec-Fetch-Site: cross-site' "https://$H/force_snapshot/0/"
curl -sk -o /dev/null -w 'cross-origin POST: %{http_code}\n' --resolve "$H:443:127.0.0.1" -X POST -H 'Origin: https://evil.example' "https://$H/login/"
curl -sk -o /dev/null -w 'unknown host name: %{http_code}\n' --resolve evil.example:443:127.0.0.1 https://evil.example/ || true

signed-out overview: 302 cross-site forced snapshot: 403 cross-origin POST: 403 unknown host name: 000

Only root can read the credentials, the TLS key and powa-web's configuration, which holds the cookie secret. The powaweb service can also read the configuration. The PostgreSQL configuration is owned by root:

sudo stat -c '%a %U:%G %n' /root/powa-credentials.txt /etc/ssl/powa/powa.key /etc/powa-web.conf /etc/postgresql/18/main/pg_hba.conf /etc/postgresql/18/main/conf.d

600 root:root /root/powa-credentials.txt 600 root:root /etc/ssl/powa/powa.key 640 root:powaweb /etc/powa-web.conf 640 root:postgres /etc/postgresql/18/main/pg_hba.conf 755 root:postgres /etc/postgresql/18/main/conf.d

Other notes:

• Sign-in limits. nginx allows about 10 sign-in attempts a minute per address (a burst of 5) and answers more with 429. It uses the address that connected to nginx and replaces any forwarding headers a client sends.
• Who can reach it. Anyone who signs in sees the text of every query PoWA records. Open ports 80 and 443 in the security group only to the networks your administrators work from.
• The build. /usr/share/doc/sac-powa/BUILD-INFO.txt records the release, its checksums and the library versions. NOTICE.txt next to it records the licences.
• Logs. nginx logs are in /var/log/nginx. powa-web and PostgreSQL log to the journal (journalctl -u powa-web, journalctl -u postgresql@18-main).

Ubuntu, PostgreSQL and the PoWA extensions update from their package repositories. Ubuntu security updates install automatically. To apply all updates now, run:

sudo apt-get update && sudo apt-get upgrade -y

After an upgrade of the postgresql-18-powa package, update the extension in the repository with sudo -u postgres psql -d powa -c 'ALTER EXTENSION powa UPDATE'. powa-web refuses to sign in when its version and the extension's version differ in major or minor number.

New powa-web releases arrive as new versions of this listing. Read the PoWA release notes before you move.