diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index db4801c246..76ebb84735 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -185,7 +185,7 @@
{ oid => '1', oid_symbol => 'TemplateDbOid',
descr => 'database\'s default template',
datname => 'template1', encoding => 'ENCODING', datcollate => 'LC_COLLATE',
- datctype => 'LC_CTYPE', datistemplate => 't', datallowconn => 't',
+ datctype => 'LC_CTYPE', datistemplate => 't', datallowconn => 't', dathaslogontriggers => 'f',
datconnlimit => '-1', datlastsysoid => '0', datfrozenxid => '0',
datminmxid => '1', dattablespace => 'pg_default', datacl => '_null_' },
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 612cc69802..1ed1ffb8d7 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2945,6 +2945,17 @@ SCRAM-SHA-256$<iteration count>:&l
+
+
+ dathaslogontriggers bool
+
+
+ Indicates that there are client connection triggers defined for this database.
+ This flag is used to avoid extra lookup of pg_event_trigger table on each backend startup.
+ This flag is used internally by Postgres and should not be manually changed by DBA or application.
+
+
+
datconnlimit int4
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index dd2778611f..960b628176 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -976,6 +976,24 @@ include_dir 'conf.d'
+
+ enable_client_connection_trigger (boolean)
+
+ enable_client_connection_trigger configuration parameter
+
+
+
+
+ Enables firing the client_connection
+ trigger when a client connects. This parameter is switched on by default.
+ Errors in trigger code can prevent user to login to the system.
+ In this case disabling this parameter in connection string can solve the problem:
+ psql "dbname=postgres options='-c enable_client_connection_trigger=false'".
+ Only superuser can change this variable.
+
+
+
+
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index 7266e229a4..752ffc2357 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -4663,6 +4663,7 @@ datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = t (type: 1)
datallowconn = t (type: 1)
+dathaslogontriggers = f (type: 1)
datconnlimit = -1 (type: 5)
datlastsysoid = 11510 (type: 1)
datfrozenxid = 379 (type: 1)
@@ -4688,6 +4689,7 @@ datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = f (type: 1)
datallowconn = t (type: 1)
+dathaslogontriggers = 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..6f99909a74 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
+ client_connection,
ddl_command_start,
ddl_command_end,
table_rewrite
@@ -35,6 +36,29 @@
Support for additional events may be added in future releases.
+
+ The client_connection event occurs when a client connection
+ to the server is established.
+ There are two mechanisms for dealing with any bugs in a trigger procedure for
+ this event which might prevent successful login to the system:
+
+
+
+ The configuration parameter enable_client_connection_trigger
+ makes it possible to disable firing the client_connection
+ trigger when a client connects.
+
+
+
+
+ Errors in the client_connection trigger procedure are
+ ignored for superuser. An error message is delivered to the client as
+ NOTICE in this case.
+
+
+
+
+
The ddl_command_start event occurs just before the
execution of a CREATE, ALTER, DROP,
@@ -1140,7 +1164,7 @@ typedef struct EventTriggerData
- A Complete Event Trigger Example
+ A C language Event Trigger Example
Here is a very simple example of an event trigger function written in C.
@@ -1280,6 +1304,64 @@ $$;
CREATE EVENT TRIGGER no_rewrite_allowed
ON table_rewrite
EXECUTE FUNCTION no_rewrite();
+
+
+
+
+
+ A Database Client Connection Event Trigger Example
+
+
+ The event trigger on the client_connection event
+ can be useful for client connections logging,
+ for verifying the connection and assigning roles according to current circumstances,
+ or for some session data initialization.
+
+
+
+ The following example demonstrates these options.
+
+
+-- create test tables and roles
+CREATE TABLE user_sessions_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 connect 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_sessions_log VALUES (session_user, current_timestamp);
+
+END;
+$$;
+
+-- trigger definition
+CREATE EVENT TRIGGER init_session
+ ON client_connection
+ EXECUTE FUNCTION init_session();
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index f27c3fe8c1..8646db7d3d 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -560,6 +560,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_dathaslogontriggers - 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);
@@ -1627,7 +1628,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_dathaslogontriggers - 1] = BoolGetDatum(datform->dathaslogontriggers);
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 b6894cbf76..d3e3eafddd 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,11 +44,14 @@
#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/syscache.h"
+bool enable_client_connection_trigger;
+
typedef struct EventTriggerQueryState
{
/* memory context for this state's objects */
@@ -130,6 +134,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, "client_connection") != 0 &&
strcmp(stmt->eventname, "table_rewrite") != 0)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -293,6 +298,23 @@ insert_event_trigger_tuple(const char *trigname, const char *eventname, Oid evtO
CatalogTupleInsert(tgrel, tuple);
heap_freetuple(tuple);
+ if (strcmp(eventname, "client_connection") == 0)
+ {
+ Form_pg_database db;
+ Relation pg_db = table_open(DatabaseRelationId, RowExclusiveLock);
+ /* Set dathaslogontriggers 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->dathaslogontriggers)
+ {
+ db->dathaslogontriggers = true;
+ CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+ }
+ table_close(pg_db, RowExclusiveLock);
+ }
+
/* Depend on owner. */
recordDependencyOnOwner(EventTriggerRelationId, trigoid, evtOwner);
@@ -584,6 +606,9 @@ EventTriggerCommonSetup(Node *parsetree,
ListCell *lc;
List *runlist = NIL;
+ /* Get the command tag. */
+ tag = parsetree ? CreateCommandTag(parsetree) : CMDTAG_CONNECT;
+
/*
* 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
@@ -599,22 +624,18 @@ EventTriggerCommonSetup(Node *parsetree,
* relevant command tag.
*/
#ifdef USE_ASSERT_CHECKING
+ if (event == EVT_DDLCommandStart ||
+ event == EVT_DDLCommandEnd ||
+ event == EVT_SQLDrop ||
+ event == EVT_Connect)
{
- 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
@@ -623,9 +644,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
@@ -822,6 +840,117 @@ EventTriggerSQLDrop(Node *parsetree)
list_free(runlist);
}
+static bool
+DatabaseHasLogonTriggers(void)
+{
+ bool has_logon_triggers;
+ HeapTuple tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
+
+ has_logon_triggers = ((Form_pg_database) GETSTRUCT(tuple))->dathaslogontriggers;
+ ReleaseSysCache(tuple);
+ return has_logon_triggers;
+}
+
+/*
+ * Fire connect triggers.
+ */
+void
+EventTriggerOnConnect(void)
+{
+ List *runlist;
+ EventTriggerData trigdata;
+
+ /*
+ * See EventTriggerDDLCommandStart for a discussion about why event
+ * triggers are disabled in single user mode.
+ */
+ if (!IsUnderPostmaster
+ || !OidIsValid(MyDatabaseId)
+ || !enable_client_connection_trigger)
+ return;
+
+ StartTransactionCommand();
+
+ if (DatabaseHasLogonTriggers())
+ {
+ runlist = EventTriggerCommonSetup(NULL,
+ EVT_Connect, "connect",
+ &trigdata);
+
+ if (runlist != NIL)
+ {
+ MemoryContext old_context = CurrentMemoryContext;
+ bool is_superuser = superuser();
+ /*
+ * Make sure anything the main command did will be visible to the event
+ * triggers.
+ */
+ CommandCounterIncrement();
+
+ /* Run the triggers. */
+ PG_TRY();
+ {
+ EventTriggerInvoke(runlist, &trigdata);
+ list_free(runlist);
+ }
+ PG_CATCH();
+ {
+ ErrorData* error;
+ /*
+ * Try to ignore error for superuser to make it possible to login even in case of errors
+ * during trigger execution
+ */
+ if (!is_superuser)
+ PG_RE_THROW();
+
+ MemoryContextSwitchTo(old_context);
+ error = CopyErrorData();
+ FlushErrorState();
+ elog(NOTICE, "client_connection trigger failed with message: %s", error->message);
+ AbortCurrentTransaction();
+ return;
+ }
+ PG_END_TRY();
+ }
+ else
+ {
+ /* Runtlist is empty: clear dathaslogontriggers 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->dathaslogontriggers)
+ {
+ db->dathaslogontriggers = false;
+ CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+ /*
+ * There can be race condition: event trigger may be added after we have scanned
+ * pg_event_trigger table. Repeat this test nuder pg_database table lock.
+ */
+ AcceptInvalidationMessages();
+ runlist = EventTriggerCommonSetup(NULL,
+ EVT_Connect, "connect",
+ &trigdata);
+ if (runlist != NULL) /* if list is not empty, then restore the flag */
+ {
+ db->dathaslogontriggers = true;
+ CatalogTupleUpdate(pg_db, &tuple->t_self, tuple);
+ }
+ }
+ table_close(pg_db, RowExclusiveLock);
+ }
+ }
+ CommitTransactionCommand();
+}
+
/*
* Fire table_rewrite triggers.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 174c72a14b..60954939c0 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"
@@ -167,6 +168,9 @@ static ProcSignalReason RecoveryConflictReason;
static MemoryContext row_description_context = NULL;
static StringInfoData row_description_buf;
+/* Hook for plugins to get control at start of session */
+client_connection_hook_type client_connection_hook = EventTriggerOnConnect;
+
/* ----------------------------------------------------------------
* decls for routines only used in this file
* ----------------------------------------------------------------
@@ -4029,6 +4033,11 @@ PostgresMain(int argc, char *argv[],
if (!IsUnderPostmaster)
PgStartTime = GetCurrentTimestamp();
+ if (client_connection_hook)
+ {
+ (*client_connection_hook) ();
+ }
+
/*
* POSTGRES main processing loop begins here
*
diff --git a/src/backend/utils/cache/evtcache.c b/src/backend/utils/cache/evtcache.c
index 73d091d1f6..d134aac7d4 100644
--- a/src/backend/utils/cache/evtcache.c
+++ b/src/backend/utils/cache/evtcache.c
@@ -169,6 +169,8 @@ BuildEventTriggerCache(void)
event = EVT_SQLDrop;
else if (strcmp(evtevent, "table_rewrite") == 0)
event = EVT_TableRewrite;
+ else if (strcmp(evtevent, "client_connection") == 0)
+ event = EVT_Connect;
else
continue;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 974235d705..a37c9a45a2 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -40,6 +40,7 @@
#include "commands/async.h"
#include "commands/prepare.h"
#include "commands/trigger.h"
+#include "commands/event_trigger.h"
#include "commands/user.h"
#include "commands/vacuum.h"
#include "commands/variable.h"
@@ -931,6 +932,18 @@ static const unit_conversion time_unit_conversion_table[] =
static struct config_bool ConfigureNamesBool[] =
{
+ {
+ {"enable_client_connection_trigger", PGC_SU_BACKEND, DEVELOPER_OPTIONS,
+ gettext_noop("Enables the client_connection event trigger."),
+ gettext_noop("In case of errors in the ON client_connection EVENT TRIGGER procedure, "
+ "this parameter can be used to disable trigger activation "
+ "and provide access to the database."),
+ GUC_EXPLAIN
+ },
+ &enable_client_connection_trigger,
+ true,
+ NULL, NULL, NULL
+ },
{
{"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of sequential-scan plans."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 2663bccfda..92707ed703 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -72,6 +72,7 @@
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
+#enable_client_connection_trigger = true # enables firing the client_connection trigger when a client connect
# - TCP settings -
# see "man tcp" for details
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f8d99b7722..29a9f08ec9 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2718,6 +2718,7 @@ dumpDatabase(Archive *fout)
i_datacl,
i_rdatacl,
i_datistemplate,
+ i_dathaslogontriggers,
i_datconnlimit,
i_tablespace;
CatalogId dbCatId;
@@ -2730,6 +2731,7 @@ dumpDatabase(Archive *fout)
*datacl,
*rdatacl,
*datistemplate,
+ *dathaslogontriggers,
*datconnlimit,
*tablespace;
uint32 frozenxid,
@@ -2748,7 +2750,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 >= 140000)
{
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, "
"(%s datdba) AS dba, "
@@ -2774,7 +2776,41 @@ dumpDatabase(Archive *fout)
" AS permp(orig_acl) "
" WHERE acl = orig_acl)) AS rdatacls) "
" AS rdatacl, "
- "datistemplate, datconnlimit, "
+ "datistemplate, datconnlimit, dathaslogontriggers, "
+ "(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 dathaslogontriggers, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
@@ -2788,7 +2824,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 dathaslogontriggers"
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
@@ -2802,7 +2838,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 dathaslogontriggers, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
@@ -2816,7 +2852,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 dathaslogontriggers, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
@@ -2831,7 +2867,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 dathaslogontriggers, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace "
"FROM pg_database "
"WHERE datname = current_database()",
@@ -2853,6 +2889,7 @@ dumpDatabase(Archive *fout)
i_rdatacl = PQfnumber(res, "rdatacl");
i_datistemplate = PQfnumber(res, "datistemplate");
i_datconnlimit = PQfnumber(res, "datconnlimit");
+ i_dathaslogontriggers = PQfnumber(res, "dathaslogontriggers");
i_tablespace = PQfnumber(res, "tablespace");
dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
@@ -2867,6 +2904,7 @@ dumpDatabase(Archive *fout)
datacl = PQgetvalue(res, 0, i_datacl);
rdatacl = PQgetvalue(res, 0, i_rdatacl);
datistemplate = PQgetvalue(res, 0, i_datistemplate);
+ dathaslogontriggers = PQgetvalue(res, 0, i_dathaslogontriggers);
datconnlimit = PQgetvalue(res, 0, i_datconnlimit);
tablespace = PQgetvalue(res, 0, i_tablespace);
@@ -3040,6 +3078,14 @@ dumpDatabase(Archive *fout)
appendPQExpBufferStr(delQry, ";\n");
}
+ if (strcmp(dathaslogontriggers, "t") == 0)
+ {
+ appendPQExpBufferStr(creaQry, "UPDATE pg_catalog.pg_database "
+ "SET dathaslogontriggers = 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 eb018854a5..c1c0cd6753 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2871,7 +2871,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("ON");
/* Complete CREATE EVENT TRIGGER 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",
+ "client_connection", "sql_drop");
/*
* Complete CREATE EVENT TRIGGER ON . EXECUTE FUNCTION
diff --git a/src/include/catalog/pg_database.dat b/src/include/catalog/pg_database.dat
index 21cd6604f3..4c10575a31 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', dathaslogontriggers => '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 f623ee81b7..f60af37089 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 on-login triggers */
+ bool dathaslogontriggers;
+
/* 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 28b352051b..b00bf66a98 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -21,6 +21,8 @@
#include "tcop/deparse_utility.h"
#include "utils/aclchk_internal.h"
+extern bool enable_client_connection_trigger; /* GUC */
+
typedef struct EventTriggerData
{
NodeTag type;
@@ -54,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 EventTriggerOnConnect(void);
extern bool EventTriggerBeginCompleteQuery(void);
extern void EventTriggerEndCompleteQuery(void);
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index be94852bbd..988aa39f4b 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -80,6 +80,7 @@ PG_CMDTAG(CMDTAG_CLUSTER, "CLUSTER", false, false, false)
PG_CMDTAG(CMDTAG_COMMENT, "COMMENT", true, false, false)
PG_CMDTAG(CMDTAG_COMMIT, "COMMIT", false, false, false)
PG_CMDTAG(CMDTAG_COMMIT_PREPARED, "COMMIT PREPARED", false, false, false)
+PG_CMDTAG(CMDTAG_CONNECT, "CONNECT", true, false, false)
PG_CMDTAG(CMDTAG_COPY, "COPY", false, false, true)
PG_CMDTAG(CMDTAG_COPY_FROM, "COPY FROM", false, false, false)
PG_CMDTAG(CMDTAG_CREATE_ACCESS_METHOD, "CREATE ACCESS METHOD", true, false, false)
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h
index bd30607b07..90376b22c7 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -30,6 +30,11 @@ extern PGDLLIMPORT const char *debug_query_string;
extern int max_stack_depth;
extern int PostAuthDelay;
+/* Hook for plugins to get control at start and end of session */
+typedef void (*client_connection_hook_type) (void);
+
+extern PGDLLIMPORT client_connection_hook_type client_connection_hook;
+
/* GUC-configurable parameters */
typedef enum
diff --git a/src/include/utils/evtcache.h b/src/include/utils/evtcache.h
index bb1e39eb64..ab7e8c39b8 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_Connect,
} EventTriggerEvent;
typedef struct
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 778f11b28b..652995b021 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -43,6 +43,27 @@ $node_standby_2->start;
$node_master->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 connects(id serial, who text);
+
+CREATE FUNCTION on_login_proc() RETURNS EVENT_TRIGGER AS $$
+BEGIN
+ IF NOT pg_is_in_recovery() THEN
+ INSERT INTO connects (who) VALUES (session_user);
+ END IF;
+ IF session_user = 'regress_hacker' THEN
+ RAISE EXCEPTION 'You are not welcome!';
+ END IF;
+ RAISE NOTICE 'You are welcome!';
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+CREATE EVENT TRIGGER on_login_trigger ON client_connection EXECUTE FUNCTION on_login_proc();
+ALTER EVENT TRIGGER on_login_trigger ENABLE ALWAYS;
+});
+
# Wait for standbys to catch up
$node_master->wait_for_catchup($node_standby_1, 'replay',
$node_master->lsn('insert'));
@@ -266,6 +287,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 bdd0ffcdaf..bdfa3eefb8 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -536,3 +536,40 @@ NOTICE: DROP POLICY - ddl_command_end
DROP EVENT TRIGGER start_rls_command;
DROP EVENT TRIGGER end_rls_command;
DROP EVENT TRIGGER sql_drop_command;
+-- On session start triggers
+create table connects(id serial, who text);
+create function on_login_proc() returns event_trigger as $$
+begin
+ insert into connects (who) values ('I am');
+ raise notice 'You are welcome!';
+end;
+$$ language plpgsql;
+create event trigger on_login_trigger on client_connection execute procedure on_login_proc();
+alter event trigger on_login_trigger enable always;
+\c
+NOTICE: You are welcome!
+select * from connects;
+ id | who
+----+------
+ 1 | I am
+(1 row)
+
+\c
+NOTICE: You are welcome!
+select * from connects;
+ id | who
+----+------
+ 1 | I am
+ 2 | I am
+(2 rows)
+
+-- Test handing exeptions in client_connection trigger
+drop table connects;
+-- superuser should ignore error
+\c
+NOTICE: client_connection trigger failed with message: relation "connects" does not exist
+-- suppress trigger firing
+\c "dbname=regression options='-c enable_client_connection_trigger=false'"
+-- Cleanup
+drop event trigger on_login_trigger;
+drop function on_login_proc();
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index c3b988597c..dc672ab1db 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -77,27 +77,28 @@ select count(*) = 0 as ok from pg_stat_wal_receiver;
-- This is to record the prevailing planner enable_foo settings during
-- a regression test run.
select name, setting from pg_settings where name like 'enable%';
- name | setting
---------------------------------+---------
- enable_bitmapscan | on
- enable_gathermerge | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_incremental_sort | on
- enable_indexonlyscan | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_parallel_append | on
- enable_parallel_hash | on
- enable_partition_pruning | on
- enable_partitionwise_aggregate | off
- enable_partitionwise_join | off
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
-(18 rows)
+ name | setting
+----------------------------------+---------
+ enable_bitmapscan | on
+ enable_client_connection_trigger | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(19 rows)
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
-- more-or-less working. We can't test their contents in any great detail
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 18b2a267cb..abece80426 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -429,3 +429,31 @@ DROP POLICY p2 ON event_trigger_test;
DROP EVENT TRIGGER start_rls_command;
DROP EVENT TRIGGER end_rls_command;
DROP EVENT TRIGGER sql_drop_command;
+
+-- On session start triggers
+create table connects(id serial, who text);
+create function on_login_proc() returns event_trigger as $$
+begin
+ insert into connects (who) values ('I am');
+ raise notice 'You are welcome!';
+end;
+$$ language plpgsql;
+create event trigger on_login_trigger on client_connection execute procedure on_login_proc();
+alter event trigger on_login_trigger enable always;
+\c
+select * from connects;
+\c
+select * from connects;
+
+-- Test handing exeptions in client_connection trigger
+
+drop table connects;
+-- superuser should ignore error
+\c
+-- suppress trigger firing
+\c "dbname=regression options='-c enable_client_connection_trigger=false'"
+
+
+-- Cleanup
+drop event trigger on_login_trigger;
+drop function on_login_proc();