← Back

PgHero query stats and a user per database

Update

This applies to PostgreSQL servers that Hatchbox installs, on regular and Dockerfile clusters. Managed DigitalOcean databases aren't affected.

Each database gets its own user

Until now, every database's user was a Postgres superuser. A superuser can read every other database on the server and run shell commands on it, so one leaked DATABASE_URL exposed everything on that server.


New databases now belong to their own user, without superuser. That user can still run your migrations and enable the common extensions, like pgcrypto, citext, hstore, pg_trgm and uuid-ossp. Existing databases keep the user they have.


Extensions that need a superuser, like postgis or timescaledb, can't be created from a migration anymore. Install them on the server and create them once as postgres. After that, enable_extension in your migrations finds the extension already there and moves on.

PostGIS

SSH into the database server and install the package for your PostgreSQL version (18 here), then create the extension in your app's database:

sudo apt install postgresql-18-postgis-3
sudo -u postgres psql -d your_database -c "CREATE EXTENSION postgis;"

TimescaleDB

Install it with Timescale's Ubuntu instructions. TimescaleDB also has to be preloaded, so add it next to pg_stat_statements, restart PostgreSQL, and create the extension:

sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements', 'timescaledb';"
sudo systemctl restart postgresql
sudo -u postgres psql -d your_database -c "CREATE EXTENSION timescaledb;"

These steps are for regular clusters. Dockerfile clusters run the official postgres image, which doesn't include PostGIS or TimescaleDB.

PgHero query stats work out of the box

New PostgreSQL servers load pg_stat_statements, and each new database has it installed, so PgHero's Queries page works with no setup. Each app only sees its own queries.


On an existing server, click Update on the server's page to turn it on. This restarts PostgreSQL, so pick a quiet time. Then run CREATE EXTENSION pg_stat_statements; in each existing database you want stats for, or use PgHero's Enable button.

If you customized shared_preload_libraries

Hatchbox now sets shared_preload_libraries when it updates a PostgreSQL server. It keeps the libraries your server already loads and adds pg_stat_statements, so nothing you set is removed. Where you set your list matters:

  • On the existing shared_preload_libraries line in postgresql.conf: nothing to do. Hatchbox copies your libraries into conf.d/hatchbox.conf next to pg_stat_statements.
  • At the end of postgresql.conf, under CUSTOMIZED OPTIONS: your line still wins, so pg_stat_statements won't load. Add it to your list, for example shared_preload_libraries = 'timescaledb,pg_stat_statements', and restart PostgreSQL.
  • With ALTER SYSTEM, including on Dockerfile clusters: your setting wins as well. On a Dockerfile cluster, Hatchbox adds pg_stat_statements to your list for you. On other servers, include it in your own ALTER SYSTEM list.

Questions? Email us.