v18-0001-Add-a-new-login-event-and-login-trigger-support.patch

From 19a4f4c41d439fdcd8a0a65f953882373e7f7af0 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Wed, 15 Sep 2021 22:38:23 +1000
Subject: [PATCH v18] Add a new "login" event and login trigger support.

The login event occurs when a user logs in to the system.

Author: Konstantin Knizhnik
Discussion: https://www.postgresql.org/message-id/flat/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru
---
 doc/src/sgml/bki.sgml                       |   2 +-
 doc/src/sgml/catalogs.sgml                  |  11 ++
 doc/src/sgml/ecpg.sgml                      |   2 +
 doc/src/sgml/event-trigger.sgml             |  70 ++++++++-
 src/backend/commands/dbcommands.c           |   3 +-
 src/backend/commands/event_trigger.c        | 159 +++++++++++++++++---
 src/backend/tcop/postgres.c                 |   3 +
 src/backend/utils/cache/evtcache.c          |   2 +
 src/bin/pg_dump/pg_dump.c                   |  58 ++++++-
 src/bin/psql/tab-complete.c                 |   3 +-
 src/include/catalog/pg_database.dat         |   2 +-
 src/include/catalog/pg_database.h           |   3 +
 src/include/commands/event_trigger.h        |   1 +
 src/include/tcop/cmdtaglist.h               |   1 +
 src/include/utils/evtcache.h                |   3 +-
 src/test/recovery/t/001_stream_rep.pl       |  23 +++
 src/test/regress/expected/event_trigger.out |  31 ++++
 src/test/regress/sql/event_trigger.sql      |  20 +++
 18 files changed, 367 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index f900a01b82..c312165f71 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -182,7 +182,7 @@
 { oid =&gt; '1', oid_symbol =&gt; 'TemplateDbOid',
   descr =&gt; 'database\'s default template',
   datname =&gt; 'template1', encoding =&gt; 'ENCODING', datcollate =&gt; 'LC_COLLATE',
-  datctype =&gt; 'LC_CTYPE', datistemplate =&gt; 't', datallowconn =&gt; 't',
+  datctype =&gt; 'LC_CTYPE', datistemplate =&gt; 't', datallowconn =&gt; 't', dathaslogintriggers =&gt; 'f',
   datconnlimit =&gt; '-1', datlastsysoid =&gt; '0', datfrozenxid =&gt; '0',
   datminmxid =&gt; '1', dattablespace =&gt; 'pg_default', datacl =&gt; '_null_' },
 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2f0def9b19..c35a70ce50 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2972,6 +2972,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>dathaslogintriggers</structfield> <type>bool</type>
+      </para>
+      <para>
+        Indicates that there are login triggers defined for this database.
+        This flag is used to avoid extra lookups on the pg_event_trigger table during each backend startup.
+        This flag is used internally by Postgres and should not be manually changed by DBA or application.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>datconnlimit</structfield> <type>int4</type>
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index 04d4a6bdb2..565f88d298 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -4731,6 +4731,7 @@ datdba = 10 (type: 1)
 encoding = 0 (type: 5)
 datistemplate = t (type: 1)
 datallowconn = t (type: 1)
+dathaslogintriggers = f (type: 1)
 datconnlimit = -1 (type: 5)
 datlastsysoid = 11510 (type: 1)
 datfrozenxid = 379 (type: 1)
@@ -4756,6 +4757,7 @@ datdba = 10 (type: 1)
 encoding = 0 (type: 5)
 datistemplate = f (type: 1)
 datallowconn = t (type: 1)
+dathaslogintriggers = f (type: 1)
 datconnlimit = -1 (type: 5)
 datlastsysoid = 11510 (type: 1)
 datfrozenxid = 379 (type: 1)
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 60366a950e..ec35ddfad8 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -28,6 +28,7 @@
      An event trigger fires whenever the event with which it is associated
      occurs in the database in which it is defined. Currently, the only
      supported events are
+     <literal>login</literal>,
      <literal>ddl_command_start</literal>,
      <literal>ddl_command_end</literal>,
      <literal>table_rewrite</literal>
@@ -35,6 +36,16 @@
      Support for additional events may be added in future releases.
    </para>
 
