[PATCH] Add permit_unlogged_tables GUC to control unlogged table creation.

Started by harinath kanchu10 days ago3 messages
#1harinath kanchu
kanchuharinath@gmail.com
1 attachment(s)

Hello,
This patch implements a new GUC parameter "permit_unlogged_tables" which
allows admins to disable creation of unlogged tables or altering the
existing tables to be unlogged.

This is useful in environments with strict compliance requirements as well
as multi-tenant environments where the provider needs to guarantee
durability of customer data.

The default is "true", which is to say that behavior would remain unchanged.

Implementation
- Boolean GUC with PGC_SIGHUP context (reloadable without restart)
- Check in "DefineRelation()" to block "CREATE UNLOGGED TABLE" commands.
- Check in "ATPrepCmd()" to block "ALTER TABLE SET UNLOGGED" commands.
- "ALTER TABLE SET LOGGED" commands remain unaffected and should always
work.

Attached the patch for review. Happy to make adjustments based on feedback.

Thanks !

Best,
Harinath

Attachments:

v1-0001-Add-permit_unlogged_tables-GUC-to-control-unlogge.patchapplication/octet-stream; name=v1-0001-Add-permit_unlogged_tables-GUC-to-control-unlogge.patchDownload
From 8e55cf697fb2ea0c3681052bf59615d3df97f37d Mon Sep 17 00:00:00 2001
From: Harinath <kanchuharinath@gmail.com>
Date: Tue, 16 Dec 2025 13:01:16 -0800
Subject: [PATCH v1] Add permit_unlogged_tables GUC to control unlogged table
 creation

Introduces a boolean GUC parameter (default: true) that allows administrators
to disable creation of unlogged tables and conversion of existing tables to
unlogged state. The parameter uses PGC_SIGHUP context allowing configuration
reload without server restart.

When set to false, CREATE UNLOGGED TABLE and ALTER TABLE SET UNLOGGED
operations are rejected with appropriate error messages. ALTER TABLE SET
LOGGED operations remain unaffected.

Includes comprehensive documentation, regression tests, and TAP tests
to verify configuration reload behavior and error handling.

This enables policy enforcement for data safety and compliance requirements
while maintaining backward compatibility (default allows unlogged tables).
---
 doc/src/sgml/config.sgml                      |  22 ++
 src/backend/commands/tablecmds.c              |  26 +++
 src/backend/utils/misc/guc_parameters.dat     |   9 +
 src/backend/utils/misc/guc_tables.c           |   2 +
 src/include/utils/guc.h                       |   2 +
 src/include/utils/guc_hooks.h                 |   1 +
 src/test/modules/Makefile                     |   1 +
 src/test/modules/meson.build                  |   1 +
 .../test_permit_unlogged_tables/.gitignore    |   4 +
 .../test_permit_unlogged_tables/Makefile      |  22 ++
 .../expected/test_permit_unlogged_tables.out  |  23 ++
 .../test_permit_unlogged_tables/meson.build   |  18 ++
 .../sql/test_permit_unlogged_tables.sql       |  24 ++
 .../t/001_permit_unlogged_tables.pl           | 214 ++++++++++++++++++
 .../test_permit_unlogged_tables/test.conf     |   1 +
 15 files changed, 370 insertions(+)
 create mode 100644 src/test/modules/test_permit_unlogged_tables/.gitignore
 create mode 100644 src/test/modules/test_permit_unlogged_tables/Makefile
 create mode 100644 src/test/modules/test_permit_unlogged_tables/expected/test_permit_unlogged_tables.out
 create mode 100644 src/test/modules/test_permit_unlogged_tables/meson.build
 create mode 100644 src/test/modules/test_permit_unlogged_tables/sql/test_permit_unlogged_tables.sql
 create mode 100755 src/test/modules/test_permit_unlogged_tables/t/001_permit_unlogged_tables.pl
 create mode 100644 src/test/modules/test_permit_unlogged_tables/test.conf

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 601aa3afb8e..fda6d25e60f 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10525,6 +10525,28 @@ SET XML OPTION { DOCUMENT | CONTENT };
      </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-permit-unlogged-tables" xreflabel="permit_unlogged_tables">
