Thread: How do CHECK Constraint Function privileges work?
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";
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;
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));
);
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;
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.
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 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.
"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
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.
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