+   <para>
+     The <literal>login</literal> event occurs when a user logs in to the
+     system.
+     Any bugs in a trigger procedure for this event may prevent successful
+     login to the system. Such bugs may be fixed after first restarting the
+     system in single-user mode (as event triggers are disabled in this mode).
+     See the <xref linkend="app-postgres"/> reference page for details about
+     using single-user mode.
+   </para>
+
    <para>
      The <literal>ddl_command_start</literal> event occurs just before the
      execution of a <literal>CREATE</literal>, <literal>ALTER</literal>, <literal>DROP</literal>,
@@ -1140,7 +1151,7 @@ typedef struct EventTriggerData
   </sect1>
 
   <sect1 id="event-trigger-example">
-   <title>A Complete Event Trigger Example</title>
+   <title>A C language Event Trigger Example</title>
 
    <para>
     Here is a very simple example of an event trigger function written in C.
@@ -1280,6 +1291,63 @@ $$;
 CREATE EVENT TRIGGER no_rewrite_allowed
                   ON table_rewrite
    EXECUTE FUNCTION no_rewrite();
+</programlisting>
+   </para>
+ </sect1>
+
+  <sect1 id="event-trigger-database-login-example">
+   <title>A Database Login Event Trigger Example</title>
+
+   <para>
+    The event trigger on the <literal>login</literal> event can be
+    useful for logging user logins, for verifying the connection and
+    assigning roles according to current circumstances, or for some session data
+    initialization.
+   </para>
+
+   <para>
+    The following example demonstrates these options.
+<programlisting>
+-- create test tables and roles
+CREATE TABLE user_login_log (
+  "user" text,
+  "session_start" timestamp with time zone
+);
+CREATE ROLE day_worker;
+CREATE ROLE night_worker;
+
+-- the example trigger function
+CREATE OR REPLACE FUNCTION init_session()
+  RETURNS event_trigger SECURITY DEFINER
+  LANGUAGE plpgsql AS
+$$
+DECLARE
+  hour integer = EXTRACT('hour' FROM current_time);
+BEGIN
+-- 1) Assign some roles
+IF hour BETWEEN 8 AND 20 THEN         -- at daytime grant the day_worker role
+  EXECUTE 'REVOKE night_worker FROM ' || quote_ident(session_user);
+  EXECUTE 'GRANT    day_worker TO '   || quote_ident(session_user);
+ELSIF hour BETWEEN 2 AND 4 THEN
+  RAISE EXCEPTION 'Login forbidden';  -- do not allow to login these hours
+ELSE                                  -- at other time grant the night_worker role
+  EXECUTE 'REVOKE day_worker FROM ' || quote_ident(session_user);
+  EXECUTE 'GRANT  night_worker TO ' || quote_ident(session_user);
+END IF;
+
+-- 2) Initialize some user session data
+CREATE TEMP TABLE session_storage (x float, y integer);
+
+-- 3) Log the connection time
+INSERT INTO user_login_log VALUES (session_user, current_timestamp);
+
+END;
+$$;
+
+-- trigger definition
+CREATE EVENT TRIGGER init_session
+  ON login
+  EXECUTE FUNCTION init_session();
 </programlisting>
    </para>
  </sect1>
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 029fab48df..a658d476ca 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -530,6 +530,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	new_record[Anum_pg_database_datctype - 1] =
 		DirectFunctionCall1(namein, CStringGetDatum(dbctype));
 	new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(dbistemplate);
+	new_record[Anum_pg_database_dathaslogintriggers - 1] = BoolGetDatum(false);
 	new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(dballowconnections);
 	new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
 	new_record[Anum_pg_database_datlastsysoid - 1] = ObjectIdGetDatum(src_lastsysoid);
@@ -1585,7 +1586,7 @@ AlterDatabase(ParseState *pstate, AlterDatabaseStmt *stmt, bool isTopLevel)
 		new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
 		new_record_repl[Anum_pg_database_datconnlimit - 1] = true;
 	}
-
+	new_record[Anum_pg_database_dathaslogintriggers - 1] = BoolGetDatum(datform->dathaslogintriggers);
 	newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel), new_record,
 								 new_record_nulls, new_record_repl);
 	CatalogTupleUpdate(rel, &tuple->t_self, newtuple);
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 71612d577e..539ba2e8ad 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -20,6 +20,7 @@
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
 #include "catalog/objectaccess.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_event_trigger.h"
 #include "catalog/pg_namespace.h"
 #include "catalog/pg_opclass.h"
@@ -43,9 +44,11 @@
 #include "utils/builtins.h"
 #include "utils/evtcache.h"
 #include "utils/fmgroids.h"
