How do CHECK Constraint Function privileges work?

From: Ruwan Fernando <rutechs(at)gmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: How do CHECK Constraint Function privileges work?
Date: 2020-04-05 08:22:03
Message-ID: CANYEAx8vZnN9eeFQfsiLGMi9NdCP0wUdriHTCGU-7jP0VmNKPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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";

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));
);

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.

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2020-04-05 14:37:26 Re: How do CHECK Constraint Function privileges work?
Previous Message Steve Midgley 2020-04-01 15:29:44 Re: FK constraint question