document the dangers of granting TRIGGER or REFERENCES

On Tue, Jul 14, 2026 at 09:54:22AM -0400, Robert Haas wrote:

For many years now, the PostgreSQL security team has been receiving
reports that the TRIGGER and REFERENCES permissions are dangerous to
grant. This is not news, at least to me, and I actually feel like I
remember Tom making some comment about this on this list a long time
ago, but the searches I have tried so far have failed to locate that
email, so either I hallucinated it (heh, heh) or my search-fu is not
up to the task.

Here's a couple:

/messages/by-id/20230613182920.GA259321@gust.leadboat.com
/messages/by-id/flat/52EF20B2E3209443BC37736D00C3C1380BE323DC@EXADV1.host.magwien.gv.at

But for reasons which many of you may be able to guess, the rate of
such reports has increased lately, and I rather suspect it will
continue to increase. While that's frustrating, it is also true that
this is something that really should be called out in the
documentation, so here's a patch to do that. I modeled this on the
existing language we use for granting pg_execute_server_program. If
you don't like that language, feel free to suggest alternatives.

+1. I wonder if we should promote these to <note> or even <warning>.
While I'm hesitant to pepper the docs with too many more of those, this
seems like the sort of thing we don't want users to miss.

--
nathan

On Tue, Jul 14, 2026 at 10:24 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:

+1. I wonder if we should promote these to <note> or even <warning>.
While I'm hesitant to pepper the docs with too many more of those, this
seems like the sort of thing we don't want users to miss.

I'm not going to fight you on that tooth and nail if you really want
to do it that way, but I don't see much point. The paragraphs to which
I propose to add this information are currently just one sentence long
each, and with this patch they will be two sentences each. Probably we
don't need markup for people to notice the second sentence.

Also, while there may very well be people who have legitimately been
unpleasantly surprised by the actual behavior here, an awful lot of
the "people" currently discovering the situation here are in fact just
looking for a way to manufacture a security report. As long as we have
a statement of some kind in the docs, we can just point to that. It
doesn't need to be particularly prominent -- although I'm not trying
to conceal it, either. I didn't find a more natural place to mention
this than where I actually put it.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Tue, Jul 14, 2026 at 9:54 AM Robert Haas <robertmhaas@gmail.com> wrote:

If you don't like that language, feel free to suggest alternatives.

+1, good idea.

IMHO, apart from patching the documentation, the other thing we might

want to consider doing is removing one or both of these privileges
entirely, and making them part of table ownership.

Big +1 for references. I could see some pushback for triggers, but still
a +1 on that as well, as it should not break anything sane, and can easily
be worked around.

--
Cheers,
Greg

Greg Sabino Mullane <htamfids@gmail.com> writes:

On Tue, Jul 14, 2026 at 9:54 AM Robert Haas <robertmhaas@gmail.com> wrote:

IMHO, apart from patching the documentation, the other thing we might
want to consider doing is removing one or both of these privileges
entirely, and making them part of table ownership.

Big +1 for references.

REFERENCES is required by SQL spec, no?

(hmm, actually, TRIGGER is too ...)

regards, tom lane

Robert Haas <robertmhaas@gmail.com> writes:

Also, while there may very well be people who have legitimately been
unpleasantly surprised by the actual behavior here, an awful lot of
the "people" currently discovering the situation here are in fact just
looking for a way to manufacture a security report. As long as we have
a statement of some kind in the docs, we can just point to that. It
doesn't need to be particularly prominent -- although I'm not trying
to conceal it, either. I didn't find a more natural place to mention
this than where I actually put it.

Right. I looked around too, but this section (5.9) seems fine.
In particular, while the GRANT reference page lists the names of
the privileges, it does not describe any of them, just points you
to 5.9.

regards, tom lane

On Tue, Jul 14, 2026 at 10:35:38AM -0400, Robert Haas wrote:

On Tue, Jul 14, 2026 at 10:24 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:

+1. I wonder if we should promote these to <note> or even <warning>.
While I'm hesitant to pepper the docs with too many more of those, this
seems like the sort of thing we don't want users to miss.

I'm not going to fight you on that tooth and nail if you really want
to do it that way, but I don't see much point. The paragraphs to which
I propose to add this information are currently just one sentence long
each, and with this patch they will be two sentences each. Probably we
don't need markup for people to notice the second sentence.

WFM. I'm not particularly opinionated on the matter.

