Re: How do CHECK Constraint Function privileges work?

On Sun, Apr 5, 2020 at 1:22 AM Ruwan Fernando <rutechs(at)gmail(dot)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.