Skip to content

Commit e83d1b0

Browse files
akorotkovakorotkov
committed
Add support event triggers on authenticated login
This commit introduces trigger on login event, allowing to fire some actions right on the user connection. This can be useful for logging or connection check purposes as well as for some personalization of environment. Usage details are described in the documentation included, but shortly usage is the same as for other triggers: create function returning event_trigger and then create event trigger on login event. In order to prevent the connection time overhead when there are no triggers the commit introduces pg_database.dathasloginevt flag, which indicates database has active login triggers. This flag is set by CREATE/ALTER EVENT TRIGGER command, and unset at connection time when no active triggers found. Author: Konstantin Knizhnik, Mikhail Gribkov Discussion: https://postgr.es/m/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru Reviewed-by: Pavel Stehule, Takayuki Tsunakawa, Greg Nancarrow, Ivan Panchenko Reviewed-by: Daniel Gustafsson, Teodor Sigaev, Robert Haas, Andres Freund Reviewed-by: Tom Lane, Andrey Sokolov, Zhihong Yu, Sergey Shinderuk Reviewed-by: Gregory Stark, Nikita Malakhov, Ted Yu
1 parent c558e6f commit e83d1b0

File tree

25 files changed

+644
-21
lines changed

25 files changed

+644
-21
lines changed

‎doc/src/sgml/bki.sgml

+1-1
Original file line numberDiff line numberDiff line change
@@ -184,7 +184,7 @@
184184
descr => 'database\'s default template',
185185
datname => 'template1', encoding => 'ENCODING',
186186
datlocprovider => 'LOCALE_PROVIDER', datistemplate => 't',
187-
datallowconn => 't', datconnlimit => '-1', datfrozenxid => '0',
187+
datallowconn => 't', dathasloginevt => 'f', datconnlimit => '-1', datfrozenxid => '0',
188188
datminmxid => '1', dattablespace => 'pg_default', datcollate => 'LC_COLLATE',
189189
datctype => 'LC_CTYPE', daticulocale => 'ICU_LOCALE', datacl => '_null_' },
190190

‎doc/src/sgml/catalogs.sgml

+13
Original file line numberDiff line numberDiff line change
@@ -3035,6 +3035,19 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
30353035
</para></entry>
30363036
</row>
30373037

3038+
<row>
3039+
<entry role="catalog_table_entry"><para role="column_definition">
3040+
<structfield>dathasloginevt</structfield> <type>bool</type>
3041+
</para>
3042+
<para>
3043+
Indicates that there are login event triggers defined for this database.
3044+
This flag is used to avoid extra lookups on the
3045+
<structname>pg_event_trigger</structname> table during each backend
3046+
startup. This flag is used internally by <productname>PostgreSQL</productname>
3047+
and should not be manually altered or read for monitoring purposes.
3048+
</para></entry>
3049+
</row>
3050+
30383051
<row>
30393052
<entry role="catalog_table_entry"><para role="column_definition">
30403053
<structfield>datconnlimit</structfield> <type>int4</type>

‎doc/src/sgml/ecpg.sgml

+2
Original file line numberDiff line numberDiff line change
@@ -4769,6 +4769,7 @@ datdba = 10 (type: 1)
47694769
encoding = 0 (type: 5)
47704770
datistemplate = t (type: 1)
47714771
datallowconn = t (type: 1)
4772+
dathasloginevt = f (type: 1)
47724773
datconnlimit = -1 (type: 5)
47734774
datfrozenxid = 379 (type: 1)
47744775
dattablespace = 1663 (type: 1)
@@ -4793,6 +4794,7 @@ datdba = 10 (type: 1)
47934794
encoding = 0 (type: 5)
47944795
datistemplate = f (type: 1)
47954796
datallowconn = t (type: 1)
4797+
dathasloginevt = f (type: 1)
47964798
datconnlimit = -1 (type: 5)
47974799
datfrozenxid = 379 (type: 1)
47984800
dattablespace = 1663 (type: 1)

‎doc/src/sgml/event-trigger.sgml

+94
Original file line numberDiff line numberDiff line change
@@ -28,13 +28,32 @@
2828
An event trigger fires whenever the event with which it is associated
2929
occurs in the database in which it is defined. Currently, the only
3030
supported events are
31+
<literal>login</literal>,
3132
<literal>ddl_command_start</literal>,
3233
<literal>ddl_command_end</literal>,
3334
<literal>table_rewrite</literal>
3435
and <literal>sql_drop</literal>.
3536
Support for additional events may be added in future releases.
3637
</para>
3738

