Hi
Thank you.
> I have applied all your fixes in on_connect_event_trigger-12.patch.
>
> Concerning enable_client_connection_trigger GUC, I think that it is really
> useful: it is the fastest and simplest way to disable login triggers in case
> of some problems with them (not only for superuser itself, but for all
> users). Yes, it can be also done using "ALTER EVENT TRIGGER DISABLE".
> But assume that you have a lot of databases with different login policies
> enforced by on-login event triggers. And you want temporary disable them
> all, for example for testing purposes.
> In this case GUC is most convenient way to do it.
>
>
There was typo in patch
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f810789..8861f1b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1,4 +1,4 @@
-<!-- doc/src/sgml/config.sgml -->
+\<!-- doc/src/sgml/config.sgml -->
I have not any objections against functionality or design. I tested the
performance, and there are no negative impacts when this feature is not
used. There is significant overhead related to plpgsql runtime
initialization, but when this trigger will be used, then probably some
other PLpgSQL procedures and functions will be used too, and then this
overhead can be ignored.
* make without warnings
* make check-world passed
* doc build passed
Possible ToDo:
The documentation can contain a note so usage connect triggers in
environments with short life sessions and very short fast queries without
usage PLpgSQL functions or procedures can have negative impact on
performance due overhead of initialization of PLpgSQL engine.
I'll mark this patch as ready for committers
Regards
Pavel
>
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index 036a72c81e..bb62f25b2a 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -182,7 +182,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 3a2266526c..8100ff761a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2944,6 +2944,17 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dathaslogontriggers</structfield> <type>bool</type>
+ </para>
+ <para>
+ 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.
+ </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/config.sgml b/doc/src/sgml/config.sgml
index 4b60382778..c1914c5ceb 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -996,6 +996,24 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-enable-client-connection-trigger" xreflabel="enable_client_connection_trigger">
+ <term><varname>enable_client_connection_trigger</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>enable_client_connection_trigger</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Enables firing the <literal>client_connection</literal>
+ 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:
+ <literal>psql "dbname=postgres options='-c enable_client_connection_trigger=false'".</literal>
+ Only superuser can change this variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index 0fef9bfcbe..1ecb8c1f45 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -4673,6 +4673,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)
@@ -4698,6 +4699,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..ae40a8e1a2 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>client_connection</literal>,
<literal>ddl_command_start</literal>,
<literal>ddl_command_end</literal>,
<literal>table_rewrite</literal>
@@ -35,6 +36,29 @@
Support for additional events may be added in future releases.
</para>
+ <para>
+ The <literal>client_connection</literal> 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:
+ <itemizedlist>
+ <listitem>
+ <para>
+ The configuration parameter <literal>enable_client_connection_trigger</literal>
+ makes it possible to disable firing the <literal>client_connection</literal>
+ trigger when a client connects.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Errors in the <literal>client_connection</literal> trigger procedure are
+ ignored for superuser. An error message is delivered to the client as
+ <literal>NOTICE</literal> in this case.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </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>,
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 3ffba4e63e..855b65644f 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);
@@ -562,6 +584,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
@@ -577,22 +602,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
@@ -601,9 +622,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 +818,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 d35c5020ea..4e12cc958b 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"
@@ -168,6 +169,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
* ----------------------------------------------------------------
@@ -4031,6 +4035,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 0877bc7e0e..288d6ae829 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, "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 878fcc2236..347e1cc6c8 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -43,6 +43,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"
@@ -927,6 +928,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/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1ab98a2286..f6606b40ea 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2727,6 +2727,7 @@ dumpDatabase(Archive *fout)
i_datacl,
i_rdatacl,
i_datistemplate,
+ i_dathaslogontriggers,
i_datconnlimit,
i_tablespace;
CatalogId dbCatId;
@@ -2739,6 +2740,7 @@ dumpDatabase(Archive *fout)
*datacl,
*rdatacl,
*datistemplate,
+ *dathaslogontriggers,
*datconnlimit,
*tablespace;
uint32 frozenxid,
@@ -2757,7 +2759,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, "
@@ -2783,7 +2785,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 "
@@ -2797,7 +2833,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 "
@@ -2811,7 +2847,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 "
@@ -2825,7 +2861,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 "
@@ -2840,7 +2876,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()",
@@ -2862,6 +2898,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));
@@ -2876,6 +2913,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);
@@ -3049,6 +3087,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 3a43c09bf6..08f00d8fc4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2970,7 +2970,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",
+ "client_connection", "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 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 47bcf40346..7dc15a5ff1 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 407fd6a978..47d481e56c 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;
@@ -53,6 +55,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/000_client_connection_trigger.pl b/src/test/recovery/t/000_client_connection_trigger.pl
new file mode 100644
index 0000000000..3dcd475f72
--- /dev/null
+++ b/src/test/recovery/t/000_client_connection_trigger.pl
@@ -0,0 +1,69 @@
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More;
+if (!$use_unix_sockets)
+{
+ plan skip_all =>
+ "authentication tests cannot run without Unix-domain sockets";
+}
+else
+{
+ plan tests => 5;
+}
+
+# Initialize master node
+my $node = get_new_node('master');
+$node->init;
+$node->start;
+$node->safe_psql('postgres', q{
+CREATE ROLE regress_user LOGIN PASSWORD 'pass';
+CREATE ROLE regress_hacker 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;
+}
+);
+my $res;
+
+$res = $node->safe_psql('postgres', "SELECT 1");
+
+$res = $node->safe_psql('postgres', "SELECT 1",
+ extra_params => [ '-U', 'regress_user', '-w' ]);
+
+my ($ret, $stdout, $stderr) = $node->psql('postgres', "SELECT 1",
+ extra_params => [ '-U', 'regress_hacker', '-w' ]);
+ok( $ret != 0 && $stderr =~ /You are not welcome!/ );
+
+$res = $node->safe_psql('postgres', "SELECT COUNT(1) FROM connects WHERE who = 'regress_user'");
+ok($res == 1);
+
+my $tempdir = TestLib::tempdir;
+command_ok(
+ [ "pg_dumpall", '-p', $node->port, '-c', "--file=$tempdir/regression_dump.sql", ],
+ "dumpall");
+# my $dump_contents = slurp_file("$tempdir/regression_dump.sql");
+# print($dump_contents);
+
+my $node1 = get_new_node('secondary');
+$node1->init;
+$node1->start;
+command_ok(["psql", '-p', $node1->port, '-b', '-f', "$tempdir/regression_dump.sql" ] );
+$res = $node1->safe_psql('postgres', "SELECT 1", extra_params => [ '-U', 'regress_user', '-w' ]);
+$res = $node1->safe_psql('postgres', "SELECT COUNT(1) FROM connects WHERE who = 'regress_user'");
+ok($res == 2);
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 9e31a53de7..b4a21fbc2a 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_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 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_primary->wait_for_catchup($node_standby_1, 'replay',
$node_primary->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 81bdacf59d..88a51022fb 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -86,27 +86,28 @@ select count(*) = 1 as ok from pg_stat_wal;
-- 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();