+#include "utils/inval.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
+#include "utils/snapmgr.h"
 #include "utils/syscache.h"
 
 typedef struct EventTriggerQueryState
@@ -130,6 +133,7 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
 	if (strcmp(stmt->eventname, "ddl_command_start") != 0 &&
 		strcmp(stmt->eventname, "ddl_command_end") != 0 &&
 		strcmp(stmt->eventname, "sql_drop") != 0 &&
+		strcmp(stmt->eventname, "login") != 0 &&
 		strcmp(stmt->eventname, "table_rewrite") != 0)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
@@ -293,6 +297,26 @@ insert_event_trigger_tuple(const char *trigname, const char *eventname, Oid evtO
 	CatalogTupleInsert(tgrel, tuple);
 	heap_freetuple(tuple);
 
+	if (strcmp(eventname, "login") == 0)
+	{
+		Form_pg_database db;
+		Relation pg_db = table_open(DatabaseRelationId, RowExclusiveLock);
+		/* Set dathaslogintriggers flag in pg_database */
+		tuple = SearchSysCacheCopy1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+		if (!HeapTupleIsValid(tuple))
+			elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
+		db = (Form_pg_database) GETSTRUCT(tuple);
+		if (!db->dathaslogintriggers)
+		{
+			db->dathaslogintriggers = true;
+			CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+		}
+		else
+			CacheInvalidateRelcacheByTuple(tuple);
+		table_close(pg_db, RowExclusiveLock);
+		heap_freetuple(tuple);
+	}
+
 	/* Depend on owner. */
 	recordDependencyOnOwner(EventTriggerRelationId, trigoid, evtOwner);
 
@@ -562,6 +586,9 @@ EventTriggerCommonSetup(Node *parsetree,
 	ListCell   *lc;
 	List	   *runlist = NIL;
 
+	/* Get the command tag. */
+	tag = (event == EVT_Login) ? CMDTAG_LOGIN : CreateCommandTag(parsetree);
+
 	/*
 	 * We want the list of command tags for which this procedure is actually
 	 * invoked to match up exactly with the list that CREATE EVENT TRIGGER
@@ -577,22 +604,18 @@ EventTriggerCommonSetup(Node *parsetree,
 	 * relevant command tag.
 	 */
 #ifdef USE_ASSERT_CHECKING
+	if (event == EVT_DDLCommandStart ||
+		event == EVT_DDLCommandEnd ||
+		event == EVT_SQLDrop ||
+		event == EVT_Login)
 	{
-		CommandTag	dbgtag;
-
-		dbgtag = CreateCommandTag(parsetree);
-		if (event == EVT_DDLCommandStart ||
-			event == EVT_DDLCommandEnd ||
-			event == EVT_SQLDrop)
-		{
-			if (!command_tag_event_trigger_ok(dbgtag))
-				elog(ERROR, "unexpected command tag \"%s\"", GetCommandTagName(dbgtag));
-		}
-		else if (event == EVT_TableRewrite)
-		{
-			if (!command_tag_table_rewrite_ok(dbgtag))
-				elog(ERROR, "unexpected command tag \"%s\"", GetCommandTagName(dbgtag));
-		}
+		if (!command_tag_event_trigger_ok(tag))
+			elog(ERROR, "unexpected command tag \"%s\"", GetCommandTagName(tag));
+	}
+	else if (event == EVT_TableRewrite)
+	{
+		if (!command_tag_table_rewrite_ok(tag))
+			elog(ERROR, "unexpected command tag \"%s\"", GetCommandTagName(tag));
 	}
 #endif
 
@@ -601,9 +624,6 @@ EventTriggerCommonSetup(Node *parsetree,
 	if (cachelist == NIL)
 		return NIL;
 
-	/* Get the command tag. */
-	tag = CreateCommandTag(parsetree);
-
 	/*
 	 * Filter list of event triggers by command tag, and copy them into our
 	 * memory context.  Once we start running the command triggers, or indeed
@@ -800,6 +820,109 @@ EventTriggerSQLDrop(Node *parsetree)
 	list_free(runlist);
 }
 
+/*
+ * Return true if this database has login triggers, false otherwise.
+ */
+static bool
+DatabaseHasLoginTriggers(void)
+{
+	bool has_login_triggers;
+	HeapTuple tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+
+	if (!HeapTupleIsValid(tuple))
+		elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
+
+	has_login_triggers = ((Form_pg_database) GETSTRUCT(tuple))->dathaslogintriggers;
+	ReleaseSysCache(tuple);
+	return has_login_triggers;
+}
+
+/*
+ * Fire login triggers.
+ */
+void
+EventTriggerOnLogin(void)
+{
+	List	   *runlist;
+	EventTriggerData trigdata;
+
+	/*
+	 * See EventTriggerDDLCommandStart for a discussion about why event
+	 * triggers are disabled in single user mode.
+	 */
+	if (!IsUnderPostmaster || !OidIsValid(MyDatabaseId))
+		return;
+
+	StartTransactionCommand();
+
+	if (DatabaseHasLoginTriggers())
+	{
+		runlist = EventTriggerCommonSetup(NULL,
+										  EVT_Login, "login",
+										  &trigdata);
+
+		if (runlist != NIL)
+		{
+			/*
+			 * Make sure anything the main command did will be visible to the event
+			 * triggers.
+			 */
+			CommandCounterIncrement();
+
+			/*
+			 * Event trigger execution may require an active snapshot.
+			 */
+			PushActiveSnapshot(GetTransactionSnapshot());
+
+			/* Run the triggers. */
+			EventTriggerInvoke(runlist, &trigdata);
+
+			/* Cleanup. */
+			list_free(runlist);
+
+			PopActiveSnapshot();
+		}
+		else
+		{
+			/*
+			 * Runlist is empty: clear dathaslogintriggers flag
+			 */
+			Relation pg_db = table_open(DatabaseRelationId, RowExclusiveLock);
+			HeapTuple tuple = SearchSysCacheCopy1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+			Form_pg_database db;
+
+			if (!HeapTupleIsValid(tuple))
+				elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
+
+			db = (Form_pg_database) GETSTRUCT(tuple);
+			if (db->dathaslogintriggers)
+			{
+				/*
+				 * There can be a race condition: a login event trigger may have
+				 * been added after the pg_event_trigger table was scanned, and
+				 * we don't want to erroneously clear the dathaslogintriggers
+				 * flag in this case. To be sure that this hasn't happened,
+				 * repeat the scan under the pg_database table lock.
+				 */
+				AcceptInvalidationMessages();
+				runlist = EventTriggerCommonSetup(NULL,
+												  EVT_Login, "login",
+												  &trigdata);
+				if (runlist == NULL) /* list is still empty, so clear the flag */
+				{
+					db->dathaslogintriggers = false;
+					CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+				}
+				else
+					CacheInvalidateRelcacheByTuple(tuple);
+			}
+			table_close(pg_db, RowExclusiveLock);
+			heap_freetuple(tuple);
+		}
+	}
+	CommitTransactionCommand();
+}
+
 
 /*
  * Fire table_rewrite triggers.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 3f9ed549f9..657e200541 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -42,6 +42,7 @@
 #include "catalog/pg_type.h"
 #include "commands/async.h"
 #include "commands/prepare.h"
+#include "commands/event_trigger.h"
 #include "executor/spi.h"
 #include "jit/jit.h"
 #include "libpq/libpq.h"
@@ -4167,6 +4168,8 @@ PostgresMain(int argc, char *argv[],
 	if (!IsUnderPostmaster)
 		PgStartTime = GetCurrentTimestamp();
 
+	EventTriggerOnLogin();
+
 	/*
 	 * POSTGRES main processing loop begins here
 	 *
diff --git a/src/backend/utils/cache/evtcache.c b/src/backend/utils/cache/evtcache.c
index 460b720a65..b9b935f1cc 100644
--- a/src/backend/utils/cache/evtcache.c
+++ b/src/backend/utils/cache/evtcache.c
@@ -167,6 +167,8 @@ BuildEventTriggerCache(void)
 			event = EVT_SQLDrop;
 		else if (strcmp(evtevent, "table_rewrite") == 0)
 			event = EVT_TableRewrite;
+		else if (strcmp(evtevent, "login") == 0)
+			event = EVT_Login;
 		else
 			continue;
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a485fb2d07..4e83dec4b1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2797,6 +2797,7 @@ dumpDatabase(Archive *fout)
 				i_datacl,
 				i_rdatacl,
 				i_datistemplate,
+				i_dathaslogintriggers,
 				i_datconnlimit,
 				i_tablespace;
 	CatalogId	dbCatId;
@@ -2809,6 +2810,7 @@ dumpDatabase(Archive *fout)
 			   *datacl,
 			   *rdatacl,
 			   *datistemplate,
+			   *dathaslogintriggers,
 			   *datconnlimit,
 			   *tablespace;
 	uint32		frozenxid,
@@ -2827,7 +2829,7 @@ dumpDatabase(Archive *fout)
 	 * (pg_init_privs) are not supported on databases, so this logic cannot
 	 * make use of buildACLQueries().
 	 */
-	if (fout->remoteVersion >= 90600)
+	if (fout->remoteVersion >= 150000)
 	{
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, "
 						  "(%s datdba) AS dba, "
@@ -2853,7 +2855,41 @@ dumpDatabase(Archive *fout)
 						  "       AS permp(orig_acl) "
 						  "     WHERE acl = orig_acl)) AS rdatacls) "
 						  " AS rdatacl, "
-						  "datistemplate, datconnlimit, "
+						  "datistemplate, datconnlimit, dathaslogintriggers, "
+						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
+						  "shobj_description(oid, 'pg_database') AS description "
+
+						  "FROM pg_database "
+						  "WHERE datname = current_database()",
+						  username_subquery);
+	}
+	else if (fout->remoteVersion >= 90600)
+	{
+		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, "
+						  "(%s datdba) AS dba, "
+						  "pg_encoding_to_char(encoding) AS encoding, "
+						  "datcollate, datctype, datfrozenxid, datminmxid, "
+						  "(SELECT array_agg(acl ORDER BY row_n) FROM "
+						  "  (SELECT acl, row_n FROM "
+						  "     unnest(coalesce(datacl,acldefault('d',datdba))) "
+						  "     WITH ORDINALITY AS perm(acl,row_n) "
+						  "   WHERE NOT EXISTS ( "
+						  "     SELECT 1 "
+						  "     FROM unnest(acldefault('d',datdba)) "
+						  "       AS init(init_acl) "
+						  "     WHERE acl = init_acl)) AS datacls) "
+						  " AS datacl, "
+						  "(SELECT array_agg(acl ORDER BY row_n) FROM "
+						  "  (SELECT acl, row_n FROM "
+						  "     unnest(acldefault('d',datdba)) "
+						  "     WITH ORDINALITY AS initp(acl,row_n) "
+						  "   WHERE NOT EXISTS ( "
+						  "     SELECT 1 "
+						  "     FROM unnest(coalesce(datacl,acldefault('d',datdba))) "
+						  "       AS permp(orig_acl) "
+						  "     WHERE acl = orig_acl)) AS rdatacls) "
+						  " AS rdatacl, "
+						  "datistemplate, datconnlimit, false as dathaslogintriggers, "
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2867,7 +2903,7 @@ dumpDatabase(Archive *fout)
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
 						  "datcollate, datctype, datfrozenxid, datminmxid, "
-						  "datacl, '' as rdatacl, datistemplate, datconnlimit, "
+						  "datacl, '' as rdatacl, datistemplate, datconnlimit, false as dathaslogintriggers"
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2881,7 +2917,7 @@ dumpDatabase(Archive *fout)
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
 						  "datcollate, datctype, datfrozenxid, 0 AS datminmxid, "
-						  "datacl, '' as rdatacl, datistemplate, datconnlimit, "
+						  "datacl, '' as rdatacl, datistemplate, datconnlimit, false as dathaslogintriggers, "
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2895,7 +2931,7 @@ dumpDatabase(Archive *fout)
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
 						  "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, "
-						  "datacl, '' as rdatacl, datistemplate, datconnlimit, "
+						  "datacl, '' as rdatacl, datistemplate, datconnlimit, false as dathaslogintriggers, "
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2910,7 +2946,7 @@ dumpDatabase(Archive *fout)
 						  "pg_encoding_to_char(encoding) AS encoding, "
 						  "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, "
 						  "datacl, '' as rdatacl, datistemplate, "
-						  "-1 as datconnlimit, "
+						  "-1 as datconnlimit, false as dathaslogintriggers, "
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace "
 						  "FROM pg_database "
 						  "WHERE datname = current_database()",
@@ -2932,6 +2968,7 @@ dumpDatabase(Archive *fout)
 	i_rdatacl = PQfnumber(res, "rdatacl");
 	i_datistemplate = PQfnumber(res, "datistemplate");
 	i_datconnlimit = PQfnumber(res, "datconnlimit");
+	i_dathaslogintriggers = PQfnumber(res, "dathaslogintriggers");
 	i_tablespace = PQfnumber(res, "tablespace");
 
 	dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
@@ -2946,6 +2983,7 @@ dumpDatabase(Archive *fout)
 	datacl = PQgetvalue(res, 0, i_datacl);
 	rdatacl = PQgetvalue(res, 0, i_rdatacl);
 	datistemplate = PQgetvalue(res, 0, i_datistemplate);
+	dathaslogintriggers = PQgetvalue(res, 0, i_dathaslogintriggers);
 	datconnlimit = PQgetvalue(res, 0, i_datconnlimit);
 	tablespace = PQgetvalue(res, 0, i_tablespace);
 
@@ -3119,6 +3157,14 @@ dumpDatabase(Archive *fout)
 		appendPQExpBufferStr(delQry, ";\n");
 	}
 
+	if (strcmp(dathaslogintriggers, "t") == 0)
+	{
+		appendPQExpBufferStr(creaQry, "UPDATE pg_catalog.pg_database "
+							 "SET dathaslogintriggers = true WHERE datname = ");
+		appendStringLiteralAH(creaQry, datname, fout);
+		appendPQExpBufferStr(creaQry, ";\n");
+	}
+
 	/* Add database-specific SET options */
 	dumpDatabaseConfig(fout, creaQry, datname, dbCatId.oid);
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5cd5838668..70c64b6700 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3073,7 +3073,8 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH("ON");
 	/* Complete CREATE EVENT TRIGGER <name> ON with event_type */
 	else if (Matches("CREATE", "EVENT", "TRIGGER", MatchAny, "ON"))
-		COMPLETE_WITH("ddl_command_start", "ddl_command_end", "sql_drop");
+		COMPLETE_WITH("ddl_command_start", "ddl_command_end",
+					  "login", "sql_drop");
 
 	/*
 	 * Complete CREATE EVENT TRIGGER <name> ON <event_type>.  EXECUTE FUNCTION
diff --git a/src/include/catalog/pg_database.dat b/src/include/catalog/pg_database.dat
index b8aa1364a0..2b804a47c3 100644
--- a/src/include/catalog/pg_database.dat
+++ b/src/include/catalog/pg_database.dat
@@ -15,7 +15,7 @@
 { oid => '1', oid_symbol => 'TemplateDbOid',
   descr => 'default template for new databases',
   datname => 'template1', encoding => 'ENCODING', datcollate => 'LC_COLLATE',
-  datctype => 'LC_CTYPE', datistemplate => 't', datallowconn => 't',
+  datctype => 'LC_CTYPE', datistemplate => 't', dathaslogintriggers => 'f', datallowconn => 't',
   datconnlimit => '-1', datlastsysoid => '0', datfrozenxid => '0',
   datminmxid => '1', dattablespace => 'pg_default', datacl => '_null_' },
 
diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h
index 43f3beb6a3..a6e0309bb3 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -52,6 +52,9 @@ CATALOG(pg_database,1262,DatabaseRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID
 	/* new connections allowed? */
 	bool		datallowconn;
 
+	/* database has login triggers? */
+	bool		dathaslogintriggers;
+
 	/* max connections allowed (-1=no limit) */
 	int32		datconnlimit;
 
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index e765e67fd1..a9f9954597 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -54,6 +54,7 @@ extern void EventTriggerDDLCommandStart(Node *parsetree);
 extern void EventTriggerDDLCommandEnd(Node *parsetree);
 extern void EventTriggerSQLDrop(Node *parsetree);
 extern void EventTriggerTableRewrite(Node *parsetree, Oid tableOid, int reason);
+extern void EventTriggerOnLogin(void);
 
 extern bool EventTriggerBeginCompleteQuery(void);
 extern void EventTriggerEndCompleteQuery(void);
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 9ba24d4ca9..b0e7df3d81 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -186,6 +186,7 @@ PG_CMDTAG(CMDTAG_INSERT, "INSERT", false, false, true)
 PG_CMDTAG(CMDTAG_LISTEN, "LISTEN", false, false, false)
 PG_CMDTAG(CMDTAG_LOAD, "LOAD", false, false, false)
 PG_CMDTAG(CMDTAG_LOCK_TABLE, "LOCK TABLE", false, false, false)
+PG_CMDTAG(CMDTAG_LOGIN, "LOGIN", true, false, false)
 PG_CMDTAG(CMDTAG_MOVE, "MOVE", false, false, true)
 PG_CMDTAG(CMDTAG_NOTIFY, "NOTIFY", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
diff --git a/src/include/utils/evtcache.h b/src/include/utils/evtcache.h
index 58ddb71cb1..29d73afe3d 100644
--- a/src/include/utils/evtcache.h
+++ b/src/include/utils/evtcache.h
@@ -22,7 +22,8 @@ typedef enum
 	EVT_DDLCommandStart,
 	EVT_DDLCommandEnd,
 	EVT_SQLDrop,
-	EVT_TableRewrite
+	EVT_TableRewrite,
+	EVT_Login,
 } EventTriggerEvent;
 
 typedef struct
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index ac581c1c07..bdf576651f 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -46,6 +46,26 @@ $node_standby_2->start;
 $node_primary->safe_psql('postgres',
 	"CREATE TABLE tab_int AS SELECT generate_series(1,1002) AS a");
 
+$node_primary->safe_psql('postgres', q{
+CREATE ROLE regress_user LOGIN PASSWORD 'pass';
+
+CREATE TABLE user_logins(id serial, who text);
+
+CREATE FUNCTION on_login_proc() RETURNS EVENT_TRIGGER AS $$
+BEGIN
+  IF NOT pg_is_in_recovery() THEN
+    INSERT INTO user_logins (who) VALUES (session_user);
+  END IF;
+  IF session_user = 'regress_hacker' THEN
+    RAISE EXCEPTION 'You are not welcome!';
+  END IF;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+CREATE EVENT TRIGGER on_login_trigger ON login EXECUTE FUNCTION on_login_proc();
+ALTER EVENT TRIGGER on_login_trigger ENABLE ALWAYS;
+});
+
 # Wait for standbys to catch up
 $node_primary->wait_for_catchup($node_standby_1, 'replay',
 	$node_primary->lsn('insert'));
@@ -339,6 +359,9 @@ sub replay_check
 
 replay_check();
 
+$node_standby_1->safe_psql('postgres', "SELECT 1", extra_params => [ '-U', 'regress_user', '-w' ]);
+$node_standby_2->safe_psql('postgres', "SELECT 2", extra_params => [ '-U', 'regress_user', '-w' ]);
+
 note "enabling hot_standby_feedback";
 
 # Enable hs_feedback. The slot should gain an xmin. We set the status interval
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 44d545de25..7794744d6b 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -604,3 +604,34 @@ SELECT
 DROP EVENT TRIGGER start_rls_command;
 DROP EVENT TRIGGER end_rls_command;
 DROP EVENT TRIGGER sql_drop_command;
+-- On login triggers
+create table user_logins(id serial, who text);
+create function on_login_proc() returns event_trigger as $$
+begin
+  insert into user_logins (who) values ('I am');
+  raise notice 'You are welcome!';
+end;
+$$ language plpgsql;
+create event trigger on_login_trigger on login execute procedure on_login_proc();
+alter event trigger on_login_trigger enable always;
+\c
+NOTICE:  You are welcome!
+select * from user_logins;
+ id | who  
+----+------
+  1 | I am
+(1 row)
+
+\c
+NOTICE:  You are welcome!
+select * from user_logins;
+ id | who  
+----+------
+  1 | I am
+  2 | I am
+(2 rows)
+
+-- Cleanup
+drop table user_logins;
+drop event trigger on_login_trigger;
+drop function on_login_proc();
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 1446cf8cc8..d2ebb4b2c9 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -462,3 +462,23 @@ SELECT
 DROP EVENT TRIGGER start_rls_command;
 DROP EVENT TRIGGER end_rls_command;
 DROP EVENT TRIGGER sql_drop_command;
+
+-- On login triggers
+create table user_logins(id serial, who text);
+create function on_login_proc() returns event_trigger as $$
+begin
+  insert into user_logins (who) values ('I am');
+  raise notice 'You are welcome!';
+end;
+$$ language plpgsql;
+create event trigger on_login_trigger on login execute procedure on_login_proc();
+alter event trigger on_login_trigger enable always;
+\c
+select * from user_logins;
+\c
+select * from user_logins;
+
+-- Cleanup
+drop table user_logins;
+drop event trigger on_login_trigger;
+drop function on_login_proc();
-- 
2.27.0