agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Ruwan Fernando <rutechs@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: How do CHECK Constraint Function privileges work?
Date: Sun, 05 Apr 2020 11:42:15 -0400
Message-ID: <11652.1586101335@sss.pgh.pa.us> (raw)
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>

"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

view thread (5+ messages)  latest in thread

Message-ID: <11652.1586101335@sss.pgh.pa.us>
Permalink:  ../11652.1586101335@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/11652.1586101335@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, rutechs@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: How do CHECK Constraint Function privileges work?
  In-Reply-To: <11652.1586101335@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox