"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Could you provide more details about that?
> We have a capture_replication_data() trigger function that we attach
> to each table which is to be replicated as the first AFTER EACH ROW
> trigger for INSERT OR UPDATE OR DELETE. It records the data
> involved in the primitive operation against the row for logical
> replication at the row level. We don't want users to be able to
> modify or even view the captured data in the replication tables
> except through this function. (It's actually a bit more complicated
> than that because of transaction metadata, but the overall concept
> is the same.)
> We currently use the database owner for the owner of these SECURITY
> DEFINER functions, but it seems to me that there could be legitimate
> reasons to create a special user with more limited rights than the
> database owner in some cases -- just to ensure that a mistake in the
> coding of a function doesn't open up an unnecessarily large security
> hole.
I'm not entirely following here. If the function is coded in C, then
it can pretty much do what it pleases independently of what user ID
is thought to be executing it at the SQL level. That would really only
matter if you were doing some SQL stuff via the SPI interface --- and
if that's the case, couldn't the C function set the appropriate role
to use for itself, anyway? (In other words, it's not that hard to build
a "RUN AS other-user" feature into a C function, even without any support
from the rest of the system.)
> Rather than trying to enforce this in the
> ALTER FUNCTION implementation, maybe we should just advise that if
> you're going to grant ownership of a C function to a role which
> might accidentally or maliciously allow it to be called with NULL
> input, the C function should return NULL in that case.
Yeah, the just-code-defensively option is worth considering too.
regards, tom lane