On Tue, Jun 3, 2025 at 08:58:58AM -0400, Robert Haas wrote:
> On Mon, Jun 2, 2025 at 11:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > That being said I would like to see it corrected everywhere.
> >
> > Yeah, one approach we could take here is to try to move the goalposts
> > for this whole topic, understanding that that will mean incompatible
> > changes as well as some performance loss. I'm not sure how many users
> > would be happy to take that tradeoff, but some would. Maybe two
> > different operating modes would make it an easier sell?
>
> I still believe that the answer here is some kind of function trust mechanism:
>
> https://www.postgresql.org/message-id/flat/20180809190443.GA14011%40momjian.us#3dda365965c7d95007e58b7551161442
> https://www.postgresql.org/message-id/CA%2BTgmoaHpmz9-7ybB17B2qpDoqsi7%3DOWigc-3VBctb6B_x8bKA%40mail.gmail.com
> (pretty sure there's also a proposal from Noah, can't find the most
> current version of it at the moment)
>
> The problem with just up and changing the behavior is that it will
> probably break some use cases (e.g. an ON INSERT/UPDATE trigger that
> sets some column to current_user) and, worse still, it probably will
> just result in a bunch of security holes in the opposite direction,
> where the person inserting into the table is trying to hack the
> account of the table owner rather than the other way around. Jeff
> Davis has mentioned this hazard before: any time we switch user IDs to
> execute code, there are possible attacks in BOTH directions. I haven't
> seen a proposal other than function trust (in one of several proposed
> variations) that can close all of those holes.
I think the two cases are slightly different. Our existing system has
users running triggers on tables that don't own as themselves, so the
table owner has full control over what is in the triggers. If we were
to switch it so users run triggers as the table owner, the users can't
change the triggers --- they can only try to break the trigger by
changing the search path or something.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
