As I have published on
https://abdulyadi.wordpress.com/2019/12/26/reinforce-data-validation-prevent-direct-table-modification/,
the patch is to have "private_modify" option in table creation. For example:
CREATE TABLE mytable (id integer) WITH (private_modify=true);

Having the option set, even superuser can not insert/update/delete the
table outside SQL or SPI-based function where complex data validation takes
place.

The patch has been passed all regression test provided in Postgresql source
code (src/test/regression): make check, make installcheck, make
installcheck-parallel, make checkworld, make install-checkworld.

Regards,
Abdul Yadi
Only in .: pgsqlprivate.patch
diff -ur ../postgresql-12.1/src/backend/access/common/reloptions.c ./src/backend/access/common/reloptions.c
--- ../postgresql-12.1/src/backend/access/common/reloptions.c	2019-11-12 05:03:10.000000000 +0700
+++ ./src/backend/access/common/reloptions.c	2019-12-28 10:09:03.818458584 +0700
@@ -158,6 +158,17 @@
 		},
 		true
 	},
+
+	{
+		{
+			"private_modify",
+			"Tuples can only be inserted, updated or deleted from within function",
+			RELOPT_KIND_HEAP,
+			ShareUpdateExclusiveLock
+		},
+		false
+	},
+
 	/* list terminator */
 	{{NULL}}
 };
@@ -1419,7 +1430,9 @@
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"private_modify", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, private_modify)}
 	};
 
 	options = parseRelOptions(reloptions, validate, kind, &numoptions);
diff -ur ../postgresql-12.1/src/backend/executor/functions.c ./src/backend/executor/functions.c
--- ../postgresql-12.1/src/backend/executor/functions.c	2019-11-12 05:03:10.000000000 +0700
+++ ./src/backend/executor/functions.c	2019-12-28 10:12:15.277641899 +0700
@@ -791,6 +791,8 @@
 	/* Caller should have ensured a suitable snapshot is active */
 	Assert(ActiveSnapshotSet());
 
+	GetActiveSnapshot()->insideFunction = SNAPSHOTFUNCTION_SQL;
+
 	/*
 	 * If this query produces the function result, send its output to the
 	 * tuplestore; else discard any output.
diff -ur ../postgresql-12.1/src/backend/executor/nodeModifyTable.c ./src/backend/executor/nodeModifyTable.c
--- ../postgresql-12.1/src/backend/executor/nodeModifyTable.c	2019-11-12 05:03:10.000000000 +0700
+++ ./src/backend/executor/nodeModifyTable.c	2019-12-28 10:05:23.574949769 +0700
@@ -56,7 +56,7 @@
 #include "utils/datum.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
-
+#include "utils/snapmgr.h"
 
 static bool ExecOnConflictUpdate(ModifyTableState *mtstate,
 								 ResultRelInfo *resultRelInfo,
@@ -2275,6 +2275,9 @@
 	int			i;
 	Relation	rel;
 	bool		update_tuple_routing_needed = node->partColsUpdated;
+	char       *replica_role;
+	SnapshotFunctionType snapshot_functiontype = SNAPSHOTFUNCTION_NONE;
+	bool private_modify = false;
 
 	/* check for unsupported flags */
 	Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -2389,6 +2392,22 @@
 	/* Get the target relation */
 	rel = (getTargetResultRelInfo(mtstate))->ri_RelationDesc;
 
+	/* If private_modify table option is set then raise error
+	 * whenever insideFunction is other than SNAPSHOTFUNCTION_SQL and SNAPSHOTFUNCTION_SPI.
+	 * Relax restriction if session_replication_role is 'replica'.
+	 */
+	replica_role = GetConfigOptionByName("session_replication_role", NULL, true);
+	if( replica_role==NULL || strcasecmp("replica", replica_role) ) {
+		if( rel->rd_options!=NULL )
+			private_modify = ((StdRdOptions *) rel->rd_options)->private_modify;
+
+		if( private_modify ) {
+			snapshot_functiontype = ActiveSnapshotSet() ? GetActiveSnapshot()->insideFunction : SNAPSHOTFUNCTION_NONE;
+			if( snapshot_functiontype != SNAPSHOTFUNCTION_SQL && snapshot_functiontype != SNAPSHOTFUNCTION_SPI )
+				elog(ERROR, "do not modify table with \"private_modify\" option outside SQL, PLPGSQL or other SPI-based function");
+		}
+	}
+
 	/*
 	 * If it's not a partitioned table after all, UPDATE tuple routing should
 	 * not be attempted.
diff -ur ../postgresql-12.1/src/backend/executor/spi.c ./src/backend/executor/spi.c
--- ../postgresql-12.1/src/backend/executor/spi.c	2019-11-12 05:03:10.000000000 +0700
+++ ./src/backend/executor/spi.c	2019-12-28 09:51:47.818664369 +0700
@@ -2109,6 +2109,9 @@
 	ErrorContextCallback spierrcontext;
 	CachedPlan *cplan = NULL;
 	ListCell   *lc1;
+	SnapshotFunctionType current_snapshot_functiontype = SNAPSHOTFUNCTION_NONE;
+	Snapshot transaction_snapshot = NULL;
+	SnapshotFunctionType transaction_snapshot_functiontype = SNAPSHOTFUNCTION_NONE;
 
 	/*
 	 * Setup error traceback support for ereport()
@@ -2223,7 +2226,18 @@
 		{
 			if (pushed_active_snap)
 				PopActiveSnapshot();
+			
+			/* get current snapshot insideFunction before pushed down */
+			current_snapshot_functiontype = ActiveSnapshotSet() ? GetActiveSnapshot()->insideFunction : SNAPSHOTFUNCTION_NONE;
+
 			PushActiveSnapshot(GetTransactionSnapshot());
