Re: running logical replication as the subscription owner

On Fri, Mar 24, 2023 at 10:00:36AM -0400, Robert Haas wrote:
> On Fri, Mar 24, 2023 at 3:59 AM Jeff Davis <pgsql@j-davis.com> wrote:
> > That's a little confusing, why not just always use the
> > SECURITY_RESTRICTED_OPERATION? Is there a use case I'm missing?
> 
> Some concern was expressed -- not sure exactly where the email is
> exactly, and it might've been on pgsql-security -- that doing that
> categorically might break things that are currently working. The
> people who were concerned included Andres and I forget who else. My

SECURITY_RESTRICTED_OPERATION blocks deferred triggers and CREATE TEMP TABLE.
If you create a DEFERRABLE INITIALLY DEFERRED fk constraint and replicate to
the constraint's table in a SECURITY_RESTRICTED_OPERATION, I expect an error.

> gut reaction was the same as yours, just do it always and don't worry
> about it. But if people think that users are likely to run afoul of

Hard to know.  It's the sort of thing that I model as creating months of work
for epsilon users to adapt their applications.  Epsilon could be zero.  Most
users don't notice.

> the SECURITY_RESTRICTED_OPERATION restrictions in practice, then this
> is better, and the implementation complexity isn't high. We could even
> think of extending this kind of logic to other places where
> SECURITY_RESTRICTED_OPERATION is enforced, if desired.

Firing a trigger in an index expression or materialized view query is not
reasonable, so today's uses of SECURITY_RESTRICTED_OPERATION would not benefit
from this approach.  Being able to create temp tables in those places has some
value, but I would allow temp tables in SECURITY_RESTRICTED_OPERATION instead
of proliferating the check on the ability to SET ROLE.  (One might allow temp
tables by introducing NewTempSchemaNestLevel(), called whenever we call
NewGUCNestLevel().  The transaction would then proceed as though it has no
temp schema, allocating an additional schema if creating a temp object.)