39+
<para>
40+
The <literal>login</literal> event occurs when an authenticated user logs
41+
into the system. Any bug in a trigger procedure for this event may
42+
prevent successful login to the system. Such bugs may be fixed by
43+
setting <xref linkend="guc-event-triggers"/> is set to <literal>false</literal>
44+
either in a connection string or configuration file. Alternative is
45+
restarting the system in single-user mode (as event triggers are
46+
disabled in this mode). See the <xref linkend="app-postgres"/> reference
47+
page for details about using single-user mode.
48+
The <literal>login</literal> event will also fire on standby servers.
49+
To prevent servers from becoming inaccessible, such triggers must avoid
50+
writing anything to the database when running on a standby.
51+
Also, it's recommended to avoid long-running queries in
52+
<literal>login</literal> event triggers. Notes that, for instance,
53+
cancelling connection in <application>psql</application> wouldn't cancel
54+
the in-progress <literal>login</literal> trigger.
55+
</para>
56+
3857
<para>
3958
The <literal>ddl_command_start</literal> event occurs just before the
4059
execution of a <literal>CREATE</literal>, <literal>ALTER</literal>, <literal>DROP</literal>,
@@ -1300,4 +1319,79 @@ CREATE EVENT TRIGGER no_rewrite_allowed
13001319
</programlisting>
13011320
</para>
13021321
</sect1>
1322+
1323+
<sect1 id="event-trigger-database-login-example">
1324+
<title>A Database Login Event Trigger Example</title>
1325+
1326+
<para>
1327+
The event trigger on the <literal>login</literal> event can be
1328+
useful for logging user logins, for verifying the connection and
1329+
assigning roles according to current circumstances, or for session
1330+
data initialization. It is very important that any event trigger using
1331+
the <literal>login</literal> event checks whether or not the database is
1332+
in recovery before performing any writes. Writing to a standby server
1333+
will make it inaccessible.
1334+
</para>
1335+
1336+
<para>
1337+
The following example demonstrates these options.
1338+
<programlisting>
1339+
-- create test tables and roles
1340+
CREATE TABLE user_login_log (
1341+
"user" text,
1342+
"session_start" timestamp with time zone
1343+
);
1344+
CREATE ROLE day_worker;
1345+
CREATE ROLE night_worker;
1346+
1347+
-- the example trigger function
1348+
CREATE OR REPLACE FUNCTION init_session()
1349+
RETURNS event_trigger SECURITY DEFINER
1350+
LANGUAGE plpgsql AS
1351+
$$
1352+
DECLARE
1353+
hour integer = EXTRACT('hour' FROM current_time at time zone 'utc');
1354+
rec boolean;
1355+
BEGIN
1356+
-- 1. Forbid logging in between 2AM and 4AM.
1357+
IF hour BETWEEN 2 AND 4 THEN
1358+
RAISE EXCEPTION 'Login forbidden';
1359+
END IF;
1360+
1361+
-- The checks below cannot be performed on standby servers so
1362+
-- ensure the database is not in recovery before we perform any
1363+
-- operations.
1364+
SELECT pg_is_in_recovery() INTO rec;
1365+
IF rec THEN
1366+
RETURN;
1367+
END IF;
1368+
1369+
-- 2. Assign some roles. At daytime, grant the day_worker role, else the
1370+
-- night_worker role.
1371+
IF hour BETWEEN 8 AND 20 THEN
1372+
EXECUTE 'REVOKE night_worker FROM ' || quote_ident(session_user);
1373+
EXECUTE 'GRANT day_worker TO ' || quote_ident(session_user);
1374+
ELSE
1375+
EXECUTE 'REVOKE day_worker FROM ' || quote_ident(session_user);
1376+
EXECUTE 'GRANT night_worker TO ' || quote_ident(session_user);
1377+
END IF;
1378+
1379+
-- 3. Initialize user session data
1380+
CREATE TEMP TABLE session_storage (x float, y integer);
1381+
ALTER TABLE session_storage OWNER TO session_user;
1382+
1383+
-- 4. Log the connection time
1384+
INSERT INTO public.user_login_log VALUES (session_user, current_timestamp);
1385+
1386+
END;
1387+
$$;
1388+
1389+
-- trigger definition
1390+
CREATE EVENT TRIGGER init_session
1391+
ON login
1392+
EXECUTE FUNCTION init_session();
1393+
ALTER EVENT TRIGGER init_session ENABLE ALWAYS;
1394+
</programlisting>
1395+
</para>
1396+
</sect1>
13031397
</chapter>