Also, while there may very well be people who have legitimately been
unpleasantly surprised by the actual behavior here, an awful lot of
the "people" currently discovering the situation here are in fact just
looking for a way to manufacture a security report. As long as we have
a statement of some kind in the docs, we can just point to that. It
doesn't need to be particularly prominent -- although I'm not trying
to conceal it, either. I didn't find a more natural place to mention
this than where I actually put it.

True.

--
nathan

On Tue, Jul 14, 2026 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Greg Sabino Mullane <htamfids@gmail.com> writes:

On Tue, Jul 14, 2026 at 9:54 AM Robert Haas <robertmhaas@gmail.com> wrote:

IMHO, apart from patching the documentation, the other thing we might
want to consider doing is removing one or both of these privileges
entirely, and making them part of table ownership.

Big +1 for references.

REFERENCES is required by SQL spec, no?

(hmm, actually, TRIGGER is too ...)

I presume so, but what concerns me is that I don't see a way that
either of them could be unproblematic from a security standpoint, and
therefore they seem like attractive nuisances rather than actually
valuable features to me. That said, I don't want to get sidetracked
into a big argument about potentially removing them; I'm content to
document the problem for now.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Tue, Jul 14, 2026 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

REFERENCES is required by SQL spec, no?

Makes me wonder if other database systems suffer from the same issue?
Perhaps not because they are not as extensible?

Cheers,
Greg

Greg Sabino Mullane <htamfids@gmail.com> writes:

On Tue, Jul 14, 2026 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

REFERENCES is required by SQL spec, no?

Makes me wonder if other database systems suffer from the same issue?

I think a strict-SQL-spec database is probably insulated from the
arbitrary-code-execution problem, because there isn't any way to get
user-defined code involved in executing comparisons. For real-world
systems, you'd have to take a close look at what datatype extension
capabilities they've added.

Of course, a holder of REFERENCES can still block updates and deletes
on your table, so it's not like it's something you'd give out to
untrusted roles anyway. (I wonder if Robert's proposed patch should
be extended to point that out. It's obvious, but so is the issue
with TRIGGER.)

regards, tom lane

On Tue, Jul 14, 2026 at 09:54:22AM -0400, Robert Haas wrote:

[...]. At least for TRIGGER, I
think this is pretty intuitive. If you cannot foresee that letting
someone create triggers on your table might lead to a security
exposure, you haven't thought very hard. The issue with REFERENCES is
admittedly more subtle. At least to me, it's abundantly clear that
letting your arch-nemesis create foreign keys pointing to your table
is a bad idea, because they'll probably use that access to make your
life miserable. I find it somewhat less clear, but it is nonetheless
true, that it lets them run code of their choosing as you -- and
AFAICS there's nothing we can really do about that without removing
SQL functionality upon which lots of people depend.

It lets them run their code as you because trigger procedures run as the
current_user, right? Maybe there should be some cases where SECURITY
DEFINER is implied, or where not having it is an error. E.g., if I
create a tigger or foreign key on a table I don't own but do have the
corresponding grant, then maybe my triggers' trigger procedures should
have to be SECURITY DEFINER. The problem is that cascading this
property may not be possible, and anyways it's silly for all the reasons
you give, so a documentation fix seems like the best/only thing to do.

IMHO, apart from patching the documentation, the other thing we might
want to consider doing is removing one or both of these privileges
entirely, and making them part of table ownership. I think there's
basically no scenario where granting an untrusted user even limited
permission to perform DDL on your objects is a sensible thing to do.

Hmmm, at $WORK we don't give many users access to DBs, we generally
don't give _any_ users access to DBs, only what we call "role accounts"
(user accounts, yes, but used for automation not interactive sessions),
and rarely more than a small handful per DB. In these cases these grant
permissions are really not a problem because these accounts are
generally used for related things and generally trust each other. So I
wouldn't think this is a great idea, but I guess we could always deal.

Nico
--

On Tue, Jul 14, 2026 at 3:52 PM Nico Williams <nico@cryptonector.com> wrote:

It lets them run their code as you because trigger procedures run as the
current_user, right? Maybe there should be some cases where SECURITY
DEFINER is implied, or where not having it is an error. E.g., if I
create a tigger or foreign key on a table I don't own but do have the
corresponding grant, then maybe my triggers' trigger procedures should
have to be SECURITY DEFINER. The problem is that cascading this
property may not be possible, and anyways it's silly for all the reasons
you give, so a documentation fix seems like the best/only thing to do.

