How do CHECK Constraint Function privileges work?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

How do CHECK Constraint Function privileges work?

Ruwan Fernando
I have created a test harness in a fresh DB where a role has access to 2 custom schemas, and have created a constraint check function in the app_private schema, while creating a table on the app_public schema.

CREATE SCHEMA app_public;
CREATE SCHEMA app_private;

GRANT USAGE ON SCHEMA app_public TO "grant_test_role";

CREATE OR REPLACE FUNCTION app_private.constraint_max_length(name_ TEXT) RETURNS BOOLEAN
  AS $$
BEGIN
  -- do some checks here
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE app_public.test_tab (
   id INT NOT NULL PRIMARY KEY,
   name TEXT NOT NULL,

   CONSTRAINT name_length_check CHECK (app_private.constraint_max_length(name));
);

BEGIN;
  SET LOCAL ROLE TO "grant_test_role";
  INSERT INTO app_public.test_tab (id, name) VALUES (1, 'Very Long Name');
COMMIT;

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?

I feel I'm missing some knowledge on how PostgreSQL internals work when checking privileges for CHECK constraint expressions, and I didn't find anything mentioned about this in documentation.

There are some followup questions regarding the PUBLIC role, which I will reserve until I can get some clarification on the current behavior of the CHECK constraint function's privilege check.

Thanks & Kind Regards,
/Ruwan.
Reply | Threaded
Open this post in threaded view
|

Re: How do CHECK Constraint Function privileges work?

David G Johnston
On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <[hidden email]> wrote:
I have created a test harness in a fresh DB where a role has access to 2 custom schemas, and have created a constraint check function in the app_private schema, while creating a table on the app_public schema.

CREATE SCHEMA app_public;
CREATE SCHEMA app_private;

GRANT USAGE ON SCHEMA app_public TO "grant_test_role";

CREATE OR REPLACE FUNCTION app_private.constraint_max_length(name_ TEXT) RETURNS BOOLEAN
  AS $$
BEGIN
  -- do some checks here
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE app_public.test_tab (
   id INT NOT NULL PRIMARY KEY,
   name TEXT NOT NULL,

   CONSTRAINT name_length_check CHECK (app_private.constraint_max_length(name));
);

Assuming you are connected as a superuser role here; you are not setting an explicit owner.
 

BEGIN;
  SET LOCAL ROLE TO "grant_test_role";
  INSERT INTO app_public.test_tab (id, name) VALUES (1, 'Very Long Name');
COMMIT;

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?

I feel I'm missing some knowledge on how PostgreSQL internals work when checking privileges for CHECK constraint expressions, and I didn't find anything mentioned about this in documentation.

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.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: How do CHECK Constraint Function privileges work?

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:
> On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: How do CHECK Constraint Function privileges work?

Ruwan Fernando
In reply to this post by David G Johnston
On Sun, Apr 5, 2020 at 8:07 PM David G. Johnston
<[hidden email]> wrote:

>
> On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <[hidden email]> wrote:
>>
>> I have created a test harness in a fresh DB where a role has access to 2 custom schemas, and have created a constraint check function in the app_private schema, while creating a table on the app_public schema.
>>
>> CREATE SCHEMA app_public;
>> CREATE SCHEMA app_private;
>>
>> GRANT USAGE ON SCHEMA app_public TO "grant_test_role";
>>
>> CREATE OR REPLACE FUNCTION app_private.constraint_max_length(name_ TEXT) RETURNS BOOLEAN
>>   AS $$
>> BEGIN
>>   -- do some checks here
>>   RETURN TRUE;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> CREATE TABLE app_public.test_tab (
>>    id INT NOT NULL PRIMARY KEY,
>>    name TEXT NOT NULL,
>>
>>    CONSTRAINT name_length_check CHECK (app_private.constraint_max_length(name));
>> );
>
>
> Assuming you are connected as a superuser role here; you are not setting an explicit owner.
I'm sorry, forgot to mention that part. I run the DDL statements as
the db_owner user role who owns the DB, who is also a superuser.

>
>>
>>
>> BEGIN;
>>   SET LOCAL ROLE TO "grant_test_role";
>>   INSERT INTO app_public.test_tab (id, name) VALUES (1, 'Very Long Name');
>> COMMIT;
>>
>> 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?
>>
>> I feel I'm missing some knowledge on how PostgreSQL internals work when checking privileges for CHECK constraint expressions, and I didn't find anything mentioned about this in documentation.
>
>
> 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.
>
> David J.
>
Thank you for your reply, and that was my thought initially as well.
I'm trying to abstract out pieces of a bigger problem one at a time
here. So originally I had a Trigger and Check constraint both on the
same table, and I was under the impression that privileges would only
be checked during "creation" of the trigger & constraint against the
owner of the objects, but not on runtime against the executor role.
This worked out fine for sometime. Then I wanted to tighten up the
security of my DB and introduced this DDL statement at the very
beginning of deployment after reading about it in a stack-overflow
answer.

ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM PUBLIC;

As I understood, this is supposed to revoke all grants on functions
created from that point onwards from the "PUBLIC" user role which all
other roles inherit. And now started my confusion. After introducing
this line early in my deployment script, the CHECK constraint
functions were now giving me runtime exceptions with the text
"permission denied for function constraint_max_length". But the
triggers continued to execute just fine, even though both the trigger
function and the check constraint function existed in the same
"app_private" schema, and I had not granted anything special for the
trigger function.

So then I understood that PostgreSQL is checking some sort of runtime
privilege before calling the check constraint function which it's not
doing for the trigger function. And I'm really fine with that, but I
don't know why this behavior (checking permissions for check
constraint function) only happens when I have the above REVOKE
privilege statement, and not otherwise. I don't see how the PUBLIC
role can affect the check constraint function, as it's inside an
"app_private" schema, which is not granted to the executing user, then
that should be the error message (permission denied for schema
app_private). Also if I look at the opposite of this (without the
REVOKE ALL ON FUNCTIONS statement) then the schema where the CHECK
Constraint function existed didn't matter for this privilege check.

So I kind of get the feeling when it comes to CHECK constraint
functions, the schema of the function is never considered for any
privilege checks but just the function itself is checked. Thus I
posted the question here in the mailing list, where I'm trying to
understand exactly how the privileges are checked when executing a
CHECK Constraint function in PostgreSQL as it seems different from the
logic for Trigger Functions.

/Ruwan.


Reply | Threaded
Open this post in threaded view
|

Re: How do CHECK Constraint Function privileges work?

Ruwan Fernando
In reply to this post by Tom Lane-2
On Sun, Apr 5, 2020 at 9:12 PM Tom Lane <[hidden email]> wrote:

>
> "David G. Johnston" <[hidden email]> writes:
> > On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <[hidden email]> 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@...
- I guess it still works the same way :)

Thank you very much & Kind Regards,
/Ruwan