[Yugo Nagata <nagata@sraoss.co.jp>]

Hi,

Currently, PostgreSQL doen't have a hook triggered at session
start. Although we already have ClientAuthentication_hook,
this is triggered during authentication, so we can not
access the database. 

If we have a hook triggerd only once at session start, we may
do something useful on the session for certain database or user.

For example, one of our clients wanted such feature. He wanted
to handle encription for specific users, though I don't know
the detail.

The attached patch (session_start_hook.patch) implements such
hook. 

Another patch, session_start_sample.patch, is a very simple
example of this hook that changes work_mem values for sessions
of a specific database. 

I would appreciate hearing your opinion on this hook.

Regards,

-- 
Yugo Nagata <nagata@sraoss.co.jp>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachments:

  [text/x-diff] session_start_hook.patch (1.4K, ../20170720204733.40f2b7eb.nagata@sraoss.co.jp/2-session_start_hook.patch)
  download | inline diff:
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index b8d860e..7a1fa3b 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -160,6 +160,9 @@ static bool RecoveryConflictPending = false;
 static bool RecoveryConflictRetryable = true;
 static ProcSignalReason RecoveryConflictReason;
 
+/* Hook for plugins to get control at start of session */
+session_start_hook_type session_start_hook = NULL;
+
 /* ----------------------------------------------------------------
  *		decls for routines only used in this file
  * ----------------------------------------------------------------
@@ -3808,6 +3811,9 @@ PostgresMain(int argc, char *argv[],
 	if (!IsUnderPostmaster)
 		PgStartTime = GetCurrentTimestamp();
 
+	if (session_start_hook)
+		(*session_start_hook) (dbname, username);
+
 	/*
 	 * POSTGRES main processing loop begins here
 	 *
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h
index f8c535c..d349592 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -35,6 +35,11 @@ extern PGDLLIMPORT const char *debug_query_string;
 extern int	max_stack_depth;
 extern int	PostAuthDelay;
 
+/* Hook for plugins to get control at start of session */
+typedef void (*session_start_hook_type) (const char *dbname,
+										 const char *username);
+extern PGDLLIMPORT session_start_hook_type session_start_hook;
+
 /* GUC-configurable parameters */
 
 typedef enum

  [text/x-diff] session_start_sample.patch (1.9K, ../20170720204733.40f2b7eb.nagata@sraoss.co.jp/3-session_start_sample.patch)
  download | inline diff:
diff --git a/contrib/session_start/Makefile b/contrib/session_start/Makefile
new file mode 100644
index 0000000..f94355b
--- /dev/null
+++ b/contrib/session_start/Makefile
@@ -0,0 +1,15 @@
+# contrib/session_start/Makefile
+
+MODULES = session_start
+PGFILEDESC = "session_start - sample for session start hook"
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/session_start
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/session_start/session_start.c b/contrib/session_start/session_start.c
new file mode 100644
index 0000000..1792879
--- /dev/null
+++ b/contrib/session_start/session_start.c
@@ -0,0 +1,53 @@
+/* -------------------------------------------------------------------------
+ *
+ * session_start.c
+ *
+ * Copyright (c) 2010-2017, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ *		contrib/session_start/session_start.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "executor/spi.h"
+#include "tcop/tcopprot.h"
+
+PG_MODULE_MAGIC;
+
+void		_PG_init(void);
+
+/* Original Hook */
+static session_start_hook_type original_session_start_hook = NULL;
+
+/* sample hook function */
+static void
+sample_session_start_hook(const char *dbname, const char *username)
+{
+	CommandDest back;
+
+	if (original_session_start_hook)
+		original_session_start_hook(dbname, username);
+
+	if (!strcmp(dbname, "test"))
+	{
+		StartTransactionCommand();
+		SPI_connect();
+		SPI_exec("set work_mem to 10240", 1);
+		SPI_finish();
+		CommitTransactionCommand();
+	}
+}
+
+/*
+ * Module Load Callback
+ */
+void
+_PG_init(void)
+{
+	/* Install Hooks */
+
+	original_session_start_hook = session_start_hook;
+	session_start_hook = sample_session_start_hook;
+}