codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>31.9. Security</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="logical-replication-monitoring.html" title="31.8. Monitoring" /><link rel="next" href="logical-replication-config.html" title="31.10. Configuration Settings" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">31.9. Security</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logical-replication-monitoring.html" title="31.8. Monitoring">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><th width="60%" align="center">Chapter 31. Logical Replication</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logical-replication-config.html" title="31.10. Configuration Settings">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGICAL-REPLICATION-SECURITY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">31.9. Security <a href="#LOGICAL-REPLICATION-SECURITY" class="id_link">#</a></h2></div></div></div><p>3   The role used for the replication connection must have4   the <code class="literal">REPLICATION</code> attribute (or be a superuser).  If the5   role lacks <code class="literal">SUPERUSER</code> and <code class="literal">BYPASSRLS</code>,6   publisher row security policies can execute.  If the role does not trust7   all table owners, include <code class="literal">options=-crow_security=off</code> in8   the connection string; if a table owner then adds a row security policy,9   that setting will cause replication to halt rather than execute the policy.10   Access for the role must be configured in <code class="filename">pg_hba.conf</code>11   and it must have the <code class="literal">LOGIN</code> attribute.12  </p><p>13   In order to be able to copy the initial table data, the role used for the14   replication connection must have the <code class="literal">SELECT</code> privilege on15   a published table (or be a superuser).16  </p><p>17   To create a publication, the user must have the <code class="literal">CREATE</code>18   privilege in the database.19  </p><p>20   To add tables to a publication, the user must have ownership rights on the21   table. To add all tables in schema to a publication, the user must be a22   superuser. To create a publication that publishes all tables or all tables in23   schema automatically, the user must be a superuser.24  </p><p>25   There are currently no privileges on publications.  Any subscription (that26   is able to connect) can access any publication.  Thus, if you intend to27   hide some information from particular subscribers, such as by using row28   filters or column lists, or by not adding the whole table to the29   publication, be aware that other publications in the same database could30   expose the same information.  Publication privileges might be added to31   <span class="productname">PostgreSQL</span> in the future to allow for32   finer-grained access control.33  </p><p>34   To create a subscription, the user must have the privileges of the35   the <code class="literal">pg_create_subscription</code> role, as well as36   <code class="literal">CREATE</code> privileges on the database.37  </p><p>38   The subscription apply process will, at a session level, run with the39   privileges of the subscription owner. However, when performing an insert,40   update, delete, or truncate operation on a particular table, it will switch41   roles to the table owner and perform the operation with the table owner's42   privileges. This means that the subscription owner needs to be able to43   <code class="literal">SET ROLE</code> to each role that owns a replicated table.44  </p><p>45   If the subscription has been configured with46   <code class="literal">run_as_owner = true</code>, then no user switching will47   occur. Instead, all operations will be performed with the permissions48   of the subscription owner. In this case, the subscription owner only49   needs privileges to <code class="literal">SELECT</code>, <code class="literal">INSERT</code>,50   <code class="literal">UPDATE</code>, and <code class="literal">DELETE</code> from the51   target table, and does not need privileges to <code class="literal">SET ROLE</code>52   to the table owner. However, this also means that any user who owns53   a table into which replication is happening can execute arbitrary code with54   the privileges of the subscription owner. For example, they could do this55   by simply attaching a trigger to one of the tables which they own.56   Because it is usually undesirable to allow one role to freely assume57   the privileges of another, this option should be avoided unless user58   security within the database is of no concern.59  </p><p>60   On the publisher, privileges are only checked once at the start of a61   replication connection and are not re-checked as each change record is read.62  </p><p>63   On the subscriber, the subscription owner's privileges are re-checked for64   each transaction when applied. If a worker is in the process of applying a65   transaction when the ownership of the subscription is changed by a66   concurrent transaction, the application of the current transaction will67   continue under the old owner's privileges.68  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logical-replication-monitoring.html" title="31.8. Monitoring">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logical-replication-config.html" title="31.10. Configuration Settings">Next</a></td></tr><tr><td width="40%" align="left" valign="top">31.8. Monitoring </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 31.10. Configuration Settings</td></tr></table></div></body></html>