+
+			/* backup transaction snapshot insideFunction before changed for restoration */
+			transaction_snapshot = GetActiveSnapshot();
+			transaction_snapshot_functiontype = transaction_snapshot->insideFunction;
+			/* copy insideFunction */
+			transaction_snapshot->insideFunction = current_snapshot_functiontype;
+
 			pushed_active_snap = true;
 		}
 
@@ -2405,6 +2419,9 @@
 	}
 
 fail:
+	/* restore transaction snapshot insideFunction */
+	if (pushed_active_snap && transaction_snapshot!=NULL)
+		transaction_snapshot->insideFunction = transaction_snapshot_functiontype;
 
 	/* Pop the snapshot off the stack if we pushed one */
 	if (pushed_active_snap)
@@ -2516,6 +2533,12 @@
 	else
 		eflags = EXEC_FLAG_SKIP_TRIGGERS;
 
+	/* Set insideFunction to SNAPSHOTFUNCTION_SPI only if it was SNAPSHOTFUNCTION_NONE.
+	 * Do not overwrite SNAPSHOTFUNCTION_ANONYMOUS_PLPGSQL set in anonymous block function call
+	 */
+	if( ActiveSnapshotSet() && GetActiveSnapshot()->insideFunction == SNAPSHOTFUNCTION_NONE )
+		GetActiveSnapshot()->insideFunction = SNAPSHOTFUNCTION_SPI;
+
 	ExecutorStart(queryDesc, eflags);
 
 	ExecutorRun(queryDesc, ForwardScanDirection, tcount, true);
diff -ur ../postgresql-12.1/src/include/utils/rel.h ./src/include/utils/rel.h
--- ../postgresql-12.1/src/include/utils/rel.h	2019-11-12 05:03:10.000000000 +0700
+++ ./src/include/utils/rel.h	2019-12-28 09:07:55.506678211 +0700
@@ -273,6 +273,7 @@
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool        private_modify; /*insert, update, delete tuples from within SQL or SPI-based function only*/
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
diff -ur ../postgresql-12.1/src/include/utils/snapshot.h ./src/include/utils/snapshot.h
--- ../postgresql-12.1/src/include/utils/snapshot.h	2019-11-12 05:03:10.000000000 +0700
+++ ./src/include/utils/snapshot.h	2019-12-28 09:22:09.652461461 +0700
@@ -118,6 +118,16 @@
 	SNAPSHOT_NON_VACUUMABLE
 } SnapshotType;
 
+/*
+ * Type of function surrounding tuple modification (INSERT/UPDATE/DELETE)
+ */
+typedef enum SnapshotFunctionType {
+	SNAPSHOTFUNCTION_NONE = 0, /* tuple modification outside function */
+	SNAPSHOTFUNCTION_SQL, /* tuple modification within SQL function */
+	SNAPSHOTFUNCTION_SPI, /* tuple modification within SPI-based function */
+	SNAPSHOTFUNCTION_ANONYMOUS_PLPGSQL /* tuple modified within anonymous block function */
+} SnapshotFunctionType;
+
 typedef struct SnapshotData *Snapshot;
 
 #define InvalidSnapshot		((Snapshot) NULL)
@@ -201,6 +211,8 @@
 
 	TimestampTz whenTaken;		/* timestamp when snapshot was taken */
 	XLogRecPtr	lsn;			/* position in the WAL stream when taken */
+
+	SnapshotFunctionType    insideFunction; /* function type surrounding tuple modification (INSERT/UPDATE/DELETE) */
 } SnapshotData;
 
 #endif							/* SNAPSHOT_H */
diff -ur ../postgresql-12.1/src/pl/plpgsql/src/pl_handler.c ./src/pl/plpgsql/src/pl_handler.c
--- ../postgresql-12.1/src/pl/plpgsql/src/pl_handler.c	2019-11-12 05:03:10.000000000 +0700
+++ ./src/pl/plpgsql/src/pl_handler.c	2019-12-28 10:11:31.502141396 +0700
@@ -28,6 +28,7 @@
 
 #include "plpgsql.h"
 
+#include "utils/snapmgr.h"
 
 static bool plpgsql_extra_checks_check_hook(char **newvalue, void **extra, GucSource source);
 static void plpgsql_extra_warnings_assign_hook(const char *newvalue, void *extra);
@@ -333,6 +334,9 @@
 	/* Create a private EState for simple-expression execution */
 	simple_eval_estate = CreateExecutorState();
 
+	if( ActiveSnapshotSet() )
+		GetActiveSnapshot()->insideFunction = SNAPSHOTFUNCTION_ANONYMOUS_PLPGSQL;
+
 	/* And run the function */
 	PG_TRY();
 	{

Reply via email to