How do CHECK Constraint Function privileges work?
От | Ruwan Fernando |
---|---|
Тема | How do CHECK Constraint Function privileges work? |
Дата | |
Msg-id | CANYEAx8vZnN9eeFQfsiLGMi9NdCP0wUdriHTCGU-7jP0VmNKPA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: How do CHECK Constraint Function privileges work?
|
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: