On Sun, Apr 5, 2020 at 9:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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
Arrgh! So that explains it! Thank You!
And the point about triggers (trigger function privileges being
checked at "creation" time, and not at "execution" time) is described
here https://www.postgresql.org/message-id/52EF20B2E3209443BC37736D00C3C1380BE323DC@EXADV1.host.magwien.gv.at
- I guess it still works the same way :)
Thank you very much & Kind Regards,
/Ruwan
