From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Ruwan Fernando <rutechs@gmail.com>,
        pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: How do CHECK Constraint Function privileges work?
In-reply-to: 
 <CAKFQuwZ-BV+fN-N2Ba8X+UWAO=1+8B_+dUZty7oGSNoyJ6iZFg@mail.gmail.com>
References: 
 <CANYEAx8vZnN9eeFQfsiLGMi9NdCP0wUdriHTCGU-7jP0VmNKPA@mail.gmail.com>
 <CAKFQuwZ-BV+fN-N2Ba8X+UWAO=1+8B_+dUZty7oGSNoyJ6iZFg@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Sun, 05 Apr 2020 07:37:26 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <11651.1586101335.1@sss.pgh.pa.us>
Date: Sun, 05 Apr 2020 11:42:15 -0400
Message-ID: <11652.1586101335@sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <rutechs@gmail.com> wrote:
>> My expectation was the INSERT would give me an exception due to
>> "grant_test_role" not having permissions on the "app_private" schema, but
>> it does not. Why does the CHECK constraint function executes fine in this
>> instance?

> While I cannot locate the relevant documentation right now, privileges for
> triggers and constraints attached to a table are made against the owner of
> the table, not the user performing the action.

No, that's not how it works, at least not for CHECK constraints --- those
are executed and privilege-checked as the user running the DML command.
(This might be the wrong thing, but that's what happens.)

The important point about the OP's example is that privilege checks on
schemas only happen at parse time, ie they are interpreted as "can you
look up this object right now?".  The only check made at execution time
is whether the calling user has EXECUTE privilege on the function,
working from the already-stored function OID --- so the schema is
irrelevant at that point.  Any stored expression such as a view or
CHECK constraint will act that way.

Not sure if this comports with what the SQL spec says, but that's
how PG does it.

			regards, tom lane