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();
{
