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,
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.