+      <term><varname>permit_unlogged_tables</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>permit_unlogged_tables</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Controls whether unlogged tables can be created. When this parameter is
+        set to <literal>true</literal> (the default), unlogged tables are permitted.
+        When set to <literal>false</literal>, attempts to create unlogged
+        tables or alter existing tables to be unlogged will fail with an error.
+       </para>
+       <para>
+        This parameter can only be set in the <filename>postgresql.conf</filename>
+        file or on the server command line. Changes to this parameter can be
+        applied without restarting the server by reloading the configuration
+        (using <literal>pg_ctl reload</literal> or <function>pg_reload_conf()</function>).
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
     </sect2>
      <sect2 id="runtime-config-client-format">
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..a9c7a98a0bf 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -109,6 +109,24 @@
 #include "utils/timestamp.h"
 #include "utils/typcache.h"
 #include "utils/usercontext.h"
+#include "utils/guc.h"
+
+/*
+ *	User-tweakable parameters
+ */
+
+/*
+ * GUC hook functions for permit_unlogged_tables
+ */
+void
+assign_permit_unlogged_tables(bool newval, void *extra)
+{
+	permit_unlogged_tables = newval;
+
+	/* Log the change with user context */
+	elog(LOG, "permit_unlogged_tables changed to %s",
+		 newval ? "on" : "off");
+}
 
 /*
  * ON COMMIT action list
@@ -791,6 +809,10 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	LOCKMODE	parentLockmode;
 	Oid			accessMethodId = InvalidOid;
 
+	if (stmt->relation->relpersistence == RELPERSISTENCE_UNLOGGED && !permit_unlogged_tables)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				errmsg("Unlogged tables are not permitted."));
 	/*
 	 * Truncate relname to appropriate length (probably a waste of time, as
 	 * parser should have done this already).
@@ -5149,6 +5171,10 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 			break;
 		case AT_SetLogged:		/* SET LOGGED */
 		case AT_SetUnLogged:	/* SET UNLOGGED */
+			if (cmd->subtype == AT_SetUnLogged && !permit_unlogged_tables)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("Unlogged tables are not permitted.")));
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
 			if (tab->chgPersistence)
 				ereport(ERROR,
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 7c60b125564..d85bf47470d 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2248,6 +2248,15 @@
   options => 'password_encryption_options',
 },
 