‎src/backend/commands/dbcommands.c

+11-6
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ static void movedb(const char *dbname, const char *tblspcname);
116116
static void movedb_failure_callback(int code, Datum arg);
117117
static bool get_db_info(const char *name, LOCKMODE lockmode,
118118
Oid *dbIdP, Oid *ownerIdP,
119-
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
119+
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, bool *dbHasLoginEvtP,
120120
TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
121121
Oid *dbTablespace, char **dbCollate, char **dbCtype, char **dbIculocale,
122122
char **dbIcurules,
@@ -680,6 +680,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
680680
char src_locprovider = '\0';
681681
char *src_collversion = NULL;
682682
bool src_istemplate;
683+
bool src_hasloginevt;
683684
bool src_allowconn;
684685
TransactionId src_frozenxid = InvalidTransactionId;
685686
MultiXactId src_minmxid = InvalidMultiXactId;
@@ -968,7 +969,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
968969

969970
if (!get_db_info(dbtemplate, ShareLock,
970971
&src_dboid, &src_owner, &src_encoding,
971-
&src_istemplate, &src_allowconn,
972+
&src_istemplate, &src_allowconn, &src_hasloginevt,
972973
&src_frozenxid, &src_minmxid, &src_deftablespace,
973974
&src_collate, &src_ctype, &src_iculocale, &src_icurules, &src_locprovider,
974975
&src_collversion))
@@ -1375,6 +1376,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
13751376
new_record[Anum_pg_database_datlocprovider - 1] = CharGetDatum(dblocprovider);
13761377
new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(dbistemplate);
13771378
new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(dballowconnections);
1379+
new_record[Anum_pg_database_dathasloginevt - 1] = BoolGetDatum(src_hasloginevt);
13781380
new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
13791381
new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
13801382
new_record[Anum_pg_database_datminmxid - 1] = TransactionIdGetDatum(src_minmxid);
@@ -1603,7 +1605,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
16031605
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
16041606

16051607
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
1606-
&db_istemplate, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
1608+
&db_istemplate, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
16071609
{
16081610
if (!missing_ok)
16091611
{
@@ -1817,7 +1819,7 @@ RenameDatabase(const char *oldname, const char *newname)
18171819
*/
18181820
rel = table_open(DatabaseRelationId, RowExclusiveLock);
18191821

1820-
if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL,
1822+
if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
18211823
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
18221824
ereport(ERROR,
18231825
(errcode(ERRCODE_UNDEFINED_DATABASE),
@@ -1927,7 +1929,7 @@ movedb(const char *dbname, const char *tblspcname)
19271929
*/
19281930
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
19291931

1930-
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
1932+
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL, NULL,
19311933
NULL, NULL, NULL, NULL, &src_tblspcoid, NULL, NULL, NULL, NULL, NULL, NULL))
19321934
ereport(ERROR,
19331935
(errcode(ERRCODE_UNDEFINED_DATABASE),
@@ -2693,7 +2695,7 @@ pg_database_collation_actual_version(PG_FUNCTION_ARGS)
26932695
static bool
26942696
get_db_info(const char *name, LOCKMODE lockmode,
26952697
Oid *dbIdP, Oid *ownerIdP,
2696-
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
2698+
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, bool *dbHasLoginEvtP,
26972699
TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
26982700
Oid *dbTablespace, char **dbCollate, char **dbCtype, char **dbIculocale,
26992701
char **dbIcurules,
@@ -2778,6 +2780,9 @@ get_db_info(const char *name, LOCKMODE lockmode,
27782780
/* allowed as template? */
27792781
if (dbIsTemplateP)
27802782
*dbIsTemplateP = dbform->datistemplate;
2783+
/* Has on login event trigger? */
2784+
if (dbHasLoginEvtP)
2785+
*dbHasLoginEvtP = dbform->dathasloginevt;
27812786
/* allowing connections? */
27822787
if (dbAllowConnP)
27832788
*dbAllowConnP = dbform->datallowconn;

0 commit comments

Comments
 (0)