On 15.12.2020 20:16, Pavel Stehule wrote:
Please notice that we still need GUC to disable on-login
triggers: to make it possible for superuser who did mistake
and defined incorrect on-login trigger to
login to the system.
Do we need GUC to disable all other event triggers? May be I
am wrong, but I do not see much need in such GUC: error in
any of such event triggers is non fatal
and can be easily reverted.
So the only question is whether
"disable_client_connection_trigger" should be true by default
or not...
I agree with you that @2 is a little bit chaotic and @1 looks
like a workaround.
But from my point of view @3 is not the best solution but
overkill: maintaining yet another shared hash just to save
few milliseconds on login seems to be too high price.
Actually there are many things which are loaded by new
backend from the database on start: for example - catalog.
This is why launch of new backend is an expensive operation.
Certainly if we execute "select 1", then system catalog is
not needed...
But does anybody start new backend just to execute "select 1"
and exit?
I understand so the implementation of a new shared cache can be a
lot of work. The best way is enhancing pg_database about one
column with information about the login triggers
(dathaslogontriggers). In init time these data are in syscache,
and can be easily checked. Some like pg_attribute have an
atthasdef column. Same fields has pg_class - relhasrules,
relhastriggers, ... Then the overhead of this design should be
really zero.
What do you think about it?
I like this approach more than implementation of shared hash.
But still I have some concerns:
1. pg_database table format has to be changed. Certainly it is not
something completely unacceptable. But IMHO we should try to avoid
modification of such commonly used catalog tables as much as possible.
yes, I know. Unfortunately I don't see any other and correct
solution. There should be more wide discussion before this work about
this topic. On second hand, this change should not break anything (if
this new field will be placed on as the last field). The logon trigger
really looks like a database trigger - so I think so this semantic is
correct. I have no idea if it is acceptable for committers :-/. I hope
so.
The fact that the existence of a logon trigger can be visible from a
shared database object can be an interesting feature too.
2. It is not so easy to maintain this flag. There can be multiple
on-login triggers defined. If such trigger is dropped, we can not
just clear this flag.
We should check if other triggers exist. Now assume that there are
two triggers and two concurrent transactions dropping each one.
According to their snapshots them do not see changes made by other
transaction. So them remove both triggers but didn't clear the flag.
Certainly we can use counter instead of flag. But I am not sure
that their will be not other problems with maintaining counter.
I don't think it is necessary. My opinion is not too strong, but if
pg_class doesn't need to hold a number of triggers, then I think so
pg_database doesn't need to hold this number too.
Attached please find new versoin of the patch based on
on_connect_event_trigger_WITH_SUGGESTED_UPDATES.patch
So there is still only "disable_client_connection_trigger" GUC? because
we need possibility to disable client connect triggers and there is no
such need for other event types.
As you suggested have added "dathaslogontriggers" flag which is set
when client connection trigger is created.
This flag is never cleaned (to avoid visibility issues mentioned in my
previous mail).
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 60366a9..00f69b8 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>
@@ -36,6 +37,29 @@
</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>disable_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>,
<literal>SECURITY LABEL</literal>,
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index f27c3fe..8646db7 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 3ffba4e..39b42d6 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"
@@ -48,6 +49,8 @@
#include "utils/rel.h"
#include "utils/syscache.h"
+bool disable_client_connection_trigger;
+
typedef struct EventTriggerQueryState
{
/* memory context for this state's objects */
@@ -130,6 +133,7 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
if (strcmp(stmt->eventname, "ddl_command_start") != 0 &&
strcmp(stmt->eventname, "ddl_command_end") != 0 &&
strcmp(stmt->eventname, "sql_drop") != 0 &&
+ strcmp(stmt->eventname, "client_connection") != 0 &&
strcmp(stmt->eventname, "table_rewrite") != 0)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -293,6 +297,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 +583,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 +601,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 +621,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 +817,85 @@ 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)
+ || disable_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();
+ }
+ }
+ CommitTransactionCommand();
+}
+
/*
* Fire table_rewrite triggers.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 3679799..ce9b98e 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
* ----------------------------------------------------------------
@@ -4012,6 +4016,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 0427795..c621b8f 100644
--- a/src/backend/utils/cache/evtcache.c
+++ b/src/backend/utils/cache/evtcache.c
@@ -168,6 +168,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 245a347..5c7e339 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"
@@ -928,6 +929,16 @@ static const unit_conversion time_unit_conversion_table[] =
static struct config_bool ConfigureNamesBool[] =
{
{
+ {"disable_client_connection_trigger", PGC_SU_BACKEND, DEVELOPER_OPTIONS,
+ gettext_noop("Disables 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
+ },
+ &disable_client_connection_trigger,
+ false,
+ NULL, NULL, NULL
+ },
+ {
{"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of sequential-scan plans."),
NULL,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index dc1d41d..a9a7c1f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2726,6 +2726,7 @@ dumpDatabase(Archive *fout)
i_datacl,
i_rdatacl,
i_datistemplate,
+ i_dathaslogontriggers,
i_datconnlimit,
i_tablespace;
CatalogId dbCatId;
@@ -2738,6 +2739,7 @@ dumpDatabase(Archive *fout)
*datacl,
*rdatacl,
*datistemplate,
+ *dathaslogontriggers,
*datconnlimit,
*tablespace;
uint32 frozenxid,
@@ -2756,7 +2758,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, "
@@ -2782,7 +2784,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 "
@@ -2796,7 +2832,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, dathaslogontriggers"
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
@@ -2810,7 +2846,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 "
@@ -2824,7 +2860,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 "
@@ -2839,7 +2875,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()",
@@ -2861,6 +2897,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));
@@ -2875,6 +2912,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);
@@ -3048,6 +3086,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/include/catalog/pg_database.dat b/src/include/catalog/pg_database.dat
index 21cd660..4c10575 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 47bcf40..7dc15a5 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 407fd6a..d5e86af 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 disable_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 be94852..988aa39 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 bd30607..90376b2 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 bb1e39e..ab7e8c3 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 0000000..3dcd475
--- /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 9e31a53..b4a21fb 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 bdd0ffc..3ecbd6a 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 disable_client_connection_trigger=true'"
+-- Cleanup
+drop event trigger on_login_trigger;
+drop function on_login_proc();
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 18b2a26..a1c5cf2 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 disable_client_connection_trigger=true'"
+
+
+-- Cleanup
+drop event trigger on_login_trigger;
+drop function on_login_proc();
