Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKDoJ-00000000k87-0D3N for pgsql-general@arkaria.postgresql.org; Fri, 23 Oct 2026 11:51:47 +0000 Received: from localhost ([127.0.0.1]:60098 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xKDoI-0000000FcyJ-1C6u for pgsql-general@arkaria.postgresql.org; Fri, 23 Oct 2026 11:51:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:33954) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKDoH-0000000FcyA-2Fl2 for pgsql-general@lists.postgresql.org; Fri, 23 Oct 2026 11:51:45 +0000 Received: from sonic322-10.consmr.mail.ne1.yahoo.com ([66.163.189.33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xKDoE-00000000Zob-2Sve for pgsql-general@postgresql.org; Fri, 23 Oct 2026 11:51:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1792756299; bh=nPH9grrJK3JUwAAYdy9WBeRoyzrm9vjQrwaO4jZBmY8=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=GhqiKRa9vgVtMo4NCGWGvmwibZ0kM5kMjvoKdlJFDNq16lAW+AgfvYDQdjRyGsLVkt0F6uoQKPioXs+0Zi3H0cUMgoykSsZYh64mlkrRKxygMUAzccln79h55OXsGTHP3DZ1208fT+Uoy6jgXMupvGMliy+QQC4wmMZSC3F7pMjp/WREN5ET+OSzBdidpC8VGj06axEFKYbjhgpQaGHlsMIweoBmtfXqXjHNpsJvucr/VB68/lPGy6ZcOMatAjmztr/UZgSVmnpFPfVFu7AHKIklKfvpoXMkEGT0nmsRoRUEenYz60FUU/iSaH0m1oAYuugK09y0CzeSQ1xvCNzEeQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1792756299; bh=IAsKNjK+pKeqjcHs6P6cp0Ug8rXtO1c778MKkIKtAjc=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=c/C0cHScCdB1ttwcJGUY2EqnWb2juqPmf+y1yYPjlOdSAmv9JayTpw5k7h3831R2wxrwGI3gLfSRNpi3YQPdBqDtdMNNZWRIfhnJKQd3EwMkfpVMcpjrocK0kNnAAhG8N08n47Di8kKlPURuwhSAU0RXqG8Xbqee1dzD7tJjGffuf8JmSZcF6YUTZfm0xjHOB7paI4cSIjiaQxAa4FJ7takBOtWozKCrzwYWbCm01Z6hffKXAivYb6o3slmUDTwgpn62EYc6sLAeOEI/QbhDc30DLME9tiw8d5L5RxNVX4Rl9lJTJ1Mf9v5EhIvruAJsLKuLnA9U4jVez2KbmUR8mg== X-YMail-OSG: Ue0qBwsVM1kgVoAVwyPSSStYif_sdPzP16MiYL.RJk1M865yDr2khngHF5btPzS 2GFctZ1vJ.7nD6ZspvtpiSTtKa6EcvhMGDBZGAnU0q8k.JC3sr2MFUrUDs6LM.ezPgLCYV4Mvbyi xdXcXZltar_YonuOVomZF4RwWUn6yh4bIgPhzkQOLR0GQt5M5iENzdm_Qtekhlk_2dE5p3VtPWxD Oph4btvhdlaTBiuMyebOUAut9pyDssqA.rCVtjcuEM.W5ZJVtRgTQ_OF7OU6CbCFvFSMPtde6UfA diS0MnedOMQYDqoToA4S1xoDadWLa.bAP.j2Hzb1WvgGm1St5HTPQFFFNRM1Jh5k6LN3xW9vOMQR DG2uQWdi9ZOGsjzHcmSPzlRM3sYgLO0oJ4ihH3_THy1eRsb.Cv0GvGYtpXtsOT7vAs9Sjamewk._ CZ9cvRw.YruPN6bI9wwjmzihOIProP5J_FysIWw7xoTHKln5OiQrpIa5Len5xkV8cr4jE2qECl3E p1V1seOomnxZYLXuSBgQTXho.rVzrR9J_9xOx3iU6sJp3QNAdecgLr1fty.QdxBWEVizopTeLpJe 6pE.nuZ2X9GLuqWuyq50boQFVaiME01ce6zL9EPFelrTTMNEP6_5d8SikVRjUnbf8ld4eh3vCD1y MmvIUyLy2xOTRkwAww8Ui.ph7Ryx9mPUd_7oAQVVCbRgB1MszT1b.5aUwntoI8xCQaCSrA9mX1KS VyJpwgMObRpw4nfqfcomAcSrqYXvpssMvX5Zy97i6JPI1MXFqEmC_XD0GCymR8RApvlzNiM2bvis I7icrkMe4ZxfG2GJnkZSXmDmJyDpJ4I2rIWcvswJEyjyBLrcPo5e6eVK2KNbTW95TzxDQUmIfzJF Weh0St72k_9Pvcoc7F_bzYOa4UvREcB8eidOE4XY3l39Ly6n_5dlWpmSlZwSrIUXl8viIWTNwj8M 1h.P3QypL2y8OgELwkar8Ru7RqmHvKo2m4l0uPpuUIEKymR_pdAqO2jDmVXSDV0tbh3NC5SxAZ2P W3DRpkNJFzpiLH5hWMlEXaKDVpBl6Dz5ct3ZOgem3Xz65UWWLYqegiKIDxf95c.GCtvnasDM72H0 XOrMQ9YBD8AEAGr59aOKAq3IdIL2ffVTriIRChfg_BPSjONBjkWbkgz_Tj.Zc1rJWAPBwG64ojY5 5n4C2PkOMi6r.L3mMvrXNZG4zlM76S8Dcjeb01WHxdlp9fwkke0W47XhDQSo4zmHiZCADoSPHZGY .SgIeruWBa7TUpdC8Z7Ju5SaNJgXkqNFvQLgXrNXlzZFIp.ZOzRpckUORMTsz7ZyO2e9QlyuEXSt NG1Y2Yi_EppXG2pItUeamA7ycRYr2RMzK_UrDKhcIYt.L.QkqWOIkpIqVoHlGG4G1JcjO2r8Phz0 5qzcEk.u6tdL3.NKtRCas_xnhtmdsYofFAC5iTnT4grcWXL4gg9FA7urhuMcp0oNsELJw.shQwdM tytTeqH_VfQBqfxW_RP8lf_Rfasi4Axn4DoOCK6ALWdjms3Fk48zOZ.0v3kj3QhROEEnHAqrkvtV uCoKXrohNK.LA.qapBAI4go3wP6j9uezDB6v.OBAOrEyQnEssn8CSvyhrbd3WjTfySTkNDIi83Z8 uo.ocFjemInHEnsbkWWDbcsgZK1DfZVwarBgv3vD6SPpI8amXjKq5IECj7zEB4iqzvaH94uVYs2Y VgdxGhyKAGU3.f8yokYVUdLXd5LaHjmpssucewTvQHO2CrBTTA1H8X.9dLSc6ghQ7XJDorBlm5Xf EyKRCnslvjtEQAqug93jEfGDT2rqOhKSVKHx6fphbXptRFZgFIlKy3PyCu71gOpbXNnH.uHHC3Mo b93ElJgT8376csZJYgaPsMFBLXMc8NEIrSOzY92b.1ZD9IopyrSyUBRXg5gHosIMpx10GJrZyr.S 5AkFjDo4PrpQiucOexemDiXgj.a_N9x4gd69rdkhxFDc.AfTgHo4115qtPfrYkGo3GRvOjVoLrQC CqvecDZZdKLcAgNHGqqo7EAoBlbLIo4LRfErn7gtMRZUS1jmhXMsAwUQQGRLmk7FWce9IhWGqMbG 7bwWAWJy.1iobAPUvfyTOE60N_kGgDwnwZgTRdZbn8R67oCQA7698ygfsUq5DtS5.cOXZwCt6vHQ P.yaaTH52PkvdzHhzd_nw_4JLRDu7_gHcuONLHN0QY4Zvu6Gn_sKgBQYRoxfwX3ji4LPzfnb0j8C CnQaU3xyN1JU6Ue7Ngtlyf2UO8eYWPH8kD3BB2KuWRQ4hDG5pZfaK3dV2uHWdR5bgKdbugKYiH8U .vOB7ydvmqOez1.Ez6suOy6G_.o97QnlWK7ho2Q-- X-Sonic-MF: X-Sonic-ID: 9d5415b6-aad1-4edb-b27b-decdcc1d5942 Received: from sonic.gate.mail.ne1.yahoo.com by sonic322.consmr.mail.ne1.yahoo.com with HTTP; Fri, 23 Oct 2026 11:51:39 +0000 Date: Fri, 23 Oct 2026 11:51:34 +0000 (UTC) From: Bharani SV-forum To: Manuel Reyes Bravo Cc: "pgsql-general@postgresql.org" , "ronljohnsonjr@gmail.com" , "adrian.klaver@aklaver.com" Message-ID: <2018233699.2410780.1792756294465@mail.yahoo.com> In-Reply-To: References: <1634079310.2426511.1792690729718.ref@mail.yahoo.com> <1634079310.2426511.1792690729718@mail.yahoo.com> Subject: Re: Best Practise during DB env refresh (Prod to Non Prod) and Pro's and con's of using "DB level - Logon trigger" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2410779_1735337722.1792756294463" X-Mailer: WebService/1.1.26740 YMailNovation Content-Length: 12766 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2410779_1735337722.1792756294463 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable ManuelThanks a lot On Thursday, October 22, 2026 at 01:53:08 PM EDT, Manuel Reyes Bravo wrote: =20 =20 Hi Bharani, Short answer: no. A RAISE WARNING in a login event trigger won't reliably r= each 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 pgAdm= in =C2=A0 happens to show it. Most other clients do something different: =C2=A0 =C2=A0 * JDBC (which DBeaver uses) puts it in Connection.getWarnings= (). It's usually =C2=A0 =C2=A0 =C2=A0 not shown at connect time. =C2=A0 =C2=A0 * psycopg, node-postgres and Npgsql hand it to a callback or = event handler. =C2=A0 =C2=A0 =C2=A0 If the app doesn't register one, the warning is droppe= d. =C2=A0 =C2=A0 * REST/API services never pass it on to the end user. At best= it ends up =C2=A0 =C2=A0 =C2=A0 in an application log. - Connection pools (HikariCP, SQLAlchemy, PgBouncer and so on) open connect= ions =C2=A0 once and reuse them. The trigger fires when the pool connects, not o= n each API =C2=A0 request. Behind PgBouncer, the notice goes to PgBouncer, not to the = client. - A client can connect with options=3D-c client_min_messages=3Derror and ne= ver see =C2=A0 the warning at all. - Login event triggers belong to a single database. If the refresh drops an= d =C2=A0 restores that database, the trigger is dropped too, during exactly t= he window =C2=A0 you want it active. After the restore you get whatever trigger was i= n the =C2=A0 source dump. - There's also a risk: a buggy login trigger that raises an error locks out =C2=A0 everyone it doesn't skip. The escape hatch is event_triggers =3D off= , which =C2=A0 needs a real superuser. On Azure Flexible Server you don't have a re= al =C2=A0 superuser. WHAT WORKS: BLOCK, DON'T WARN Every client surfaces a failed connection or a terminated session. Not ever= y 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/Sla= ck/ =C2=A0 email notice, and the app's own maintenance mode or health check. Dr= ive the =C2=A0 status page from the refresh pipeline itself, not by hand, so it can= 't drift =C2=A0 from reality. 2. Restore into a new database, then swap. This is the cleanest option: =C2=A0 =C2=A0 -- restore into app_new while app stays online =C2=A0 =C2=A0 ALTER DATABASE app ALLOW_CONNECTIONS false; =C2=A0 =C2=A0 SELECT pg_terminate_backend(pid) FROM pg_stat_activity =C2=A0 =C2=A0 =C2=A0 WHERE datname =3D 'app' AND pid <> pg_backend_pid(); =C2=A0 =C2=A0 ALTER DATABASE app=C2=A0 =C2=A0 RENAME TO app_old; =C2=A0 =C2=A0 ALTER DATABASE app_new RENAME TO app; =C2=A0 =C2=A0 -- re-apply grants/owners if they differ from prod, then drop= app_old later =C2=A0 Downtime is a few seconds. Clients get a clear error ("terminating =C2=A0 connection due to administrator command", then 'database "app" does = not =C2=A0 exist' or "not currently accepting connections"), and every driver a= nd GUI =C2=A0 shows that. 3. If you refresh in place, close the door explicitly: =C2=A0 =C2=A0 - ALTER DATABASE app ALLOW_CONNECTIONS false; blocks everyone= , admins =C2=A0 =C2=A0 =C2=A0 included. Or use REVOKE CONNECT ON DATABASE app FROM P= UBLIC, ; =C2=A0 =C2=A0 =C2=A0 or ALTER ROLE app_user NOLOGIN; for each application r= ole. =C2=A0 =C2=A0 - Kill the existing sessions with pg_terminate_backend, or us= e =C2=A0 =C2=A0 =C2=A0 DROP DATABASE ... WITH (FORCE) (PG13+). =C2=A0 =C2=A0 - 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 =C2=A0 message ("database refresh in progress until HH:MM, see = "). =C2=A0 Gate it on a flag table so that turning it off is a single UPDATE. T= hen =C2=A0 every client, API or GUI, gets a failed connection with your message= in it. =C2=A0 Still, cover this with tests, and keep a non-skipped admin path so a= bug =C2=A0 can't lock you out. A side note: "Azure RDS flexible server" mixes two products. On Azure Datab= ase 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 le= vers 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 =20 ------=_Part_2410779_1735337722.1792756294463 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Manuel
Thanks a lot

=20
=20
On Thursday, October 22, 2026 at 01:53:08 PM EDT, Manue= l Reyes Bravo <manuelreyesbravo@gmail.com> wrote:


Hi Bharani,

Short answer: no. A RAISE WARNING in a login ev= ent trigger won't reliably reach
API or REST clie= nts, 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 s= how it. Most other clients do something different:
    * JDBC (which DBeaver uses) puts it in Connection.getWarnin= gs().
It's usually
&nbs= p;     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.
  &nb= sp; * REST/API services never pass it on to the end user. At best it ends u= p
      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
<= div dir=3D"ltr">  request. Behind PgBouncer, the notice goes to PgBoun= cer, not to the client.
- A client can connect wi= th options=3D-c client_min_messages=3Derror 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, d= uring exactly the window
  you want it activ= e. After the restore you get whatever trigger was in the
  source dump.
- There's also a ris= k: a buggy login trigger that raises an error locks out
  everyone it doesn't skip. The escape hatch is event_trigger= s =3D off, which
  needs a real superuser. O= n Azure Flexible Server you don't have a real
&nb= sp; superuser.

WHAT WO= RKS: BLOCK, DON'T WARN

Every client surfaces a failed connection or a terminated session. Not eve= ry
client surfaces a warning. A common pattern fo= r 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
  f= rom reality.

2. Restor= e 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;
  &nbs= p; SELECT pg_terminate_backend(pid) FROM pg_stat_activity
      WHERE datname =3D 'app' AND pid <> pg_= backend_pid();
    ALTER DATABASE app&= nbsp;   RENAME TO app_old;
    A= LTER DATABASE app_new RENAME TO app;
   = ; -- re-apply grants/owners if they differ from prod, then drop app_old la= ter

  Downtime i= s a few seconds. Clients get a clear error ("terminating
  connection due to administrator command", then 'database "= app" does not
  exist' or "not currently ac= cepting connections"), and every driver and GUI
&= nbsp; shows that.

3. = If you refresh in place, close the door explicitly:
    - ALTER DATABASE app ALLOW_CONNECTIONS false; blocks ever= yone, admins
      included. Or u= se 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+).
<= div dir=3D"ltr">    - Reverse all of this as the last step of th= e pipeline.

4. Optiona= lly 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 y= our 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 Az= ure Database
for PostgreSQL Flexible Server you c= an't edit pg_hba.conf or start in
single-user/res= tricted mode. Network rules plus the steps above are your levers
<= div dir=3D"ltr">there. On a self-managed VM you can also swap in a pg_hba.c= onf that rejects
application users and reload.

In short: tell people th= rough 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,
Man= u
------=_Part_2410779_1735337722.1792756294463--