Hi!
Thank you for the review.
On Tue, Oct 10, 2023 at 7:37 PM Andres Freund <and...@anarazel.de> wrote:
> On 2023-10-10 08:18:46 +0300, Alexander Korotkov wrote:
> > @@ -968,7 +969,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
> >
> > if (!get_db_info(dbtemplate, ShareLock,
> > &src_dboid, &src_owner,
> > &src_encoding,
> > - &src_istemplate, &src_allowconn,
> > + &src_istemplate, &src_allowconn,
> > &src_hasloginevt,
> > &src_frozenxid, &src_minmxid,
> > &src_deftablespace,
> > &src_collate, &src_ctype,
> > &src_iculocale, &src_icurules, &src_locprovider,
> > &src_collversion))
>
> This isn't your fault, but this imo has become unreadable. Think we ought to
> move the information about a database to a struct.
Should I do this in a separate patch?
> > @@ -296,6 +306,13 @@ insert_event_trigger_tuple(const char *trigname, const
> > char *eventname, Oid evtO
> > CatalogTupleInsert(tgrel, tuple);
> > heap_freetuple(tuple);
> >
> > + /*
> > + * Login event triggers have an additional flag in pg_database to
> > allow
> > + * faster lookups in hot codepaths. Set the flag unless already True.
> > + */
> > + if (strcmp(eventname, "login") == 0)
> > + SetDatatabaseHasLoginEventTriggers();
>
> It's not really faster lookups, it's no lookups, right?
Yes, no extra lookups. Fixed.
> > /* Depend on owner. */
> > recordDependencyOnOwner(EventTriggerRelationId, trigoid, evtOwner);
> >
> > @@ -357,6 +374,39 @@ filter_list_to_array(List *filterlist)
> > return PointerGetDatum(construct_array_builtin(data, l, TEXTOID));
> > }
> >
> > +/*
> > + * Set pg_database.dathasloginevt flag for current database indicating that
> > + * current database has on login triggers.
> > + */
> > +void
> > +SetDatatabaseHasLoginEventTriggers(void)
> > +{
> > + /* Set dathasloginevt flag in pg_database */
> > + Form_pg_database db;
> > + Relation pg_db = table_open(DatabaseRelationId,
> > RowExclusiveLock);
> > + HeapTuple tuple;
> > +
> > + /*
> > + * Use shared lock to prevent a conflit with EventTriggerOnLogin()
> > trying
> > + * to reset pg_database.dathasloginevt flag. Note that we use
> > + * AccessShareLock allowing setters concurently.
> > + */
> > + LockSharedObject(DatabaseRelationId, MyDatabaseId, 0,
> > AccessShareLock);
>
> That seems like a very odd approach - how does this avoid concurrency issues
> with one backend setting and another unsetting the flag? And outside of that,
> won't this just lead to concurrently updated tuples?
When backend unsets the flag, it acquires the lock first. If lock is
acquired successfully then no other backends hold it. If the
concurrent backend have already inserted an event trigger then we will
detect it by rechecking event list under lock. If the concurrent
backend inserted/enabled event trigger and waiting for the lock, then
it will set the flag once we release the lock.
> > + 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->dathasloginevt)
> > + {
> > + db->dathasloginevt = true;
> > + CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
> > + CommandCounterIncrement();
> > + }
> > + table_close(pg_db, RowExclusiveLock);
> > + heap_freetuple(tuple);
> > +}
> > +
> > /*
> > * ALTER EVENT TRIGGER foo ENABLE|DISABLE|ENABLE ALWAYS|REPLICA
> > */
> > @@ -391,6 +441,10 @@ AlterEventTrigger(AlterEventTrigStmt *stmt)
> >
> > CatalogTupleUpdate(tgrel, &tup->t_self, tup);
> >
> > + if (namestrcmp(&evtForm->evtevent, "login") == 0 &&
> > + tgenabled != TRIGGER_DISABLED)
> > + SetDatatabaseHasLoginEventTriggers();
> > +
> > InvokeObjectPostAlterHook(EventTriggerRelationId,
> > trigoid, 0);
> >
> > @@ -557,7 +611,7 @@ filter_event_trigger(CommandTag tag,
> > EventTriggerCacheItem *item)
> > static List *
> > EventTriggerCommonSetup(Node *parsetree,
> > EventTriggerEvent event,
> > const char *eventstr,
> > - EventTriggerData *trigdata)
> > + EventTriggerData *trigdata,
> > bool unfiltered)
> > {
> > CommandTag tag;
> > List *cachelist;
> > @@ -582,10 +636,15 @@ EventTriggerCommonSetup(Node *parsetree,
> > {
> > CommandTag dbgtag;
> >
> > - dbgtag = CreateCommandTag(parsetree);
> > + if (event == EVT_Login)
> > + dbgtag = CMDTAG_LOGIN;
> > + else
> > + dbgtag = CreateCommandTag(parsetree);
> > +
> > if (event == EVT_DDLCommandStart ||
> > event == EVT_DDLCommandEnd ||
> > - event == EVT_SQLDrop)
> > + event == EVT_SQLDrop ||
> > + event == EVT_Login)
> > {
> > if (!command_tag_event_trigger_ok(dbgtag))
> > elog(ERROR, "unexpected command tag \"%s\"",
> > GetCommandTagName(dbgtag));
> > @@ -604,7 +663,10 @@ EventTriggerCommonSetup(Node *parsetree,
> > return NIL;
> >
> > /* Get the command tag. */
> > - tag = CreateCommandTag(parsetree);
> > + if (event == EVT_Login)
> > + tag = CMDTAG_LOGIN;
> > + else
> > + tag = CreateCommandTag(parsetree);
>
> Seems this bit should instead be in a function, given that you have it in
> multiple places.
Done.
> > +/*
> > + * Fire login event triggers if any are present. The dathasloginevt
> > + * pg_database flag is left when an event trigger is dropped, to avoid
> > + * complicating the codepath in the case of multiple event triggers. This
> > + * function will instead unset the flag if no trigger is defined.
> > + */
> > +void
> > +EventTriggerOnLogin(void)
> > +{
> > + List *runlist;
> > + EventTriggerData trigdata;
> > +
> > + /*
> > + * See EventTriggerDDLCommandStart for a discussion about why event
> > + * triggers are disabled in single user mode or via a GUC. We also
> > need a
> > + * database connection (some background workers doesn't have it).
> > + */
> > + if (!IsUnderPostmaster || !event_triggers ||
> > + !OidIsValid(MyDatabaseId) || !MyDatabaseHasLoginEventTriggers)
> > + return;
> > +
> > + StartTransactionCommand();
> > + runlist = EventTriggerCommonSetup(NULL,
> > +
> > EVT_Login, "login",
> > +
> > &trigdata, false);
> > +
> > + if (runlist != NIL)
> > + {
> > + /*
> > + * Event trigger execution may require an active snapshot.
> > + */
> > + PushActiveSnapshot(GetTransactionSnapshot());
> > +
> > + /* Run the triggers. */
> > + EventTriggerInvoke(runlist, &trigdata);
> > +
> > + /* Cleanup. */
> > + list_free(runlist);
> > +
> > + PopActiveSnapshot();
> > + }
> > + /*
> > + * There is no active login event trigger, but our
> > pg_database.dathasloginevt was set.
> > + * Try to unset this flag. We use the lock to prevent concurrent
> > + * SetDatatabaseHasLoginEventTriggers(), but we don't want to hang the
> > + * connection waiting on the lock. Thus, we are just trying to
> > acquire
> > + * the lock conditionally.
> > + */
> > + else if (ConditionalLockSharedObject(DatabaseRelationId, MyDatabaseId,
> > +
> > 0, AccessExclusiveLock))
>
> Eek. Why are we doing it this way? I think this is a seriously bad
> idea. Maybe it's obvious to you, but it seems much more reasonable to make the
> pg_database column an integer and count the number of login event
> triggers. When 0, then we don't need to look for login event triggers.
The approach with number of login event trigger obviously will also
work. But this version with lazy flag cleaning avoids need to handle
every unobvious case we delete the event triggers (cascading deletion
etc).
------
Regards,
Alexander Korotkov
From ee3d74a916c389f041c24bf6cd5c50230cf795fc Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Mon, 9 Oct 2023 15:00:26 +0300
Subject: [PATCH] Add support event triggers on authenticated login
This commit introduces trigger on login event, allowing to fire some actions
right on the user connection. This can be useful for logging or connection
check purposes as well as for some personalization of environment. Usage
details are described in the documentation included, but shortly usage is
the same as for other triggers: create function returning event_trigger and
then create event trigger on login event.
In order to prevent the connection time overhead when there are no triggers
the commit introduces pg_database.dathasloginevt flag, which indicates database
has active login triggers. This flag is set by CREATE/ALTER EVENT TRIGGER
command, and unset at connection time when no active triggers found.
Author: Konstantin Knizhnik, Mikhail Gribkov
Discussion: https://postgr.es/m/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru
Reviewed-by: Pavel Stehule, Takayuki Tsunakawa, Greg Nancarrow, Ivan Panchenko
Reviewed-by: Daniel Gustafsson, Teodor Sigaev, Robert Haas, Andres Freund
Reviewed-by: Tom Lane, Andrey Sokolov, Zhihong Yu, Sergey Shinderuk
Reviewed-by: Gregory Stark, Nikita Malakhov, Ted Yu
---
doc/src/sgml/bki.sgml | 2 +-
doc/src/sgml/catalogs.sgml | 13 ++
doc/src/sgml/ecpg.sgml | 2 +
doc/src/sgml/event-trigger.sgml | 94 ++++++++++
src/backend/commands/dbcommands.c | 17 +-
src/backend/commands/event_trigger.c | 177 +++++++++++++++++-
src/backend/storage/lmgr/lmgr.c | 38 ++++
src/backend/tcop/postgres.c | 4 +
src/backend/utils/cache/evtcache.c | 2 +
src/backend/utils/init/globals.c | 2 +
src/backend/utils/init/postinit.c | 1 +
src/bin/pg_dump/pg_dump.c | 5 +
src/bin/psql/tab-complete.c | 4 +-
src/include/catalog/pg_database.dat | 2 +-
src/include/catalog/pg_database.h | 3 +
src/include/commands/event_trigger.h | 1 +
src/include/miscadmin.h | 2 +
src/include/storage/lmgr.h | 2 +
src/include/tcop/cmdtaglist.h | 1 +
src/include/utils/evtcache.h | 3 +-
.../authentication/t/005_login_trigger.pl | 157 ++++++++++++++++
src/test/recovery/t/001_stream_rep.pl | 23 +++
src/test/regress/expected/event_trigger.out | 45 +++++
src/test/regress/sql/event_trigger.sql | 26 +++
24 files changed, 606 insertions(+), 20 deletions(-)
create mode 100644 src/test/authentication/t/005_login_trigger.pl
diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index f71644e3989..315ba819514 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -184,7 +184,7 @@
descr => 'database\'s default template',
datname => 'template1', encoding => 'ENCODING',
datlocprovider => 'LOCALE_PROVIDER', datistemplate => 't',
- datallowconn => 't', datconnlimit => '-1', datfrozenxid => '0',
+ datallowconn => 't', dathasloginevt => 'f', datconnlimit => '-1', datfrozenxid => '0',
datminmxid => '1', dattablespace => 'pg_default', datcollate => 'LC_COLLATE',
datctype => 'LC_CTYPE', daticulocale => 'ICU_LOCALE', datacl => '_null_' },
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e09adb45e41..d3458840fbe 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3035,6 +3035,19 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dathasloginevt</structfield> <type>bool</type>
+ </para>
+ <para>
+ Indicates that there are login event triggers defined for this database.
+ This flag is used to avoid extra lookups on the
+ <structname>pg_event_trigger</structname> table during each backend
+ startup. This flag is used internally by <productname>PostgreSQL</productname>
+ and should not be manually altered or read for monitoring purposes.
+ </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 f52165165dc..54de81158b5 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -4769,6 +4769,7 @@ datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = t (type: 1)
datallowconn = t (type: 1)
+dathasloginevt = f (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
@@ -4793,6 +4794,7 @@ datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = f (type: 1)
datallowconn = t (type: 1)
+dathasloginevt = f (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 3b6a5361b34..10b20f0339a 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,24 @@
Support for additional events may be added in future releases.
</para>
+ <para>
+ The <literal>login</literal> event occurs when an authenticated user logs
+ into the system. Any bug in a trigger procedure for this event may
+ prevent successful login to the system. Such bugs may be fixed by
+ setting <xref linkend="guc-event-triggers"/> is set to <literal>false</literal>
+ either in a connection string or configuration file. Alternative is
+ 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.
+ The <literal>login</literal> event will also fire on standby servers.
+ To prevent servers from becoming inaccessible, such triggers must avoid
+ writing anything to the database when running on a standby.
+ Also, it's recommended to avoid long-running queries in
+ <literal>login</literal> event triggers. Notes that, for instance,
+ cancelling connection in <application>psql</application> wouldn't cancel
+ the in-progress <literal>login</literal> trigger.
+ </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>,
@@ -1300,4 +1319,79 @@ CREATE EVENT TRIGGER no_rewrite_allowed
</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 session
+ data initialization. It is very important that any event trigger using
+ the <literal>login</literal> event checks whether or not the database is
+ in recovery before performing any writes. Writing to a standby server
+ will make it inaccessible.
+ </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 at time zone 'utc');
+ rec boolean;
+BEGIN
+-- 1. Forbid logging in between 2AM and 4AM.
+IF hour BETWEEN 2 AND 4 THEN
+ RAISE EXCEPTION 'Login forbidden';
+END IF;
+
+-- The checks below cannot be performed on standby servers so
+-- ensure the database is not in recovery before we perform any
+-- operations.
+SELECT pg_is_in_recovery() INTO rec;
+IF rec THEN
+ RETURN;
+END IF;
+
+-- 2. Assign some roles. At daytime, grant the day_worker role, else the
+-- night_worker role.
+IF hour BETWEEN 8 AND 20 THEN
+ EXECUTE 'REVOKE night_worker FROM ' || quote_ident(session_user);
+ EXECUTE 'GRANT day_worker TO ' || quote_ident(session_user);
+ELSE
+ EXECUTE 'REVOKE day_worker FROM ' || quote_ident(session_user);
+ EXECUTE 'GRANT night_worker TO ' || quote_ident(session_user);
+END IF;
+
+-- 3. Initialize user session data
+CREATE TEMP TABLE session_storage (x float, y integer);
+ALTER TABLE session_storage OWNER TO session_user;
+
+-- 4. Log the connection time
+INSERT INTO public.user_login_log VALUES (session_user, current_timestamp);
+
+END;
+$$;
+
+-- trigger definition
+CREATE EVENT TRIGGER init_session
+ ON login
+ EXECUTE FUNCTION init_session();
+ALTER EVENT TRIGGER init_session ENABLE ALWAYS;
+</programlisting>
+ </para>
+ </sect1>
</chapter>
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 307729ab7ef..b0c562aa1ea 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -116,7 +116,7 @@ static void movedb(const char *dbname, const char *tblspcname);
static void movedb_failure_callback(int code, Datum arg);
static bool get_db_info(const char *name, LOCKMODE lockmode,
Oid *dbIdP, Oid *ownerIdP,
- int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
+ int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, bool *dbHasLoginEvtP,
TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
Oid *dbTablespace, char **dbCollate, char **dbCtype, char **dbIculocale,
char **dbIcurules,
@@ -680,6 +680,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
char src_locprovider = '\0';
char *src_collversion = NULL;
bool src_istemplate;
+ bool src_hasloginevt;
bool src_allowconn;
TransactionId src_frozenxid = InvalidTransactionId;
MultiXactId src_minmxid = InvalidMultiXactId;
@@ -968,7 +969,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
if (!get_db_info(dbtemplate, ShareLock,
&src_dboid, &src_owner, &src_encoding,
- &src_istemplate, &src_allowconn,
+ &src_istemplate, &src_allowconn, &src_hasloginevt,
&src_frozenxid, &src_minmxid, &src_deftablespace,
&src_collate, &src_ctype, &src_iculocale, &src_icurules, &src_locprovider,
&src_collversion))
@@ -1375,6 +1376,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
new_record[Anum_pg_database_datlocprovider - 1] = CharGetDatum(dblocprovider);
new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(dbistemplate);
new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(dballowconnections);
+ new_record[Anum_pg_database_dathasloginevt - 1] = BoolGetDatum(src_hasloginevt);
new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
new_record[Anum_pg_database_datminmxid - 1] = TransactionIdGetDatum(src_minmxid);
@@ -1602,7 +1604,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
*/
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
- if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
+ if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
&db_istemplate, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
{
if (!missing_ok)
@@ -1817,7 +1819,7 @@ RenameDatabase(const char *oldname, const char *newname)
*/
rel = table_open(DatabaseRelationId, RowExclusiveLock);
- if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL,
+ if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
@@ -1927,7 +1929,7 @@ movedb(const char *dbname, const char *tblspcname)
*/
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
- if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
+ if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, &src_tblspcoid, NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
@@ -2693,7 +2695,7 @@ pg_database_collation_actual_version(PG_FUNCTION_ARGS)
static bool
get_db_info(const char *name, LOCKMODE lockmode,
Oid *dbIdP, Oid *ownerIdP,
- int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
+ int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, bool *dbHasLoginEvtP,
TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
Oid *dbTablespace, char **dbCollate, char **dbCtype, char **dbIculocale,
char **dbIcurules,
@@ -2778,6 +2780,9 @@ get_db_info(const char *name, LOCKMODE lockmode,
/* allowed as template? */
if (dbIsTemplateP)
*dbIsTemplateP = dbform->datistemplate;
+ /* Has on login event trigger? */
+ if (dbHasLoginEvtP)
+ *dbHasLoginEvtP = dbform->dathasloginevt;
/* allowing connections? */
if (dbAllowConnP)
*dbAllowConnP = dbform->datallowconn;
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index bd812e42d94..fda1d472fdd 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"
@@ -37,15 +38,18 @@
#include "miscadmin.h"
#include "parser/parse_func.h"
#include "pgstat.h"
+#include "storage/lmgr.h"
#include "tcop/deparse_utility.h"
#include "tcop/utility.h"
#include "utils/acl.h"
#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
@@ -103,6 +107,7 @@ static void validate_table_rewrite_tags(const char *filtervar, List *taglist);
static void EventTriggerInvoke(List *fn_oid_list, EventTriggerData *trigdata);
static const char *stringify_grant_objtype(ObjectType objtype);
static const char *stringify_adefprivs_objtype(ObjectType objtype);
+static void SetDatatabaseHasLoginEventTriggers(void);
/*
* Create an event trigger.
@@ -133,6 +138,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),
@@ -165,6 +171,10 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
else if (strcmp(stmt->eventname, "table_rewrite") == 0
&& tags != NULL)
validate_table_rewrite_tags("tag", tags);
+ else if (strcmp(stmt->eventname, "login") == 0 && tags != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Tag filtering is not supported for login event trigger")));
/*
* Give user a nice error message if an event trigger of the same name
@@ -296,6 +306,13 @@ insert_event_trigger_tuple(const char *trigname, const char *eventname, Oid evtO
CatalogTupleInsert(tgrel, tuple);
heap_freetuple(tuple);
+ /*
+ * Login event triggers have an additional flag in pg_database to avoid
+ * faster lookups in hot codepaths. Set the flag unless already True.
+ */
+ if (strcmp(eventname, "login") == 0)
+ SetDatatabaseHasLoginEventTriggers();
+
/* Depend on owner. */
recordDependencyOnOwner(EventTriggerRelationId, trigoid, evtOwner);
@@ -357,6 +374,39 @@ filter_list_to_array(List *filterlist)
return PointerGetDatum(construct_array_builtin(data, l, TEXTOID));
}
+/*
+ * Set pg_database.dathasloginevt flag for current database indicating that
+ * current database has on login triggers.
+ */
+void
+SetDatatabaseHasLoginEventTriggers(void)
+{
+ /* Set dathasloginevt flag in pg_database */
+ Form_pg_database db;
+ Relation pg_db = table_open(DatabaseRelationId, RowExclusiveLock);
+ HeapTuple tuple;
+
+ /*
+ * Use shared lock to prevent a conflit with EventTriggerOnLogin() trying
+ * to reset pg_database.dathasloginevt flag. Note that we use
+ * AccessShareLock allowing setters concurently.
+ */
+ LockSharedObject(DatabaseRelationId, MyDatabaseId, 0, AccessExclusiveLock);
+
+ 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->dathasloginevt)
+ {
+ db->dathasloginevt = true;
+ CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+ CommandCounterIncrement();
+ }
+ table_close(pg_db, RowExclusiveLock);
+ heap_freetuple(tuple);
+}
+
/*
* ALTER EVENT TRIGGER foo ENABLE|DISABLE|ENABLE ALWAYS|REPLICA
*/
@@ -391,6 +441,14 @@ AlterEventTrigger(AlterEventTrigStmt *stmt)
CatalogTupleUpdate(tgrel, &tup->t_self, tup);
+ /*
+ * Login event triggers have an additional flag in pg_database to avoid
+ * faster lookups in hot codepaths. Set the flag unless already True.
+ */
+ if (namestrcmp(&evtForm->evtevent, "login") == 0 &&
+ tgenabled != TRIGGER_DISABLED)
+ SetDatatabaseHasLoginEventTriggers();
+
InvokeObjectPostAlterHook(EventTriggerRelationId,
trigoid, 0);
@@ -549,6 +607,15 @@ filter_event_trigger(CommandTag tag, EventTriggerCacheItem *item)
return true;
}
+static CommandTag
+EventTriggerGetTag(Node *parsetree, EventTriggerEvent event)
+{
+ if (event == EVT_Login)
+ return CMDTAG_LOGIN;
+ else
+ return CreateCommandTag(parsetree);
+}
+
/*
* Setup for running triggers for the given event. Return value is an OID list
* of functions to run; if there are any, trigdata is filled with an
@@ -557,7 +624,7 @@ filter_event_trigger(CommandTag tag, EventTriggerCacheItem *item)
static List *
EventTriggerCommonSetup(Node *parsetree,
EventTriggerEvent event, const char *eventstr,
- EventTriggerData *trigdata)
+ EventTriggerData *trigdata, bool unfiltered)
{
CommandTag tag;
List *cachelist;
@@ -582,10 +649,12 @@ EventTriggerCommonSetup(Node *parsetree,
{
CommandTag dbgtag;
- dbgtag = CreateCommandTag(parsetree);
+ dbgtag = EventTriggerGetTag(parsetree, event);
+
if (event == EVT_DDLCommandStart ||
event == EVT_DDLCommandEnd ||
- event == EVT_SQLDrop)
+ event == EVT_SQLDrop ||
+ event == EVT_Login)
{
if (!command_tag_event_trigger_ok(dbgtag))
elog(ERROR, "unexpected command tag \"%s\"", GetCommandTagName(dbgtag));
@@ -604,7 +673,7 @@ EventTriggerCommonSetup(Node *parsetree,
return NIL;
/* Get the command tag. */
- tag = CreateCommandTag(parsetree);
+ tag = EventTriggerGetTag(parsetree, event);
/*
* Filter list of event triggers by command tag, and copy them into our
@@ -617,7 +686,7 @@ EventTriggerCommonSetup(Node *parsetree,
{
EventTriggerCacheItem *item = lfirst(lc);
- if (filter_event_trigger(tag, item))
+ if (unfiltered || filter_event_trigger(tag, item))
{
/* We must plan to fire this trigger. */
runlist = lappend_oid(runlist, item->fnoid);
@@ -670,7 +739,7 @@ EventTriggerDDLCommandStart(Node *parsetree)
runlist = EventTriggerCommonSetup(parsetree,
EVT_DDLCommandStart,
"ddl_command_start",
- &trigdata);
+ &trigdata, false);
if (runlist == NIL)
return;
@@ -718,7 +787,7 @@ EventTriggerDDLCommandEnd(Node *parsetree)
runlist = EventTriggerCommonSetup(parsetree,
EVT_DDLCommandEnd, "ddl_command_end",
- &trigdata);
+ &trigdata, false);
if (runlist == NIL)
return;
@@ -764,7 +833,7 @@ EventTriggerSQLDrop(Node *parsetree)
runlist = EventTriggerCommonSetup(parsetree,
EVT_SQLDrop, "sql_drop",
- &trigdata);
+ &trigdata, false);
/*
* Nothing to do if run list is empty. Note this typically can't happen,
@@ -805,6 +874,96 @@ EventTriggerSQLDrop(Node *parsetree)
list_free(runlist);
}
+/*
+ * Fire login event triggers if any are present. The dathasloginevt
+ * pg_database flag is left when an event trigger is dropped, to avoid
+ * complicating the codepath in the case of multiple event triggers. This
+ * function will instead unset the flag if no trigger is defined.
+ */
+void
+EventTriggerOnLogin(void)
+{
+ List *runlist;
+ EventTriggerData trigdata;
+
+ /*
+ * See EventTriggerDDLCommandStart for a discussion about why event
+ * triggers are disabled in single user mode or via a GUC. We also need a
+ * database connection (some background workers doesn't have it).
+ */
+ if (!IsUnderPostmaster || !event_triggers ||
+ !OidIsValid(MyDatabaseId) || !MyDatabaseHasLoginEventTriggers)
+ return;
+
+ StartTransactionCommand();
+ runlist = EventTriggerCommonSetup(NULL,
+ EVT_Login, "login",
+ &trigdata, false);
+
+ if (runlist != NIL)
+ {
+ /*
+ * Event trigger execution may require an active snapshot.
+ */
+ PushActiveSnapshot(GetTransactionSnapshot());
+
+ /* Run the triggers. */
+ EventTriggerInvoke(runlist, &trigdata);
+
+ /* Cleanup. */
+ list_free(runlist);
+
+ PopActiveSnapshot();
+ }
+ /*
+ * There is no active login event trigger, but our pg_database.dathasloginevt was set.
+ * Try to unset this flag. We use the lock to prevent concurrent
+ * SetDatatabaseHasLoginEventTriggers(), but we don't want to hang the
+ * connection waiting on the lock. Thus, we are just trying to acquire
+ * the lock conditionally.
+ */
+ else if (ConditionalLockSharedObject(DatabaseRelationId, MyDatabaseId,
+ 0, AccessExclusiveLock))
+ {
+ /*
+ * The lock is held. Now we need to recheck that login event triggers
+ * list is still empty. Once the list is empty, we know that even if
+ * there is a backend, which concurrently inserts/enables login trigger,
+ * it will update pg_database.dathasloginevt *afterwards*.
+ */
+ runlist = EventTriggerCommonSetup(NULL,
+ EVT_Login, "login",
+ &trigdata, true);
+
+ if (runlist == NIL)
+ {
+ Relation pg_db = table_open(DatabaseRelationId, RowExclusiveLock);
+ HeapTuple tuple;
+ Form_pg_database db;
+
+ 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->dathasloginevt)
+ {
+ db->dathasloginevt = false;
+ CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+ }
+ table_close(pg_db, RowExclusiveLock);
+ heap_freetuple(tuple);
+ }
+ else
+ {
+ list_free(runlist);
+ }
+ }
+ CommitTransactionCommand();
+}
+
/*
* Fire table_rewrite triggers.
@@ -835,7 +994,7 @@ EventTriggerTableRewrite(Node *parsetree, Oid tableOid, int reason)
runlist = EventTriggerCommonSetup(parsetree,
EVT_TableRewrite,
"table_rewrite",
- &trigdata);
+ &trigdata, false);
if (runlist == NIL)
return;
diff --git a/src/backend/storage/lmgr/lmgr.c b/src/backend/storage/lmgr/lmgr.c
index ee9b89a6726..b447ddf11ba 100644
--- a/src/backend/storage/lmgr/lmgr.c
+++ b/src/backend/storage/lmgr/lmgr.c
@@ -1060,6 +1060,44 @@ LockSharedObject(Oid classid, Oid objid, uint16 objsubid,
AcceptInvalidationMessages();
}
+/*
+ * ConditionalLockSharedObject
+ *
+ * As above, but only lock if we can get the lock without blocking.
+ * Returns true iff the lock was acquired.
+ */
+bool
+ConditionalLockSharedObject(Oid classid, Oid objid, uint16 objsubid,
+ LOCKMODE lockmode)
+{
+ LOCKTAG tag;
+ LOCALLOCK *locallock;
+ LockAcquireResult res;
+
+ SET_LOCKTAG_OBJECT(tag,
+ InvalidOid,
+ classid,
+ objid,
+ objsubid);
+
+ res = LockAcquireExtended(&tag, lockmode, false, true, true, &locallock);
+
+ if (res == LOCKACQUIRE_NOT_AVAIL)
+ return false;
+
+ /*
+ * Now that we have the lock, check for invalidation messages; see notes
+ * in LockRelationOid.
+ */
+ if (res != LOCKACQUIRE_ALREADY_CLEAR)
+ {
+ AcceptInvalidationMessages();
+ MarkLockClear(locallock);
+ }
+
+ return true;
+}
+
/*
* UnlockSharedObject
*/
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 21b9763183e..20d8202cb7d 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -36,6 +36,7 @@
#include "access/xact.h"
#include "catalog/pg_type.h"
#include "commands/async.h"
+#include "commands/event_trigger.h"
#include "commands/prepare.h"
#include "common/pg_prng.h"
#include "jit/jit.h"
@@ -4288,6 +4289,9 @@ PostgresMain(const char *dbname, const char *username)
initStringInfo(&row_description_buf);
MemoryContextSwitchTo(TopMemoryContext);
+ /* Fire any defined login event triggers, if appropriate */
+ EventTriggerOnLogin();
+
/*
* POSTGRES main processing loop begins here
*
diff --git a/src/backend/utils/cache/evtcache.c b/src/backend/utils/cache/evtcache.c
index b080f7a35f3..ab5111c90fd 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/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 011ec18015a..60bc1217fb4 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -90,6 +90,8 @@ Oid MyDatabaseId = InvalidOid;
Oid MyDatabaseTableSpace = InvalidOid;
+bool MyDatabaseHasLoginEventTriggers = false;
+
/*
* DatabasePath is the path (relative to DataDir) of my database's
* primary directory, ie, its directory in the default tablespace.
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index df4d15a50fb..e23fba41157 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1101,6 +1101,7 @@ InitPostgres(const char *in_dbname, Oid dboid,
}
MyDatabaseTableSpace = datform->dattablespace;
+ MyDatabaseHasLoginEventTriggers = datform->dathasloginevt;
/* pass the database name back to the caller */
if (out_dbname)
strcpy(out_dbname, dbname);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f7b61766921..83aeef2751b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3263,6 +3263,11 @@ dumpDatabase(Archive *fout)
appendPQExpBufferStr(delQry, ";\n");
}
+ /*
+ * We do not restore pg_database.dathasloginevt because it is set
+ * automatically on login event trigger creation.
+ */
+
/* 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 d30d719a1f8..2eb8ff1afca 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3552,8 +3552,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",
- "table_rewrite");
+ COMPLETE_WITH("ddl_command_start", "ddl_command_end", "login",
+ "sql_drop", "table_rewrite");
/*
* 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 0754ef1bce4..8d91e3bf8da 100644
--- a/src/include/catalog/pg_database.dat
+++ b/src/include/catalog/pg_database.dat
@@ -16,7 +16,7 @@
descr => 'default template for new databases',
datname => 'template1', encoding => 'ENCODING',
datlocprovider => 'LOCALE_PROVIDER', datistemplate => 't',
- datallowconn => 't', datconnlimit => '-1', datfrozenxid => '0',
+ datallowconn => 't', dathasloginevt => 'f', datconnlimit => '-1', datfrozenxid => '0',
datminmxid => '1', dattablespace => 'pg_default', datcollate => 'LC_COLLATE',
datctype => 'LC_CTYPE', daticulocale => 'ICU_LOCALE',
daticurules => 'ICU_RULES', datacl => '_null_' },
diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h
index e9eb06b2e53..3e50a570046 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -49,6 +49,9 @@ CATALOG(pg_database,1262,DatabaseRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID
/* new connections allowed? */
bool datallowconn;
+ /* database has login event triggers? */
+ bool dathasloginevt;
+
/*
* Max connections allowed. Negative values have special meaning, see
* DATCONNLIMIT_* defines below.
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index 1c925dbf257..9e3ece50d5f 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -56,6 +56,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/miscadmin.h b/src/include/miscadmin.h
index 14bd574fc24..c9cad452d96 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -203,6 +203,8 @@ extern PGDLLIMPORT Oid MyDatabaseId;
extern PGDLLIMPORT Oid MyDatabaseTableSpace;
+extern PGDLLIMPORT bool MyDatabaseHasLoginEventTriggers;
+
/*
* Date/Time Configuration
*
diff --git a/src/include/storage/lmgr.h b/src/include/storage/lmgr.h
index 4ee91e3cf93..952ebe75cb4 100644
--- a/src/include/storage/lmgr.h
+++ b/src/include/storage/lmgr.h
@@ -99,6 +99,8 @@ extern void UnlockDatabaseObject(Oid classid, Oid objid, uint16 objsubid,
/* Lock a shared-across-databases object (other than a relation) */
extern void LockSharedObject(Oid classid, Oid objid, uint16 objsubid,
LOCKMODE lockmode);
+extern bool ConditionalLockSharedObject(Oid classid, Oid objid, uint16 objsubid,
+ LOCKMODE lockmode);
extern void UnlockSharedObject(Oid classid, Oid objid, uint16 objsubid,
LOCKMODE lockmode);
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index e738ac1c097..553a31874f1 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_MERGE, "MERGE", false, false, true)
PG_CMDTAG(CMDTAG_MOVE, "MOVE", false, false, true)
PG_CMDTAG(CMDTAG_NOTIFY, "NOTIFY", false, false, false)
diff --git a/src/include/utils/evtcache.h b/src/include/utils/evtcache.h
index d340026518a..52052e6252a 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/authentication/t/005_login_trigger.pl b/src/test/authentication/t/005_login_trigger.pl
new file mode 100644
index 00000000000..fec664fb865
--- /dev/null
+++ b/src/test/authentication/t/005_login_trigger.pl
@@ -0,0 +1,157 @@
+# Copyright (c) 2021-2023, PostgreSQL Global Development Group
+
+# Tests of authentication via login trigger. Mostly for rejection via
+# exception, because this scenario cannot be covered with *.sql/*.out regress
+# tests.
+
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Execute a psql command and compare its output towards given regexps
+sub psql_command
+{
+ local $Test::Builder::Level = $Test::Builder::Level + 1;
+ my ($node, $sql, $expected_ret, $test_name, %params) = @_;
+
+ my $connstr;
+ if (defined($params{connstr}))
+ {
+ $connstr = $params{connstr};
+ }
+ else
+ {
+ $connstr = '';
+ }
+
+ # Execute command
+ my ($ret, $stdout, $stderr) = $node->psql('postgres', $sql,
+ connstr => "$connstr");
+
+ # Check return code
+ is($ret, $expected_ret, "$test_name: exit code $expected_ret");
+
+ # Check stdout
+ if (defined($params{log_like}))
+ {
+ my @log_like = @{ $params{log_like} };
+ while (my $regex = shift @log_like)
+ {
+ like($stdout, $regex, "$test_name: log matches");
+ }
+ }
+ if (defined($params{log_unlike}))
+ {
+ my @log_unlike = @{ $params{log_unlike} };
+ while (my $regex = shift @log_unlike)
+ {
+ unlike($stdout, $regex, "$test_name: log unmatches");
+ }
+ }
+ if (defined($params{log_exact}))
+ {
+ is($stdout, $params{log_exact}, "$test_name: log equals");
+ }
+
+ # Check stderr
+ if (defined($params{err_like}))
+ {
+ my @err_like = @{ $params{err_like} };
+ while (my $regex = shift @err_like)
+ {
+ like($stderr, $regex, "$test_name: err matches");
+ }
+ }
+ if (defined($params{err_unlike}))
+ {
+ my @err_unlike = @{ $params{err_unlike} };
+ while (my $regex = shift @err_unlike)
+ {
+ unlike($stderr, $regex, "$test_name: err unmatches");
+ }
+ }
+ if (defined($params{err_exact}))
+ {
+ is($stderr, $params{err_exact}, "$test_name: err equals");
+ }
+
+ return;
+}
+
+# New node
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init(extra => [ '--locale=C', '--encoding=UTF8' ]);
+$node->append_conf(
+ 'postgresql.conf', q{
+wal_level = 'logical'
+max_replication_slots = 4
+max_wal_senders = 4
+});
+$node->start;
+
+# Create temporary roles and log table
+psql_command($node, 'CREATE ROLE alice WITH LOGIN;
+CREATE ROLE mallory WITH LOGIN;
+CREATE TABLE user_logins(id serial, who text);
+GRANT SELECT ON user_logins TO public;
+', 0, 'create tmp objects', log_exact => '', err_exact => ''), ;
+
+# Create login event function and trigger
+psql_command($node,
+ 'CREATE FUNCTION on_login_proc() RETURNS event_trigger AS $$
+BEGIN
+ INSERT INTO user_logins (who) VALUES (SESSION_USER);
+ IF SESSION_USER = \'mallory\' THEN
+ RAISE EXCEPTION \'Hello %! You are NOT welcome here!\', SESSION_USER;
+ END IF;
+ RAISE NOTICE \'Hello %! You are welcome!\', SESSION_USER;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+', 0, 'create trigger function', log_exact => '', err_exact => '');
+
+psql_command($node,
+ 'CREATE EVENT TRIGGER on_login_trigger '
+ .'ON login EXECUTE PROCEDURE on_login_proc();', 0,
+ 'create event trigger', log_exact => '', err_exact => '');
+psql_command($node, 'ALTER EVENT TRIGGER on_login_trigger ENABLE ALWAYS;', 0,
+ 'alter event trigger', log_exact => '',
+ err_like => [qr/You are welcome/]);
+
+# Check the two requests were logged via login trigger
+psql_command($node, 'SELECT COUNT(*) FROM user_logins;', 0, 'select count',
+ log_exact => '2', err_like => [qr/You are welcome/]);
+
+# Try to log as allowed Alice and disallowed Mallory (two times)
+psql_command($node, 'SELECT 1;', 0, 'try alice', connstr => 'user=alice',
+ log_exact => '1', err_like => [qr/You are welcome/],
+ err_unlike => [qr/You are NOT welcome/]);
+psql_command($node, 'SELECT 1;', 2, 'try mallory', connstr => 'user=mallory',
+ log_exact => '', err_like => [qr/You are NOT welcome/],
+ err_unlike => [qr/You are welcome/]);
+psql_command($node, 'SELECT 1;', 2, 'try mallory', connstr => 'user=mallory',
+ log_exact => '', err_like => [qr/You are NOT welcome/],
+ err_unlike => [qr/You are welcome/]);
+
+# Check that Alice's login record is here, while the Mallory's one is not
+psql_command($node, 'SELECT * FROM user_logins;', 0, 'select *',
+ log_like => [qr/3\|alice/], log_unlike => [qr/mallory/],
+ err_like => [qr/You are welcome/]);
+
+# Check total number of successful logins so far
+psql_command($node, 'SELECT COUNT(*) FROM user_logins;', 0, 'select count',
+ log_exact => '5', err_like => [qr/You are welcome/]);
+
+# Cleanup the temporary stuff
+psql_command($node, 'DROP EVENT TRIGGER on_login_trigger;', 0,
+ 'drop event trigger', log_exact => '',
+ err_like => [qr/You are welcome/]);
+psql_command($node, 'DROP TABLE user_logins;
+DROP FUNCTION on_login_proc;
+DROP ROLE mallory;
+DROP ROLE alice;
+', 0, 'cleanup', log_exact => '', err_exact => '');
+
+done_testing();
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 0c72ba09441..8644854eb39 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -46,6 +46,24 @@ $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 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_replay_catchup($node_standby_1);
$node_standby_1->wait_for_replay_catchup($node_standby_2, $node_primary);
@@ -384,6 +402,11 @@ sub replay_check
replay_check();
+my $evttrig = $node_standby_1->safe_psql('postgres', "SELECT evtname FROM pg_event_trigger WHERE evtevent = 'login'");
+is($evttrig, 'on_login_trigger', 'Name of login trigger');
+$evttrig = $node_standby_2->safe_psql('postgres', "SELECT evtname FROM pg_event_trigger WHERE evtevent = 'login'");
+is($evttrig, 'on_login_trigger', 'Name of login trigger');
+
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 0b87a42d0a9..eaaff6ba6f1 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -638,3 +638,48 @@ NOTICE: DROP POLICY dropped policy
CREATE POLICY pguc ON event_trigger_test USING (FALSE);
SET event_triggers = 'off';
DROP POLICY pguc ON event_trigger_test;
+-- Login event triggers
+CREATE TABLE user_logins(id serial, who text);
+GRANT SELECT ON user_logins TO public;
+CREATE FUNCTION on_login_proc() RETURNS event_trigger AS $$
+BEGIN
+ INSERT INTO user_logins (who) VALUES (SESSION_USER);
+ 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 COUNT(*) FROM user_logins;
+ count
+-------
+ 1
+(1 row)
+
+\c
+NOTICE: You are welcome!
+SELECT COUNT(*) FROM user_logins;
+ count
+-------
+ 2
+(1 row)
+
+-- Check dathasloginevt in system catalog
+SELECT dathasloginevt FROM pg_database WHERE datname= :'DBNAME';
+ dathasloginevt
+----------------
+ t
+(1 row)
+
+-- Cleanup
+DROP TABLE user_logins;
+DROP EVENT TRIGGER on_login_trigger;
+DROP FUNCTION on_login_proc();
+\c
+SELECT dathasloginevt FROM pg_database WHERE datname= :'DBNAME';
+ dathasloginevt
+----------------
+ f
+(1 row)
+
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 6f0933b9e88..9c2b7903fba 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -495,3 +495,29 @@ DROP POLICY pguc ON event_trigger_test;
CREATE POLICY pguc ON event_trigger_test USING (FALSE);
SET event_triggers = 'off';
DROP POLICY pguc ON event_trigger_test;
+
+-- Login event triggers
+CREATE TABLE user_logins(id serial, who text);
+GRANT SELECT ON user_logins TO public;
+CREATE FUNCTION on_login_proc() RETURNS event_trigger AS $$
+BEGIN
+ INSERT INTO user_logins (who) VALUES (SESSION_USER);
+ 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 COUNT(*) FROM user_logins;
+\c
+SELECT COUNT(*) FROM user_logins;
+
+-- Check dathasloginevt in system catalog
+SELECT dathasloginevt FROM pg_database WHERE datname= :'DBNAME';
+
+-- Cleanup
+DROP TABLE user_logins;
+DROP EVENT TRIGGER on_login_trigger;
+DROP FUNCTION on_login_proc();
+\c
+SELECT dathasloginevt FROM pg_database WHERE datname= :'DBNAME';
--
2.39.3 (Apple Git-145)
