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