[HACKERS] proposal : backend startup hook / after logon trigger

[Tomas Vondra]

Hi,

I occasionally need to perform some action whenever a user connects, and
there's nothing like an "AFTER LOGON" trigger (available in some other
databases).

Is there any particular reason why there's not a "backend start hook",
executed right after a backend is initialized? I've tried a very simple
PoC (basically just a new hook definition, called from PostgresMain(),
see the after-logon-hook.diff (and a simple module that uses it is in
logon.c).

This obviously is not a complete patch or something, but is there a good
reason why this is a stupid idea?

Obviously this is a bit low-level approach, as most of the time the
triggers are implemented in a PL. But who says you can't call a PL from
the C module ...

Tomas

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
new file mode 100644
index 976a832..35f1926
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*************** int                     max_stack_depth = 100;
*** 104,110 ****
  /* wait N seconds to allow attach from a debugger */
  int                   PostAuthDelay = 0;
  
! 
  
  /* ----------------
   *            private variables
--- 104,110 ----
  /* wait N seconds to allow attach from a debugger */
  int                   PostAuthDelay = 0;
  
! backend_startup_hook_type backend_startup_hook = NULL;
  
  /* ----------------
   *            private variables
*************** PostgresMain(int argc, char *argv[], con
*** 3750,3755 ****
--- 3750,3769 ----
        if (!ignore_till_sync)
                send_ready_for_query = true;    /* initially, or after error */
  
+       if (backend_startup_hook != NULL)
+       {
+               PG_TRY();
+               {
+                       backend_startup_hook(MyProcPid, MyDatabaseId, dbname, 
username);
+               }
+               PG_CATCH();
+               {
+                       elog(FATAL, "Error calling after-logon trigger");
+                       proc_exit(0);
+               }
+               PG_END_TRY();
+       }
+ 
        /*
         * Non-error queries loop here.
         */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
new file mode 100644
index 9d19417..3971268
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
*************** extern bool is_authenticated_user_replic
*** 388,391 ****
--- 388,396 ----
  extern bool BackupInProgress(void);
  extern void CancelBackup(void);
  
+ typedef void (*backend_startup_hook_type) (pid_t pid, Oid databaseOid,
+                                            const char * dbname, const char * 
username);
+ 
+ extern backend_startup_hook_type backend_startup_hook;
+ 
  #endif   /* MISCADMIN_H */

#include <stdio.h>
#include <math.h>
#include <string.h>
#include <sys/time.h>
#include <unistd.h>
#include <limits.h>

#include "postgres.h"
#include "utils/array.h"
#include "utils/lsyscache.h"
#include "utils/numeric.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "catalog/pg_type.h"

#include "miscadmin.h"

static backend_startup_hook_type prev_backend_startup_hook = NULL;

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

void _PG_init(void);
void _PG_fini(void);

static
void my_startup_hook (pid_t pid, Oid databaseOid,
                      const char * dbname, const char * username);

/*
 * Module load callback
 */
void
_PG_init(void)
{

	prev_backend_startup_hook = backend_startup_hook;
	backend_startup_hook = &my_startup_hook;

}

void
_PG_fini(void)
{

	backend_startup_hook = prev_backend_startup_hook;

}



static
void my_startup_hook (pid_t pid, Oid databaseOid,
                      const char * dbname, const char * username) {
	if (! strcmp(dbname, "testdb")) {
		elog(ERROR, "startup hook is failing");
	} else {
		elog(NOTICE, "startup hook finished OK");
	}
}
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers