Hi,
I have upgraded the patch for the 14th version.
>Вторник, 16 марта 2021, 14:32 +03:00 от Ivan Panchenko <w...@mail.ru>:
>
>Hi,
>
>Thank you, Konstantin, for this very good feature with numerous use cases.
>Please find the modified patch attached.
>
>I’ve added the ‘enable_client_connection_trigger’ GUC to the sample config
>file and also an additional example page to the docs.
>Check world has passed and it is ready for committer.
>
>>Четверг, 28 января 2021, 12:04 +03:00 от Konstantin Knizhnik <
>>k.knizh...@postgrespro.ru >:
>>
>>
>>
>>On 28.01.2021 5:47, Amit Kapila wrote:
>>> On Mon, Dec 28, 2020 at 5:46 PM Masahiko Sawada < sawada.m...@gmail.com >
>>> wrote:
>>>> On Sat, Dec 26, 2020 at 4:04 PM Pavel Stehule < pavel.steh...@gmail.com >
>>>> wrote:
>>>>>
>>>>>
>>>>> so 26. 12. 2020 v 8:00 odesílatel Pavel Stehule < pavel.steh...@gmail.com
>>>>> > napsal:
>>>>>> 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
>>>>>
>>>>> looks so this patch has not entry in commitfestapp 2021-01
>>>>>
>>>> Yeah, please register this patch before the next CommitFest[1] starts,
>>>> 2021-01-01 AoE[2].
>>>>
>>> Konstantin, did you register this patch in any CF? Even though the
>>> reviewer seems to be happy with the patch, I am afraid that we might
>>> lose track of this unless we register it.
>>> Yes, certainly:
>>https://commitfest.postgresql.org/31/2900/
>>
>>--
>>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 b33e59d5e4..2bb5804e76 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 6d06ad22b9..7e0113f1e8 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2968,6 +2968,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 7e32b0686c..d6d9b3eb34 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1035,6 +1035,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 86c078e17d..c3176e763f 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -4731,6 +4731,7 @@ datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = t (type: 1)
datallowconn = t (type: 1)
+dathaslogontriggers = f (type: 1)
datconnlimit = -1 (type: 5)
datlastsysoid = 11510 (type: 1)
datfrozenxid = 379 (type: 1)
@@ -4756,6 +4757,7 @@ datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = f (type: 1)
datallowconn = t (type: 1)
+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
+ <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>,
@@ -1140,7 +1164,7 @@ typedef struct EventTriggerData
</sect1>
<sect1 id="event-trigger-example">
- <title>A Complete Event Trigger Example</title>
+ <title>A C language Event Trigger Example</title>
<para>
Here is a very simple example of an event trigger function written in C.
@@ -1280,6 +1304,64 @@ $$;
CREATE EVENT TRIGGER no_rewrite_allowed
ON table_rewrite
EXECUTE FUNCTION no_rewrite();
+</programlisting>
+ </para>
+ </sect1>
+
+ <sect1 id="event-trigger-database-client-connection-example">
+ <title>A Database Client Connection Event Trigger Example</title>
+
+ <para>
+ The event trigger on the <literal>client_connection</literal> 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.
+ </para>
+
+ <para>
+ The following example demonstrates these options.
+<programlisting>
+
+-- 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();
</programlisting>
</para>
</sect1>
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 2b159b60eb..6ed0df3d15 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 5bde507c75..65d467b8b8 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 8cea10c901..e9f6da3501 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"
@@ -182,6 +183,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
* ----------------------------------------------------------------
@@ -4165,6 +4169,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 460b720a65..d0093191b8 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 ee731044b6..875a00d049 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -47,6 +47,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"
@@ -952,6 +953,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 6e36e4c2ef..55ad7e35d0 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 339c393718..faec0dbfe1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2807,6 +2807,7 @@ dumpDatabase(Archive *fout)
i_datacl,
i_rdatacl,
i_datistemplate,
+ i_dathaslogontriggers,
i_datconnlimit,
i_tablespace;
CatalogId dbCatId;
@@ -2819,6 +2820,7 @@ dumpDatabase(Archive *fout)
*datacl,
*rdatacl,
*datistemplate,
+ *dathaslogontriggers,
*datconnlimit,
*tablespace;
uint32 frozenxid,
@@ -2837,7 +2839,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, "
@@ -2863,7 +2865,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 "
@@ -2877,7 +2913,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 "
@@ -2891,7 +2927,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 "
@@ -2905,7 +2941,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 "
@@ -2920,7 +2956,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()",
@@ -2942,6 +2978,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));
@@ -2956,6 +2993,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);
@@ -3129,6 +3167,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 6598c5369a..15992c22b0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3008,7 +3008,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 b8aa1364a0..b89906e8ce 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 d3de45821c..b30574f79d 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 c11bf2d781..e70d68b0f3 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 9ba24d4ca9..0e64f90103 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 968345404e..159fe5d939 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -31,6 +31,11 @@ extern int max_stack_depth;
extern int PostAuthDelay;
extern int client_connection_check_interval;
+/* 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 58ddb71cb1..2440b408d1 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 df6fdc20d1..87736d82c7 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -46,6 +46,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'));
@@ -339,6 +360,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 369f3d7d84..49e0da7c9b 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -553,3 +553,40 @@ SELECT
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 0bb558d93c..b395bc7a88 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -93,29 +93,30 @@ 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_async_append | on
- 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_resultcache | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
-(20 rows)
+ name | setting
+----------------------------------+---------
+ enable_async_append | on
+ 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_resultcache | on
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(21 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 e79c5f0b5d..a16ba8455e 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -440,3 +440,31 @@ SELECT
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();
