let ALTER COLUMN SET DATA TYPE cope with POLICY dependency

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: let ALTER COLUMN SET DATA TYPE cope with POLICY dependency
Date: 2025-09-12 08:19:12
Message-ID: CACJufxE42vysVEDEmaoBGmGYLZTCgUAwh_h-c9dcSLDtD5jE3g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.

in [1],
RememberAllDependentForRebuilding
/*
* A policy can depend on a column because the column is
* specified in the policy's USING or WITH CHECK qual
* expressions. It might be possible to rewrite and recheck
* the policy expression, but punt for now. It's certainly
* easy enough to remove and recreate the policy; still, FIXME
* someday.
*/
After 11 year, I am trying to allow column type changes to cope with
security policy dependencies.

CREATE TABLE s (a int, b int);
CREATE POLICY p2 ON s USING (s.b = 1);
--master branch will result error
ALTER TABLE s ALTER COLUMN b SET DATA TYPE INT8;
ERROR: cannot alter type of a column used in a policy definition
DETAIL: policy p2 on table s depends on column "b"

with the attached patch, ALTER TABLE SET DATA TYPE can cope with columns that
have associated security policy.
The above ALTER TABLE SET DATA TYPE will just work fine.
The code roughly follows how statistics are recreated after a column
data type change.

Currently table rewrite does not recheck the policy expression, for example:
RESET SESSION AUTHORIZATION;
CREATE USER regress_rls_alice NOLOGIN;
GRANT ALL ON SCHEMA public to public;
DROP TABLE IF EXISTS R1;
SET row_security = on;
begin;
set role regress_rls_alice;
CREATE TABLE r1 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
INSERT INTO r1 VALUES (1), (2), (4);
CREATE POLICY p0 ON r1 USING (true);
CREATE POLICY p1 ON r1 AS RESTRICTIVE USING (b > 10);
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
commit;
set role regress_rls_alice;
INSERT INTO r1 VALUES (0); -- Should fail p1
ALTER TABLE r1 ALTER COLUMN b SET EXPRESSION AS (-1); --OK

so i guess ALTER TABLE SET DATA TYPE, table rewrite no checking policy
should be fine?

[1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=143b39c1855f8a22f474f20354ee5ee5d2f4d266

Attachment Content-Type Size
v1-0001-let-ALTER-COLUMN-SET-DATA-TYPE-cope-with-POLICY-dependency.patch text/x-patch 28.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2025-09-12 08:42:31 Re: Conflict detection for update_deleted in logical replication
Previous Message Holger Hoffstätte 2025-09-12 07:47:23 Re: [PATCH] jit: fix build with LLVM-21