ManuelThanks a lot
On Thursday, October 22, 2026 at 01:53:08 PM EDT, Manuel Reyes Bravo
<[email protected]> wrote:
Hi Bharani,
Short answer: no. A RAISE WARNING in a login event trigger won't reliably reach
API or REST clients, and probably not DBeaver either. It is the wrong tool for
this job.
WHY THE WARNING ONLY WORKS IN PSQL AND PGADMIN
- A WARNING travels as a protocol NoticeResponse. psql prints it, and pgAdmin
happens to show it. Most other clients do something different:
* JDBC (which DBeaver uses) puts it in Connection.getWarnings().
It's usually
not shown at connect time.
* psycopg, node-postgres and Npgsql hand it to a callback or event handler.
If the app doesn't register one, the warning is dropped.
* REST/API services never pass it on to the end user. At best it ends up
in an application log.
- Connection pools (HikariCP, SQLAlchemy, PgBouncer and so on) open connections
once and reuse them. The trigger fires when the pool connects, not on each API
request. Behind PgBouncer, the notice goes to PgBouncer, not to the client.
- A client can connect with options=-c client_min_messages=error and never see
the warning at all.
- Login event triggers belong to a single database. If the refresh drops and
restores that database, the trigger is dropped too, during exactly the window
you want it active. After the restore you get whatever trigger was in the
source dump.
- There's also a risk: a buggy login trigger that raises an error locks out
everyone it doesn't skip. The escape hatch is event_triggers = off, which
needs a real superuser. On Azure Flexible Server you don't have a real
superuser.
WHAT WORKS: BLOCK, DON'T WARN
Every client surfaces a failed connection or a terminated session. Not every
client surfaces a warning. A common pattern for refreshing non-prod
environments:
1. Announce it outside the database. Use your HTML status page, a Teams/Slack/
email notice, and the app's own maintenance mode or health check. Drive the
status page from the refresh pipeline itself, not by hand, so it can't drift
from reality.
2. Restore into a new database, then swap. This is the cleanest option:
-- restore into app_new while app stays online
ALTER DATABASE app ALLOW_CONNECTIONS false;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'app' AND pid <> pg_backend_pid();
ALTER DATABASE app RENAME TO app_old;
ALTER DATABASE app_new RENAME TO app;
-- re-apply grants/owners if they differ from prod, then drop app_old later
Downtime is a few seconds. Clients get a clear error ("terminating
connection due to administrator command", then 'database "app" does not
exist' or "not currently accepting connections"), and every driver and GUI
shows that.
3. If you refresh in place, close the door explicitly:
- ALTER DATABASE app ALLOW_CONNECTIONS false; blocks everyone, admins
included. Or use REVOKE CONNECT ON DATABASE app FROM PUBLIC, <app_roles>;
or ALTER ROLE app_user NOLOGIN; for each application role.
- Kill the existing sessions with pg_terminate_backend, or use
DROP DATABASE ... WITH (FORCE) (PG13+).
- Reverse all of this as the last step of the pipeline.
4. Optionally keep a login trigger, but make it raise an ERROR with a clear
message ("database refresh in progress until HH:MM, see <status url>").
Gate it on a flag table so that turning it off is a single UPDATE. Then
every client, API or GUI, gets a failed connection with your message in it.
Still, cover this with tests, and keep a non-skipped admin path so a bug
can't lock you out.
A side note: "Azure RDS flexible server" mixes two products. On Azure Database
for PostgreSQL Flexible Server you can't edit pg_hba.conf or start in
single-user/restricted mode. Network rules plus the steps above are your levers
there. On a self-managed VM you can also swap in a pg_hba.conf that rejects
application users and reload.
In short: tell people through the status page, and block them with
ALLOW_CONNECTIONS, REVOKE CONNECT/NOLOGIN and pg_terminate_backend. Don't
depend on clients displaying a warning.
Regards,
Manu
