Re: How do CHECK Constraint Function privileges work? - Mailing list pgsql-sql

On Sun, Apr 5, 2020 at 8:07 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <rutechs@gmail.com> wrote:
>>
>> I have created a test harness in a fresh DB where a role has access to 2 custom schemas, and have created a
constraintcheck 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
tableare 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.