Comments for Enforcing Transaction Compartments with Foreign Keys and SECURITY DEFINER

[phinjensen]

Comments for https://www.endpointdev.com/blog/2012/09/enforcing-transaction-compartments-with/ By Mark Johnson

To enter a comment:

1. Log in to GitHub
2. Leave a comment on this issue.

[phinjensen]

original author: Jon Jensen
date: 2012-09-04T20:41:08-04:00

Nice write-up, Mark.

You only mention direct pg_class manipulation in this article, which has known but rare non-MVCC behavior, as noted in this Bucardo source code that uses pg_class manipulation only on older Postgres versions where there was no better way.

From the look of things in your PL/pgSQL functions, the newer session_replication_role GUC designed for this use would work fine too. Have you used that in your functions on newer versions of Postgres?

Also, way to sneak the word "shenanigans" into your code comments as well. :)

[phinjensen]

original author: Greg Sabino Mullane
date: 2012-09-04T22:08:52-04:00

Mark can correct me if I am wrong, but I think the main reason we did not decide to use session_replication_role once Postgres introduced it is because it is "all or nothing", and the implementation used relies heavily on being able to turn off triggers and rules on a specific table only. (The other possible reason we never switched to SRR is "if it ain't broke, don't fix it' :)

[phinjensen]

original author: Mark Johnson
date: 2012-09-21T22:48:02-04:00

Jon,

There are two main reasons we've left the function pair operating directly against pg_class.

One is that our primary client using them has certain features within Bucardo's customcode that were not functioning properly while in session_replication_role.

The other is as Greg mentions. We use these functions within application code where the desire to suppress triggers and rules is surgical--some tables have it, and others don't, within the same transaction.

It would be fairly simple to produce non-arg versions of the functions that could leverage session_replication_role to make a transaction-wide disabled state. That would in fact be quite handy if that's what you really wanted and you were manipulating a large number of tables, rather than having to call the functions once each for the entire list of tables.

[phinjensen]

original author: Jon Jensen
date: 2012-09-24T11:17:04-04:00

Regarding "surgical" disabling of triggers:

I think the current way to do that without non-MVCC-safe system table munging is with ALTER TABLE ... DISABLE TRIGGER. Unless I'm missing some fundamental difference between that an pg_class munging.

[phinjensen]

original author: Mark Johnson
date: 2012-09-24T11:30:19-04:00

The main difference is the access exclusive lock required for ALTER TABLE vs. pg_class manipulation.

[phinjensen]

original author: Greg Sabino Mullane
date: 2012-09-25T23:28:47-04:00

To expand, the exclusive lock was a total show-stopper (quite literally) on the production system. Messing with pg_class has disadvantages as well, but well worth the tradeoff. :)