Unfortunately, in this case and in many others, forcing SECURITY
DEFINER just moves the problem around. Suppose Owen has a table and
grants TRIGGER permissions to Terri. Terri runs CREATE TRIGGER, using
as the trigger function one owned by Frank. Finally, Iris inserts into
a row into Owen's table, firing the trigger created by Terri, and thus
executing the function owned by Frank. If the function is SECURITY
INVOKER, it runs as Iris, else it runs as Frank. Owen is still
recorded as the owner of the table, but Owen's privileges aren't used
anywhere. Terri's involvement isn't even recorded: the trigger has no
owner separate from the table. So making the function SECURITY DEFINER
just changes the current role from one role that isn't Terri to
another role that also isn't Terri, which doesn't seem to accomplish
anything useful from a security perspective.

But even if we could make the current role Terri -- say if Terri
points the trigger at Terri's own function rather than at Frank's
function, and we go ahead and force SECURITY DEFINER -- so what? We
don't know that Terri is malicious, and it might well be that the
newly-created trigger won't even work properly if run with Terri's
permissions. But it's worse than that: what if the bad actor is
actually Iris? In that case, Iris fires a trigger that runs as Iris
and can only do things that Iris can already do. If we forced the
trigger to run as Terri, it must now secure search_path and otherwise
take great care not to allow the caller to steal Terri's permissions.
If Owen and Terri are the good guys, this kind of change represents a
substantial security *regression*.

I do have some ideas about how to provide better protection against
this sort of thing -- see the thread on "sandboxing untrusted code" --
but I don't have it all figured out yet, and I don't want to divert
this thread into trying to hash that out.

For now, I am content to commit this patch, which I have now done. I
chose to back-patch, which is arguable, as it could be argued that the
lack of this information does not constitute a factual error, and
documentation improvements are typically committed only to the master
branch. However, in view of the fact that the security team has
received a number of reports related to this behavior, I felt a
back-patch was warranted. If anyone strongly disagrees, please speak
up, because there are other, similar cases which I also want to
tackle, and it will be useful to know whether back-patching is welcome
or unwelcome in these types of situations.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, Jul 20, 2026 at 02:13:04PM -0400, Robert Haas wrote:

On Tue, Jul 14, 2026 at 3:52 PM Nico Williams <nico@cryptonector.com> wrote:

It lets them run their code as you because trigger procedures run as the
current_user, right? Maybe there should be some cases where SECURITY
DEFINER is implied, or where not having it is an error. E.g., if I
create a tigger or foreign key on a table I don't own but do have the
corresponding grant, then maybe my triggers' trigger procedures should
have to be SECURITY DEFINER. The problem is that cascading this
property may not be possible, and anyways it's silly for all the reasons
you give, so a documentation fix seems like the best/only thing to do.

Unfortunately, in this case and in many others, forcing SECURITY
DEFINER just moves the problem around. Suppose Owen has a table and
grants TRIGGER permissions to Terri. Terri runs CREATE TRIGGER, using
as the trigger function one owned by Frank. Finally, Iris inserts into
[...]

Oh, but what I wrote and had in mind is that the TRIGGER _PROCEDURE_
would run as the PROCEDURE, though really it should be the TRIGGER
owner. And any functions called by that would run as the same user if
they themselves were SECURITY INVOKER.

At least this way anything that the TRIGGER does is done with its
owner's permissions. Though still, as noted, you get a stack of
user/role names, starting that of the one that started the top level
command. If you need to do authorization (_you will_) you'll want to
know all of those names.

Nico
--

On Tue, Jul 21, 2026 at 12:57 AM Nico Williams <nico@cryptonector.com> wrote:

Oh, but what I wrote and had in mind is that the TRIGGER _PROCEDURE_
would run as the PROCEDURE, though really it should be the TRIGGER
owner. And any functions called by that would run as the same user if
they themselves were SECURITY INVOKER.

Fair enough. I think that still has the problem that I described.

At least this way anything that the TRIGGER does is done with its
owner's permissions. Though still, as noted, you get a stack of
user/role names, starting that of the one that started the top level
command. If you need to do authorization (_you will_) you'll want to
know all of those names.

Knowing all of those names is exactly what I'm working on, but it's a
much bigger project.

--
Robert Haas
EDB: http://www.enterprisedb.com