+{ name => 'permit_unlogged_tables', type => 'bool', context => 'PGC_SIGHUP', group => 'UNGROUPED',
+  short_desc => 'Enable unlogged tables.',
+  long_desc => 'When enabled, permits the creation of unlogged tables. Changes require configuration reload.',
+  flags => 'GUC_NOT_IN_SAMPLE',
+  variable => 'permit_unlogged_tables',
+  boot_val => 'true',
+  assign_hook => 'assign_permit_unlogged_tables',
+},
+
 { name => 'plan_cache_mode', type => 'enum', context => 'PGC_USERSET', group => 'QUERY_TUNING_OTHER',
   short_desc => 'Controls the planner\'s selection of custom or generic plan.',
   long_desc => 'Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better.  This can be set to override the default behavior.',
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 73ff6ad0a32..3a35a65b52b 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -560,6 +560,8 @@ char	   *external_pid_file;
 
 char	   *application_name;
 
+bool		permit_unlogged_tables = true;
+
 int			tcp_keepalives_idle;
 int			tcp_keepalives_interval;
 int			tcp_keepalives_count;
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index bf39878c43e..87cc2a33d9a 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -316,6 +316,8 @@ extern PGDLLIMPORT char *external_pid_file;
 
 extern PGDLLIMPORT char *application_name;
 
+extern PGDLLIMPORT bool permit_unlogged_tables;
+
 extern PGDLLIMPORT int tcp_keepalives_idle;
 extern PGDLLIMPORT int tcp_keepalives_interval;
 extern PGDLLIMPORT int tcp_keepalives_count;
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index f723668da9e..40bdc73b4ed 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -93,6 +93,7 @@ extern bool check_multixact_member_buffers(int *newval, void **extra,
 extern bool check_multixact_offset_buffers(int *newval, void **extra,
 										   GucSource source);
 extern bool check_notify_buffers(int *newval, void **extra, GucSource source);
+extern void assign_permit_unlogged_tables(bool newval, void *extra);
 extern bool check_primary_slot_name(char **newval, void **extra,
 									GucSource source);
 extern bool check_random_seed(double *newval, void **extra, GucSource source);
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 4c6d56d97d8..f03a906da0a 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -47,6 +47,7 @@ SUBDIRS = \
 		  test_shm_mq \
 		  test_slru \
 		  test_tidstore \
+		  test_permit_unlogged_tables \
 		  unsafe_tests \
 		  worker_spi \
 		  xid_wraparound
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 1b31c5b98d6..4f64c4eac41 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -47,6 +47,7 @@ subdir('test_rls_hooks')
 subdir('test_shm_mq')
 subdir('test_slru')
 subdir('test_tidstore')
+subdir('test_permit_unlogged_tables')
 subdir('typcache')
 subdir('unsafe_tests')
 subdir('worker_spi')
diff --git a/src/test/modules/test_permit_unlogged_tables/.gitignore b/src/test/modules/test_permit_unlogged_tables/.gitignore
new file mode 100644
index 00000000000..5dcb3ff9723
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/test_permit_unlogged_tables/Makefile b/src/test/modules/test_permit_unlogged_tables/Makefile
new file mode 100644
index 00000000000..817b2c8c745
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/Makefile
@@ -0,0 +1,22 @@
+# src/test/modules/test_permit_unlogged_tables/Makefile
+
+MODULE = test_permit_unlogged_tables
+PGFILEDESC = "test_permit_unlogged_tables -- check permit_unlogged_tables setting is working fine"
+
+REGRESS = test_permit_unlogged_tables
+EXTRA_REGRESS_OPTS=--temp-config=$(top_builddir)/src/test/modules/test_permit_unlogged_tables/test.conf
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_permit_unlogged_tables
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+# we are setting DONT_INCLUDE_TEMP_CONFIG flag to not to include conf from contrib-global.mk
+DONT_INCLUDE_TEMP_CONFIG = 1
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_permit_unlogged_tables/expected/test_permit_unlogged_tables.out b/src/test/modules/test_permit_unlogged_tables/expected/test_permit_unlogged_tables.out
new file mode 100644
index 00000000000..4d4b3254363
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/expected/test_permit_unlogged_tables.out
@@ -0,0 +1,23 @@
+-- this should work
+CREATE TABLE test1 (
+    did     integer,
+    name    varchar(40),
+    PRIMARY KEY(did)
+);
+-- this should fail
+CREATE UNLOGGED TABLE test2 (
+    did     integer,
+    name    varchar(40),
+    PRIMARY KEY(did)
+);
+ERROR:  Unlogged tables are not permitted.
+-- this should fail
+ALTER TABLE test1 SET UNLOGGED;
+ERROR:  Unlogged tables are not permitted.
+-- this should fail
+ALTER TABLE test1 ADD COLUMN new_col integer, SET UNLOGGED;
+ERROR:  Unlogged tables are not permitted.
+-- this should work
+ALTER TABLE test1 ADD COLUMN new_col integer;
+-- this should work
+ALTER TABLE test1 SET LOGGED;
diff --git a/src/test/modules/test_permit_unlogged_tables/meson.build b/src/test/modules/test_permit_unlogged_tables/meson.build
new file mode 100644
index 00000000000..e2e6707a5d7
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/meson.build
@@ -0,0 +1,18 @@
+# Copyright (c) 2022-2025, PostgreSQL Global Development Group
+
+tests += {
+  'name': 'test_permit_unlogged_tables',
+  'sd': meson.current_source_dir(),
+  'bd': meson.current_build_dir(),
+  'regress': {
+    'sql': [
+      'test_permit_unlogged_tables',
+    ],
+    'regress_args': ['--temp-config', '@0@/test.conf'.format(meson.current_source_dir())],
+  },
+  'tap': {
+    'tests': [
+      't/001_permit_unlogged_tables.pl',
+    ],
+  },
+}
diff --git a/src/test/modules/test_permit_unlogged_tables/sql/test_permit_unlogged_tables.sql b/src/test/modules/test_permit_unlogged_tables/sql/test_permit_unlogged_tables.sql
new file mode 100644
index 00000000000..6c9ea98e576
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/sql/test_permit_unlogged_tables.sql
@@ -0,0 +1,24 @@
+-- this should work
+CREATE TABLE test1 (
+    did     integer,
+    name    varchar(40),
+    PRIMARY KEY(did)
+);
+-- this should fail
+CREATE UNLOGGED TABLE test2 (
+    did     integer,
+    name    varchar(40),
+    PRIMARY KEY(did)
+);
+
+-- this should fail
+ALTER TABLE test1 SET UNLOGGED;
+
+-- this should fail
+ALTER TABLE test1 ADD COLUMN new_col integer, SET UNLOGGED;
+
+-- this should work
+ALTER TABLE test1 ADD COLUMN new_col integer;
+
+-- this should work
+ALTER TABLE test1 SET LOGGED;
diff --git a/src/test/modules/test_permit_unlogged_tables/t/001_permit_unlogged_tables.pl b/src/test/modules/test_permit_unlogged_tables/t/001_permit_unlogged_tables.pl
new file mode 100755
index 00000000000..be1773f1bb3
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/t/001_permit_unlogged_tables.pl
@@ -0,0 +1,214 @@
+#!/usr/bin/perl
+
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Test for the permit_unlogged_tables GUC parameter
+#
+# This test verifies that:
+# 1. Unlogged tables can be created when permit_unlogged_tables=true (default)
+# 2. Unlogged tables are rejected when permit_unlogged_tables=false
+# 3. Configuration changes can be applied via reload without restart
+# 4. ALTER TABLE SET UNLOGGED is also controlled by the parameter
+# 5. ALTER TABLE SET LOGGED works regardless of the parameter
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a test cluster with permit_unlogged_tables enabled by default (true)
+my $node = PostgreSQL::Test::Cluster->new('permit_unlogged_test');
+$node->init();
+$node->start();
+
+# Test 1: Verify default behavior - unlogged tables should be allowed by default
+note("Testing default behavior - unlogged tables should be allowed");
+
+# Verify the default value is true
+my $result = $node->safe_psql('postgres',
+    "SHOW permit_unlogged_tables;");
+is($result, 'on', 'permit_unlogged_tables defaults to on');
+
+# Create an unlogged table - should succeed
+$node->safe_psql('postgres',
+    "CREATE UNLOGGED TABLE test_unlogged1 (id int, data text);");
+
+# Verify the table was created as unlogged
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_unlogged1';");
+is($result, 'u', 'table created as unlogged');
+
+# Insert some data
+$node->safe_psql('postgres',
+    "INSERT INTO test_unlogged1 VALUES (1, 'test data');");
+
+# Create a regular table for later testing
+$node->safe_psql('postgres',
+    "CREATE TABLE test_regular1 (id int, data text);");
+
+# Test ALTER TABLE SET UNLOGGED on regular table - should succeed
+$node->safe_psql('postgres',
+    "ALTER TABLE test_regular1 SET UNLOGGED;");
+
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_regular1';");
+is($result, 'u', 'regular table converted to unlogged');
+
+note("Initial tests with permit_unlogged_tables=on completed successfully");
+
+# Test 2: Disable unlogged tables via configuration change
+note("Testing configuration change to disable unlogged tables");
+
+# Update postgresql.conf to disable unlogged tables
+$node->append_conf('postgresql.conf', "permit_unlogged_tables = false");
+
+# Reload configuration (SIGHUP) - should not require restart
+$node->reload();
+
+# Give the reload a moment to take effect
+sleep(1);
+
+# Verify the setting changed
+$result = $node->safe_psql('postgres',
+    "SHOW permit_unlogged_tables;");
+is($result, 'off', 'permit_unlogged_tables changed to off after reload');
+
+# Test 3: Verify unlogged table creation fails when disabled
+note("Testing unlogged table creation fails when permit_unlogged_tables=off");
+
+# Try to create an unlogged table - should fail
+my ($ret, $stdout, $stderr) = $node->psql('postgres',
+    "CREATE UNLOGGED TABLE test_unlogged2 (id int, data text);");
+
+isnt($ret, 0, 'CREATE UNLOGGED TABLE fails when permit_unlogged_tables=off');
+like($stderr, qr/Unlogged tables are not permitted/,
+    'correct error message for CREATE UNLOGGED TABLE');
+
+# Test 4: Verify ALTER TABLE SET UNLOGGED fails when disabled
+note("Testing ALTER TABLE SET UNLOGGED fails when permit_unlogged_tables=off");
+
+# Create a regular table first
+$node->safe_psql('postgres',
+    "CREATE TABLE test_regular2 (id int, data text);");
+
+# Try to convert it to unlogged - should fail
+($ret, $stdout, $stderr) = $node->psql('postgres',
+    "ALTER TABLE test_regular2 SET UNLOGGED;");
+
+isnt($ret, 0, 'ALTER TABLE SET UNLOGGED fails when permit_unlogged_tables=off');
+like($stderr, qr/Unlogged tables are not permitted/,
+    'correct error message for ALTER TABLE SET UNLOGGED');
+
+# Verify the table is still regular
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_regular2';");
+is($result, 'p', 'table remains regular after failed ALTER SET UNLOGGED');
+
+# Test 5: Verify ALTER TABLE SET LOGGED still works
+note("Testing ALTER TABLE SET LOGGED works regardless of permit_unlogged_tables setting");
+
+# Convert the existing unlogged table to logged - should always work
+$node->safe_psql('postgres',
+    "ALTER TABLE test_regular1 SET LOGGED;");
+
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_regular1';");
+is($result, 'p', 'unlogged table converted to logged');
+
+# Test 6: Verify existing unlogged tables continue to work
+note("Testing existing unlogged tables continue to work when permit_unlogged_tables=off");
+
+# The existing unlogged table should still be accessible
+$result = $node->safe_psql('postgres',
+    "SELECT COUNT(*) FROM test_unlogged1;");
+is($result, '1', 'existing unlogged table is still accessible');
+
+# Should be able to insert/update/delete
+$node->safe_psql('postgres',
+    "INSERT INTO test_unlogged1 VALUES (2, 'more test data');");
+
+$result = $node->safe_psql('postgres',
+    "SELECT COUNT(*) FROM test_unlogged1;");
+is($result, '2', 'can insert into existing unlogged table');
+
+# Test 7: Re-enable unlogged tables and verify they work again
+note("Testing re-enabling unlogged tables");
+
+# Update postgresql.conf to re-enable unlogged tables
+$node->append_conf('postgresql.conf', "permit_unlogged_tables = true");
+
+# Reload configuration
+$node->reload();
+
+# Give the reload a moment to take effect
+sleep(1);
+
+# Verify the setting changed back
+$result = $node->safe_psql('postgres',
+    "SHOW permit_unlogged_tables;");
+is($result, 'on', 'permit_unlogged_tables re-enabled');
+
+# Now unlogged table creation should work again
+$node->safe_psql('postgres',
+    "CREATE UNLOGGED TABLE test_unlogged3 (id int, data text);");
+
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_unlogged3';");
+is($result, 'u', 'unlogged table creation works again after re-enabling');
+
+# Test ALTER TABLE SET UNLOGGED should work again too
+$node->safe_psql('postgres',
+    "ALTER TABLE test_regular2 SET UNLOGGED;");
+
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_regular2';");
+is($result, 'u', 'ALTER TABLE SET UNLOGGED works again after re-enabling');
+
+# Test 8: Test parameter setting via SQL (should be rejected)
+note("Testing permit_unlogged_tables cannot be set via SQL session");
+
+# Try to set via SET command - should fail since it's PGC_SIGHUP
+($ret, $stdout, $stderr) = $node->psql('postgres',
+    "SET permit_unlogged_tables = false;");
+
+isnt($ret, 0, 'SET permit_unlogged_tables fails (PGC_SIGHUP)');
+like($stderr, qr/parameter .* cannot be changed/,
+    'correct error message for SET attempt');
+
+# Test 9: Test with different table types
+note("Testing with different table types");
+
+# Test with partitioned tables
+$node->safe_psql('postgres',
+    "CREATE TABLE test_partitioned (id int, data text) PARTITION BY RANGE (id);");
+
+# Try to create unlogged partition when enabled - should work
+$node->safe_psql('postgres',
+    "CREATE UNLOGGED TABLE test_partition1 PARTITION OF test_partitioned FOR VALUES FROM (1) TO (100);");
+
+$result = $node->safe_psql('postgres',
+    "SELECT relpersistence FROM pg_class WHERE relname = 'test_partition1';");
+is($result, 'u', 'unlogged partition created successfully');
+
+# Disable unlogged tables again
+$node->append_conf('postgresql.conf', "permit_unlogged_tables = false");
+$node->reload();
+sleep(1);
+
+# Try to create another unlogged partition - should fail
+($ret, $stdout, $stderr) = $node->psql('postgres',
+    "CREATE UNLOGGED TABLE test_partition2 PARTITION OF test_partitioned FOR VALUES FROM (100) TO (200);");
+
+isnt($ret, 0, 'CREATE UNLOGGED partition fails when permit_unlogged_tables=off');
+
+# Cleanup
+note("Cleaning up test objects");
+
+$node->safe_psql('postgres', "DROP TABLE IF EXISTS test_unlogged1, test_unlogged3, test_regular1, test_regular2, test_partition1, test_partitioned CASCADE;");
+
+# Stop the node
+$node->stop();
+
+done_testing();
diff --git a/src/test/modules/test_permit_unlogged_tables/test.conf b/src/test/modules/test_permit_unlogged_tables/test.conf
new file mode 100644
index 00000000000..5bde067c1c8
--- /dev/null
+++ b/src/test/modules/test_permit_unlogged_tables/test.conf
@@ -0,0 +1 @@
+permit_unlogged_tables = false
-- 
2.50.1 (Git-155)

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: harinath kanchu (#1)
Re: [PATCH] Add permit_unlogged_tables GUC to control unlogged table creation.

On Tue, Jan 13, 2026 at 1:48 PM harinath kanchu <kanchuharinath@gmail.com>
wrote:

This patch implements a new GUC parameter "permit_unlogged_tables" which
allows admins to disable creation of unlogged tables or altering the
existing tables to be unlogged.

I dislike the premise of this option. It doesn't seem like something that
should be evaluated on a cluster/database scope.

David J.

#3Greg Sabino Mullane
greg@turnstep.com
In reply to: David G. Johnston (#2)
Re: [PATCH] Add permit_unlogged_tables GUC to control unlogged table creation.

On Tue, Jan 13, 2026 at 4:14 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

I dislike the premise of this option. It doesn't seem like something that
should be evaluated on a cluster/database scope.

Indeed. OP, we already have a mechanism to support this: event triggers.
Quick example:

create or replace function unlogged_be_gone() returns event_trigger
language plpgsql as $$
declare myrec record;
begin
for myrec in select * from pg_event_trigger_ddl_commands() loop
if myrec.command_tag = 'CREATE TABLE' OR myrec.command_tag = 'ALTER
TABLE' then
perform 1 from pg_class where oid = myrec.objid and relpersistence =
'u';
if found then
raise 'Tables may not be created or changed to unlogged!';
end if;
end if;
end loop;
end
$$;

create event trigger etest on ddl_command_end execute function
unlogged_be_gone();

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support