From 6c78002e920b603028dd898d1311233ed7702829 Mon Sep 17 00:00:00 2001 From: Greg Nancarrow Date: Wed, 9 Dec 2020 22:47:36 +1100 Subject: [PATCH v1 2/2] Add a new "client_connection" event, supporting a "logon trigger". The client_connection event occurs when a client connection to the server is established. Discussion: https://www.postgresql.org/message-id/flat/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru --- doc/src/sgml/event-trigger.sgml | 29 ++++++ src/backend/commands/event_trigger.c | 101 +++++++++++++++++---- src/backend/tcop/postgres.c | 9 ++ src/backend/utils/cache/evtcache.c | 2 + src/include/commands/event_trigger.h | 1 + src/include/tcop/cmdtaglist.h | 1 + src/include/tcop/tcopprot.h | 5 + src/include/utils/evtcache.h | 3 +- .../recovery/t/000_client_connection_trigger.pl | 69 ++++++++++++++ src/test/recovery/t/001_stream_rep.pl | 24 +++++ src/test/regress/expected/event_trigger.out | 37 ++++++++ src/test/regress/sql/event_trigger.sql | 28 ++++++ 12 files changed, 290 insertions(+), 19 deletions(-) create mode 100644 src/test/recovery/t/000_client_connection_trigger.pl diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index 60366a9..bd593dd 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -28,6 +28,7 @@ An event trigger fires whenever the event with which it is associated occurs in the database in which it is defined. Currently, the only supported events are + client_connection, ddl_command_start, ddl_command_end, table_rewrite @@ -36,6 +37,34 @@ + The client_connection event occurs when a client connection + to the server is established. + There are two mechanisms for dealing with any bugs in a trigger procedure for + this event which might prevent successful login to the system: + + + + Event triggers may be temporarily disabled in order to allow login to the + system so that the trigger procedure can be corrected. + The configuration parameter disable_event_triggers + makes it possible to disable firing the client_connection + trigger when a client connects, provided that the user is the owner of the + database to which the event trigger belongs, or has superuser privileges. + This may be preferable to the alternative of restarting in single-user + mode, in which event triggers are disabled. + + + + + Errors in the client_connection trigger procedure are + ignored for superuser. An error message is delivered to the client as + NOTICE in this case. + + + + + + The ddl_command_start event occurs just before the execution of a CREATE, ALTER, DROP, SECURITY LABEL, diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index d0dd4aa..71d2e3a 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -133,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), @@ -565,6 +566,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 @@ -580,22 +584,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 @@ -607,9 +607,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 @@ -819,6 +816,74 @@ EventTriggerSQLDrop(Node *parsetree) list_free(runlist); } +/* + * 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)) + return; + + /* + * If all event triggers are disabled, an empty transaction can be + * avoided by checking here. + */ + if (EventTriggersDisabled()) + return; + + StartTransactionCommand(); + + 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..57dd4b6 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 client connection */ +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/include/commands/event_trigger.h b/src/include/commands/event_trigger.h index c4940b8..1d5e66d 100644 --- a/src/include/commands/event_trigger.h +++ b/src/include/commands/event_trigger.h @@ -55,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..f4c2c24 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 time of client cnnection */ +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..d1639bc 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 client connection 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_event_triggers=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..69e62b0 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 client connection 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_event_triggers=true'" + + +-- Cleanup +drop event trigger on_login_trigger; +drop function on_login_proc(); -- 1.8.3.1