public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v4 1/3] Move the code to restore files via the shell to a separate file.
7+ messages / 4 participants
[nested] [flat]
* [PATCH v4 1/3] Move the code to restore files via the shell to a separate file.
@ 2022-12-24 00:35 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Nathan Bossart @ 2022-12-24 00:35 UTC (permalink / raw)
This is preparatory work for allowing more extensibility in this
area.
---
src/backend/access/transam/Makefile | 1 +
src/backend/access/transam/meson.build | 1 +
src/backend/access/transam/shell_restore.c | 194 +++++++++++++++++++++
src/backend/access/transam/xlog.c | 44 ++++-
src/backend/access/transam/xlogarchive.c | 158 +----------------
src/include/access/xlogarchive.h | 7 +-
6 files changed, 240 insertions(+), 165 deletions(-)
create mode 100644 src/backend/access/transam/shell_restore.c
diff --git a/src/backend/access/transam/Makefile b/src/backend/access/transam/Makefile
index 661c55a9db..099c315d03 100644
--- a/src/backend/access/transam/Makefile
+++ b/src/backend/access/transam/Makefile
@@ -19,6 +19,7 @@ OBJS = \
multixact.o \
parallel.o \
rmgr.o \
+ shell_restore.o \
slru.o \
subtrans.o \
timeline.o \
diff --git a/src/backend/access/transam/meson.build b/src/backend/access/transam/meson.build
index 8920c1bfce..3031c2f6cf 100644
--- a/src/backend/access/transam/meson.build
+++ b/src/backend/access/transam/meson.build
@@ -7,6 +7,7 @@ backend_sources += files(
'multixact.c',
'parallel.c',
'rmgr.c',
+ 'shell_restore.c',
'slru.c',
'subtrans.c',
'timeline.c',
diff --git a/src/backend/access/transam/shell_restore.c b/src/backend/access/transam/shell_restore.c
new file mode 100644
index 0000000000..3ddcabd969
--- /dev/null
+++ b/src/backend/access/transam/shell_restore.c
@@ -0,0 +1,194 @@
+/*-------------------------------------------------------------------------
+ *
+ * shell_restore.c
+ *
+ * These recovery functions use a user-specified shell command (e.g., the
+ * restore_command GUC).
+ *
+ * Copyright (c) 2022, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/access/transam/shell_restore.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <signal.h>
+
+#include "access/xlogarchive.h"
+#include "access/xlogrecovery.h"
+#include "common/archive.h"
+#include "storage/ipc.h"
+#include "utils/wait_event.h"
+
+static void ExecuteRecoveryCommand(const char *command,
+ const char *commandName, bool failOnSignal,
+ uint32 wait_event_info,
+ const char *lastRestartPointFileName);
+
+bool
+shell_restore(const char *file, const char *path,
+ const char *lastRestartPointFileName)
+{
+ char *cmd;
+ int rc;
+
+ /* Build the restore command to execute */
+ cmd = BuildRestoreCommand(recoveryRestoreCommand, path, file,
+ lastRestartPointFileName);
+ if (cmd == NULL)
+ elog(ERROR, "could not build restore command \"%s\"", cmd);
+
+ ereport(DEBUG3,
+ (errmsg_internal("executing restore command \"%s\"", cmd)));
+
+ /*
+ * Copy xlog from archival storage to XLOGDIR
+ */
+ fflush(NULL);
+ pgstat_report_wait_start(WAIT_EVENT_RESTORE_COMMAND);
+ rc = system(cmd);
+ pgstat_report_wait_end();
+
+ pfree(cmd);
+
+ /*
+ * Remember, we rollforward UNTIL the restore fails so failure here is
+ * just part of the process... that makes it difficult to determine
+ * whether the restore failed because there isn't an archive to restore,
+ * or because the administrator has specified the restore program
+ * incorrectly. We have to assume the former.
+ *
+ * However, if the failure was due to any sort of signal, it's best to
+ * punt and abort recovery. (If we "return false" here, upper levels will
+ * assume that recovery is complete and start up the database!) It's
+ * essential to abort on child SIGINT and SIGQUIT, because per spec
+ * system() ignores SIGINT and SIGQUIT while waiting; if we see one of
+ * those it's a good bet we should have gotten it too.
+ *
+ * On SIGTERM, assume we have received a fast shutdown request, and exit
+ * cleanly. It's pure chance whether we receive the SIGTERM first, or the
+ * child process. If we receive it first, the signal handler will call
+ * proc_exit, otherwise we do it here. If we or the child process received
+ * SIGTERM for any other reason than a fast shutdown request, postmaster
+ * will perform an immediate shutdown when it sees us exiting
+ * unexpectedly.
+ *
+ * We treat hard shell errors such as "command not found" as fatal, too.
+ */
+ if (wait_result_is_signal(rc, SIGTERM))
+ proc_exit(1);
+
+ ereport(wait_result_is_any_signal(rc, true) ? FATAL : DEBUG2,
+ (errmsg("could not restore file \"%s\" from archive: %s",
+ file, wait_result_to_str(rc))));
+
+ return (rc == 0);
+}
+
+void
+shell_archive_cleanup(const char *lastRestartPointFileName)
+{
+ ExecuteRecoveryCommand(archiveCleanupCommand, "archive_cleanup_command",
+ false, WAIT_EVENT_ARCHIVE_CLEANUP_COMMAND,
+ lastRestartPointFileName);
+}
+
+void
+shell_recovery_end(const char *lastRestartPointFileName)
+{
+ ExecuteRecoveryCommand(recoveryEndCommand, "recovery_end_command", true,
+ WAIT_EVENT_RECOVERY_END_COMMAND,
+ lastRestartPointFileName);
+}
+
+/*
+ * Attempt to execute an external shell command during recovery.
+ *
+ * 'command' is the shell command to be executed, 'commandName' is a
+ * human-readable name describing the command emitted in the logs. If
+ * 'failOnSignal' is true and the command is killed by a signal, a FATAL
+ * error is thrown. Otherwise a WARNING is emitted.
+ *
+ * This is currently used for recovery_end_command and archive_cleanup_command.
+ */
+static void
+ExecuteRecoveryCommand(const char *command, const char *commandName,
+ bool failOnSignal, uint32 wait_event_info,
+ const char *lastRestartPointFileName)
+{
+ char xlogRecoveryCmd[MAXPGPATH];
+ char *dp;
+ char *endp;
+ const char *sp;
+ int rc;
+
+ Assert(command && commandName);
+
+ /*
+ * construct the command to be executed
+ */
+ dp = xlogRecoveryCmd;
+ endp = xlogRecoveryCmd + MAXPGPATH - 1;
+ *endp = '\0';
+
+ for (sp = command; *sp; sp++)
+ {
+ if (*sp == '%')
+ {
+ switch (sp[1])
+ {
+ case 'r':
+ /* %r: filename of last restartpoint */
+ sp++;
+ strlcpy(dp, lastRestartPointFileName, endp - dp);
+ dp += strlen(dp);
+ break;
+ case '%':
+ /* convert %% to a single % */
+ sp++;
+ if (dp < endp)
+ *dp++ = *sp;
+ break;
+ default:
+ /* otherwise treat the % as not special */
+ if (dp < endp)
+ *dp++ = *sp;
+ break;
+ }
+ }
+ else
+ {
+ if (dp < endp)
+ *dp++ = *sp;
+ }
+ }
+ *dp = '\0';
+
+ ereport(DEBUG3,
+ (errmsg_internal("executing %s \"%s\"", commandName, command)));
+
+ /*
+ * execute the constructed command
+ */
+ fflush(NULL);
+ pgstat_report_wait_start(wait_event_info);
+ rc = system(xlogRecoveryCmd);
+ pgstat_report_wait_end();
+
+ if (rc != 0)
+ {
+ /*
+ * If the failure was due to any sort of signal, it's best to punt and
+ * abort recovery. See comments in shell_restore().
+ */
+ ereport((failOnSignal && wait_result_is_any_signal(rc, true)) ? FATAL : WARNING,
+ /*------
+ translator: First %s represents a postgresql.conf parameter name like
+ "recovery_end_command", the 2nd is the value of that parameter, the
+ third an already translated error message. */
+ (errmsg("%s \"%s\": %s", commandName,
+ command, wait_result_to_str(rc))));
+ }
+}
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 0070d56b0b..fdce12614a 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -4887,10 +4887,24 @@ CleanupAfterArchiveRecovery(TimeLineID EndOfLogTLI, XLogRecPtr EndOfLog,
* Execute the recovery_end_command, if any.
*/
if (recoveryEndCommand && strcmp(recoveryEndCommand, "") != 0)
- ExecuteRecoveryCommand(recoveryEndCommand,
- "recovery_end_command",
- true,
- WAIT_EVENT_RECOVERY_END_COMMAND);
+ {
+ char lastRestartPointFname[MAXPGPATH];
+ XLogSegNo restartSegNo;
+ XLogRecPtr restartRedoPtr;
+ TimeLineID restartTli;
+
+ /*
+ * Calculate the archive file cutoff point for use during log shipping
+ * replication. All files earlier than this point can be deleted from
+ * the archive, though there is no requirement to do so.
+ */
+ GetOldestRestartPoint(&restartRedoPtr, &restartTli);
+ XLByteToSeg(restartRedoPtr, restartSegNo, wal_segment_size);
+ XLogFileName(lastRestartPointFname, restartTli, restartSegNo,
+ wal_segment_size);
+
+ shell_recovery_end(lastRestartPointFname);
+ }
/*
* We switched to a new timeline. Clean up segments on the old timeline.
@@ -7307,10 +7321,24 @@ CreateRestartPoint(int flags)
* Finally, execute archive_cleanup_command, if any.
*/
if (archiveCleanupCommand && strcmp(archiveCleanupCommand, "") != 0)
- ExecuteRecoveryCommand(archiveCleanupCommand,
- "archive_cleanup_command",
- false,
- WAIT_EVENT_ARCHIVE_CLEANUP_COMMAND);
+ {
+ char lastRestartPointFname[MAXPGPATH];
+ XLogSegNo restartSegNo;
+ XLogRecPtr restartRedoPtr;
+ TimeLineID restartTli;
+
+ /*
+ * Calculate the archive file cutoff point for use during log shipping
+ * replication. All files earlier than this point can be deleted from
+ * the archive, though there is no requirement to do so.
+ */
+ GetOldestRestartPoint(&restartRedoPtr, &restartTli);
+ XLByteToSeg(restartRedoPtr, restartSegNo, wal_segment_size);
+ XLogFileName(lastRestartPointFname, restartTli, restartSegNo,
+ wal_segment_size);
+
+ shell_archive_cleanup(lastRestartPointFname);
+ }
return true;
}
diff --git a/src/backend/access/transam/xlogarchive.c b/src/backend/access/transam/xlogarchive.c
index 76abc74c67..b5cb060d55 100644
--- a/src/backend/access/transam/xlogarchive.c
+++ b/src/backend/access/transam/xlogarchive.c
@@ -56,9 +56,8 @@ RestoreArchivedFile(char *path, const char *xlogfname,
bool cleanupEnabled)
{
char xlogpath[MAXPGPATH];
- char *xlogRestoreCmd;
char lastRestartPointFname[MAXPGPATH];
- int rc;
+ bool ret;
struct stat stat_buf;
XLogSegNo restartSegNo;
XLogRecPtr restartRedoPtr;
@@ -149,18 +148,6 @@ RestoreArchivedFile(char *path, const char *xlogfname,
else
XLogFileName(lastRestartPointFname, 0, 0L, wal_segment_size);
- /* Build the restore command to execute */
- xlogRestoreCmd = BuildRestoreCommand(recoveryRestoreCommand,
- xlogpath, xlogfname,
- lastRestartPointFname);
- if (xlogRestoreCmd == NULL)
- elog(ERROR, "could not build restore command \"%s\"",
- recoveryRestoreCommand);
-
- ereport(DEBUG3,
- (errmsg_internal("executing restore command \"%s\"",
- xlogRestoreCmd)));
-
/*
* Check signals before restore command and reset afterwards.
*/
@@ -169,15 +156,11 @@ RestoreArchivedFile(char *path, const char *xlogfname,
/*
* Copy xlog from archival storage to XLOGDIR
*/
- fflush(NULL);
- pgstat_report_wait_start(WAIT_EVENT_RESTORE_COMMAND);
- rc = system(xlogRestoreCmd);
- pgstat_report_wait_end();
+ ret = shell_restore(xlogfname, xlogpath, lastRestartPointFname);
PostRestoreCommand();
- pfree(xlogRestoreCmd);
- if (rc == 0)
+ if (ret)
{
/*
* command apparently succeeded, but let's make sure the file is
@@ -233,37 +216,6 @@ RestoreArchivedFile(char *path, const char *xlogfname,
}
}
- /*
- * Remember, we rollforward UNTIL the restore fails so failure here is
- * just part of the process... that makes it difficult to determine
- * whether the restore failed because there isn't an archive to restore,
- * or because the administrator has specified the restore program
- * incorrectly. We have to assume the former.
- *
- * However, if the failure was due to any sort of signal, it's best to
- * punt and abort recovery. (If we "return false" here, upper levels will
- * assume that recovery is complete and start up the database!) It's
- * essential to abort on child SIGINT and SIGQUIT, because per spec
- * system() ignores SIGINT and SIGQUIT while waiting; if we see one of
- * those it's a good bet we should have gotten it too.
- *
- * On SIGTERM, assume we have received a fast shutdown request, and exit
- * cleanly. It's pure chance whether we receive the SIGTERM first, or the
- * child process. If we receive it first, the signal handler will call
- * proc_exit, otherwise we do it here. If we or the child process received
- * SIGTERM for any other reason than a fast shutdown request, postmaster
- * will perform an immediate shutdown when it sees us exiting
- * unexpectedly.
- *
- * We treat hard shell errors such as "command not found" as fatal, too.
- */
- if (wait_result_is_signal(rc, SIGTERM))
- proc_exit(1);
-
- ereport(wait_result_is_any_signal(rc, true) ? FATAL : DEBUG2,
- (errmsg("could not restore file \"%s\" from archive: %s",
- xlogfname, wait_result_to_str(rc))));
-
not_available:
/*
@@ -277,110 +229,6 @@ not_available:
return false;
}
-/*
- * Attempt to execute an external shell command during recovery.
- *
- * 'command' is the shell command to be executed, 'commandName' is a
- * human-readable name describing the command emitted in the logs. If
- * 'failOnSignal' is true and the command is killed by a signal, a FATAL
- * error is thrown. Otherwise a WARNING is emitted.
- *
- * This is currently used for recovery_end_command and archive_cleanup_command.
- */
-void
-ExecuteRecoveryCommand(const char *command, const char *commandName,
- bool failOnSignal, uint32 wait_event_info)
-{
- char xlogRecoveryCmd[MAXPGPATH];
- char lastRestartPointFname[MAXPGPATH];
- char *dp;
- char *endp;
- const char *sp;
- int rc;
- XLogSegNo restartSegNo;
- XLogRecPtr restartRedoPtr;
- TimeLineID restartTli;
-
- Assert(command && commandName);
-
- /*
- * Calculate the archive file cutoff point for use during log shipping
- * replication. All files earlier than this point can be deleted from the
- * archive, though there is no requirement to do so.
- */
- GetOldestRestartPoint(&restartRedoPtr, &restartTli);
- XLByteToSeg(restartRedoPtr, restartSegNo, wal_segment_size);
- XLogFileName(lastRestartPointFname, restartTli, restartSegNo,
- wal_segment_size);
-
- /*
- * construct the command to be executed
- */
- dp = xlogRecoveryCmd;
- endp = xlogRecoveryCmd + MAXPGPATH - 1;
- *endp = '\0';
-
- for (sp = command; *sp; sp++)
- {
- if (*sp == '%')
- {
- switch (sp[1])
- {
- case 'r':
- /* %r: filename of last restartpoint */
- sp++;
- strlcpy(dp, lastRestartPointFname, endp - dp);
- dp += strlen(dp);
- break;
- case '%':
- /* convert %% to a single % */
- sp++;
- if (dp < endp)
- *dp++ = *sp;
- break;
- default:
- /* otherwise treat the % as not special */
- if (dp < endp)
- *dp++ = *sp;
- break;
- }
- }
- else
- {
- if (dp < endp)
- *dp++ = *sp;
- }
- }
- *dp = '\0';
-
- ereport(DEBUG3,
- (errmsg_internal("executing %s \"%s\"", commandName, command)));
-
- /*
- * execute the constructed command
- */
- fflush(NULL);
- pgstat_report_wait_start(wait_event_info);
- rc = system(xlogRecoveryCmd);
- pgstat_report_wait_end();
-
- if (rc != 0)
- {
- /*
- * If the failure was due to any sort of signal, it's best to punt and
- * abort recovery. See comments in RestoreArchivedFile().
- */
- ereport((failOnSignal && wait_result_is_any_signal(rc, true)) ? FATAL : WARNING,
- /*------
- translator: First %s represents a postgresql.conf parameter name like
- "recovery_end_command", the 2nd is the value of that parameter, the
- third an already translated error message. */
- (errmsg("%s \"%s\": %s", commandName,
- command, wait_result_to_str(rc))));
- }
-}
-
-
/*
* A file was restored from the archive under a temporary filename (path),
* and now we want to keep it. Rename it under the permanent filename in
diff --git a/src/include/access/xlogarchive.h b/src/include/access/xlogarchive.h
index 31ff206034..299304703e 100644
--- a/src/include/access/xlogarchive.h
+++ b/src/include/access/xlogarchive.h
@@ -20,8 +20,6 @@
extern bool RestoreArchivedFile(char *path, const char *xlogfname,
const char *recovername, off_t expectedSize,
bool cleanupEnabled);
-extern void ExecuteRecoveryCommand(const char *command, const char *commandName,
- bool failOnSignal, uint32 wait_event_info);
extern void KeepFileRestoredFromArchive(const char *path, const char *xlogfname);
extern void XLogArchiveNotify(const char *xlog);
extern void XLogArchiveNotifySeg(XLogSegNo segno, TimeLineID tli);
@@ -32,4 +30,9 @@ extern bool XLogArchiveIsReady(const char *xlog);
extern bool XLogArchiveIsReadyOrDone(const char *xlog);
extern void XLogArchiveCleanup(const char *xlog);
+extern bool shell_restore(const char *file, const char *path,
+ const char *lastRestartPointFileName);
+extern void shell_archive_cleanup(const char *lastRestartPointFileName);
+extern void shell_recovery_end(const char *lastRestartPointFileName);
+
#endif /* XLOG_ARCHIVE_H */
--
2.25.1
--Kj7319i9nmIyA2yE
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0002-Refactor-code-for-restoring-files-via-shell.patch"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Feature Recommendations for Logical Subscriptions
@ 2025-04-08 02:04 =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 04:42 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: =?utf-8?B?WWVYaXU=?= @ 2025-04-08 02:04 UTC (permalink / raw)
To: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
Business Scenario:
The BI department requires real-time data from the operational database. In our current approach (on platform 14), we create a separate database within our department's real-time backup instance, set up a logical replication account, replicate required tables to this isolated database via logical replication, and then create a dedicated account with column-level permissions on specific tables for the BI department.
Recommendations:
1、Column Filtering Functionality: During implementation, some tables may contain sensitive data or long fields (e.g., text columns), while other fields in these tables still need to be replicated to another database or instance. Manually specifying individual columns becomes cumbersome, especially for tables with many fields, and complicates future field additions. We recommend adding a column filtering feature to logical replication to streamline this process.
2、Logical Replication Account Permissions:
Logical replication permissions should be decoupled from general database access permissions.
Proposed workflow:
Create a dedicated account with logical replication privileges only.
Create a logical replication slot and grant this account access only to the authorized replication slot.
This account would have no direct access to the database itself but could subscribe to and consume data from the permitted replication slot.
This approach allows securely providing the account to the BI department. They can subscribe to the replication slot and perform downstream processing independently, without risking exposure of unauthorized data.
YeXiu
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Feature Recommendations for Logical Subscriptions
2025-04-08 02:04 Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
@ 2025-04-10 04:42 ` =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 22:10 ` Re: Feature Recommendations for Logical Subscriptions Peter Smith <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: =?utf-8?B?WWVYaXU=?= @ 2025-04-10 04:42 UTC (permalink / raw)
To: =?utf-8?B?QW1pdCBLYXBpbGE=?= <[email protected]>; +Cc: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
For example:
Assume database db1 has a user table with columns c1, c2, c3, ..., c10, telphone, and content (where telphone is a sensitive data field, and content is of type text).
1.We need to synchronize the user table to the BI department, but they should not have access to the telphone column due to sensitivity. The content column is also unnecessary for BI as it is too long and lacks analytical value.
During synchronization, we need to exclude both telphone and content columns. However, the user table may continue to add new columns (e.g., c11, c12) in the future.
The current approach is:
CREATE PUBLICATION pub FOR TABLE public.user (c1, c2, c3, ..., c10);
When new columns like c11 or c12 are added, we must manually update the publication:
ALTER PUBLICATION pub SET TABLE public.user (c1, c2, c3, ..., c10, c11, c12);
This repetitive work is inefficient. I suggest using the EXCEPT syntax as you mentioned earlier:
CREATE PUBLICATION pub FOR TABLE public.user EXCEPT (telphone, content);
This would automatically exclude sensitive or unnecessary columns, even as new columns are added. Additionally, we need a method to modify the exclusion list dynamically, such as:
ALTER PUBLICATION pub SET TABLE public.user EXCEPT (telphone);
-- or --
ALTER PUBLICATION pub SET TABLE public.user EXCEPT (telphone, content, c11);
2. Second Issue:
This extends the scenario above. I’m unsure how permissions are currently handled.
You mentioned creating a dedicated account u1 with only logical replication privileges, setting up a replication slot, and granting access to this slot.
My question: If the telphone column is excluded from the publication, does the subscriber still receive or parse data for telphone? Or is the column entirely absent from the replication slot?
If the replication slot does NOT include telphone data, this is a non-issue and can be ignored.
3. I hope others in the community can address these suggestions, as I am not a C developer and cannot implement them myself.
YeXiu
[email protected]
原始邮件
发件人：Amit Kapila <[email protected]>
发件时间：2025年4月9日 18:44
收件人：YeXiu <[email protected]>
抄送：pgsql-hackers <[email protected]>
主题：Re: Feature Recommendations for Logical Subscriptions
On Tue, Apr 8, 2025 at 2:48 PM YeXiu <[email protected]> wrote:
>
> Business Scenario:
> The BI department requires real-time data from the operational database. In our current approach (on platform 14), we create a separate database within our department's real-time backup instance, set up a logical replication account, replicate required tables to this isolated database via logical replication, and then create a dedicated account with column-level permissions on specific tables for the BI department.
>
> Recommendations:
>
> 1、Column Filtering Functionality: During implementation, some tables may contain sensitive data or long fields (e.g., text columns), while other fields in these tables still need to be replicated to another database or instance. Manually specifying individual columns becomes cumbersome, especially for tables with many fields, and complicates future field additions. We recommend adding a column filtering feature to logical replication to streamline this process.
>
It would have been better if you could provide some examples. Let me
try to describe by example. We have a feature where users can specify
columns they want to replicate. For example: Create a publication that
publishes all changes for table users, but replicates only columns
user_id and firstname:
CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
As per my understanding, you are expecting a feature where we can
specify columns that won't be replicated. For example say a table t1
has columns c1, c2, c3, ..., c10. Now, the user would like to
replicate all columns except c9 and c10, so he should be allowed to do
so by something like CREATE PUBLICATION users_filtered FOR TABLE t1
Except (c9, c10). Is that correct or you have something else in mind?
>
> 2、Logical Replication Account Permissions:
> Logical replication permissions should be decoupled from general database access permissions.
> Proposed workflow:
> Create a dedicated account with logical replication privileges only.
> Create a logical replication slot and grant this account access only to the authorized replication slot.
> This account would have no direct access to the database itself but could subscribe to and consume data from the permitted replication slot.
> This approach allows securely providing the account to the BI department. They can subscribe to the replication slot and perform downstream processing independently, without risking exposure of unauthorized data.
>
We need to access catalog tables in the database while decoding
changes, so won't some interaction with database privileges still be
required?
BTW, are you planning to work on a patch on these proposals or you
expect someone else in the community to work on these proposals?
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Feature Recommendations for Logical Subscriptions
2025-04-08 02:04 Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 04:42 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
@ 2025-04-10 22:10 ` Peter Smith <[email protected]>
2025-04-11 04:00 ` Re: Feature Recommendations for Logical Subscriptions Amit Kapila <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Peter Smith @ 2025-04-10 22:10 UTC (permalink / raw)
To: YeXiu <[email protected]>; +Cc: Amit Kapila <[email protected]>; pgsql-hackers <[email protected]>
Hi,
FYI, the Column List documentation [1] says
------
However, do not rely on this feature for security: a malicious
subscriber is able to obtain data from columns that are not
specifically published. If security is a consideration, protections
can be applied at the publisher side.
------
IIRC, this was something to do with how the COPY done by the initial
table sync might be manipulated by a malicious subscriber. I think you
can find more details about this in the original thread when Column
Lists were introduced. e.g. try searching this [2] thread for the word
"security".
======
[1] https://www.postgresql.org/docs/current/logical-replication-col-lists.html
[2] https://www.postgresql.org/message-id/flat/CAH2L28vddB_NFdRVpuyRBJEBWjz4BSyTB%3D_ektNRH8NJ1jf95g%40m...
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Feature Recommendations for Logical Subscriptions
2025-04-08 02:04 Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 04:42 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 22:10 ` Re: Feature Recommendations for Logical Subscriptions Peter Smith <[email protected]>
@ 2025-04-11 04:00 ` Amit Kapila <[email protected]>
2025-04-11 05:47 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-11 13:29 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Amit Kapila @ 2025-04-11 04:00 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: YeXiu <[email protected]>; pgsql-hackers <[email protected]>
On Fri, Apr 11, 2025 at 3:40 AM Peter Smith <[email protected]> wrote:
>
> FYI, the Column List documentation [1] says
> ------
> However, do not rely on this feature for security: a malicious
> subscriber is able to obtain data from columns that are not
> specifically published. If security is a consideration, protections
> can be applied at the publisher side.
> ------
>
> IIRC, this was something to do with how the COPY done by the initial
> table sync might be manipulated by a malicious subscriber. I think you
> can find more details about this in the original thread when Column
> Lists were introduced. e.g. try searching this [2] thread for the word
> "security".
>
The same thing applies here as well. The only key difference is user
convenience in two ways: (a) when there are a lot of columns, say 100
columns, and user would like to send all data except 2 columns, (b)
adding new columns to table would require users to again run the DDL
to change the column list.
These are primarily the two pain points YeXiu wants us to solve.
YeXiu, if I misunderstood your intention, feel free to add.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Feature Recommendations for Logical Subscriptions
2025-04-08 02:04 Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 04:42 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 22:10 ` Re: Feature Recommendations for Logical Subscriptions Peter Smith <[email protected]>
2025-04-11 04:00 ` Re: Feature Recommendations for Logical Subscriptions Amit Kapila <[email protected]>
@ 2025-04-11 05:47 ` =?utf-8?B?WWVYaXU=?= <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: =?utf-8?B?WWVYaXU=?= @ 2025-04-11 05:47 UTC (permalink / raw)
To: =?utf-8?B?QW1pdCBLYXBpbGE=?= <[email protected]>; =?utf-8?B?UGV0ZXIgU21pdGg=?= <[email protected]>; +Cc: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
Amit Kapila, Yes, as you mentioned, but I’d like to add that when using the exclusion method for newly added columns, there’s no need to modify the publication. This is similar to how fields are automatically synchronized when columns are unspecified during initial setup. This is also a key reason why this approach is valuable.
YeXiu
[email protected]
原始邮件
发件人：Amit Kapila <[email protected]>
发件时间：2025年4月11日 12:00
收件人：Peter Smith <[email protected]>
抄送：YeXiu <[email protected]>, pgsql-hackers <[email protected]>
主题：Re: Feature Recommendations for Logical Subscriptions
On Fri, Apr 11, 2025 at 3:40 AM Peter Smith wrote:
>
> FYI, the Column List documentation [1] says
> ------
> However, do not rely on this feature for security: a malicious
> subscriber is able to obtain data from columns that are not
> specifically published. If security is a consideration, protections
> can be applied at the publisher side.
> ------
>
> IIRC, this was something to do with how the COPY done by the initial
> table sync might be manipulated by a malicious subscriber. I think you
> can find more details about this in the original thread when Column
> Lists were introduced. e.g. try searching this [2] thread for the word
> "security".
>
The same thing applies here as well. The only key difference is user
convenience in two ways: (a) when there are a lot of columns, say 100
columns, and user would like to send all data except 2 columns, (b)
adding new columns to table would require users to again run the DDL
to change the column list.
These are primarily the two pain points YeXiu wants us to solve.
YeXiu, if I misunderstood your intention, feel free to add.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Feature Recommendations for Logical Subscriptions
2025-04-08 02:04 Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 04:42 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 22:10 ` Re: Feature Recommendations for Logical Subscriptions Peter Smith <[email protected]>
2025-04-11 04:00 ` Re: Feature Recommendations for Logical Subscriptions Amit Kapila <[email protected]>
@ 2025-04-11 13:29 ` =?utf-8?B?WWVYaXU=?= <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: =?utf-8?B?WWVYaXU=?= @ 2025-04-11 13:29 UTC (permalink / raw)
To: =?utf-8?B?QW1pdCBLYXBpbGE=?= <[email protected]>; =?utf-8?B?UGV0ZXIgU21pdGg=?= <[email protected]>; +Cc: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
Another permission-related issue involves scenarios where multiple logical replication slots exist. If a replication slot grants full data access permissions and user accounts are not explicitly bound to specific slots, there could be security risks where accounts might connect to high-privilege replication slots, potentially leading to data security vulnerabilities.
YeXiu
[email protected]
原始邮件
发件人：Amit Kapila <[email protected]>
发件时间：2025年4月11日 12:00
收件人：Peter Smith <[email protected]>
抄送：YeXiu <[email protected]>, pgsql-hackers <[email protected]>
主题：Re: Feature Recommendations for Logical Subscriptions
On Fri, Apr 11, 2025 at 3:40 AM Peter Smith wrote:
>
> FYI, the Column List documentation [1] says
> ------
> However, do not rely on this feature for security: a malicious
> subscriber is able to obtain data from columns that are not
> specifically published. If security is a consideration, protections
> can be applied at the publisher side.
> ------
>
> IIRC, this was something to do with how the COPY done by the initial
> table sync might be manipulated by a malicious subscriber. I think you
> can find more details about this in the original thread when Column
> Lists were introduced. e.g. try searching this [2] thread for the word
> "security".
>
The same thing applies here as well. The only key difference is user
convenience in two ways: (a) when there are a lot of columns, say 100
columns, and user would like to send all data except 2 columns, (b)
adding new columns to table would require users to again run the DDL
to change the column list.
These are primarily the two pain points YeXiu wants us to solve.
YeXiu, if I misunderstood your intention, feel free to add.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-04-11 13:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-24 00:35 [PATCH v4 1/3] Move the code to restore files via the shell to a separate file. Nathan Bossart <[email protected]>
2025-04-08 02:04 Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 04:42 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-10 22:10 ` Re: Feature Recommendations for Logical Subscriptions Peter Smith <[email protected]>
2025-04-11 04:00 ` Re: Feature Recommendations for Logical Subscriptions Amit Kapila <[email protected]>
2025-04-11 05:47 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
2025-04-11 13:29 ` Re: Feature Recommendations for Logical Subscriptions =?utf-8?B?WWVYaXU=?= <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox