Hello hackers, When a role `xx` has `grant select (name) on items to xx;`, a generic table-level error is given:
select * from items; ERROR: permission denied for table items With this patch, we now give: select * from items; ERROR: permission denied for column "id" of relation "items" This only when the user has column-level privileges, if it doesn't have any the same regular table-level error is given. This makes the most sense and also keeps current tests mostly the same. * It also works for UPDATE and INSERT. * Clears the TODO mentioned on lines https://github.com/postgres/postgres/blob/45cdaf3665bedfbabb908bb84284f3db26781ad3/src/backend/executor/execMain.c#L691-L693 * This patch is on top of the patch mentioned on https://www.postgresql.org/message-id/CAGRrpzYP%2B3zEk__KZu-a5uWySfwgRFk6eoPXKrA5AdtBTXR%3Dng%40mail.gmail.com, which refactors the code to make it simpler to review. Best regards, Steve
From 36a52a9ac00d62b8640ea04cecbad578350b6e28 Mon Sep 17 00:00:00 2001 From: steve-chavez <[email protected]> Date: Tue, 24 Mar 2026 16:40:01 -0500 Subject: [PATCH] Report column-level error when lacking privilege When a role `xx` has `grant select (name) on items to xx;`, a generic table-level error is given: select * from items; ERROR: permission denied for table items With this patch, we now give: select * from items; ERROR: permission denied for column "id" of relation "items" This only when the user has column-level privileges, if it doesn't have any the same regular table-level error is given. This makes the most sense and also keeps current tests mostly the same. * It also works for UPDATE and INSERT. * Clears the TODO mentioned on lines https://github.com/postgres/postgres/blob/45cdaf3665bedfbabb908bb84284f3db26781ad3/src/backend/executor/execMain.c#L691-L693 --- src/backend/executor/execMain.c | 51 ++++++--- src/backend/optimizer/plan/planner.c | 17 ++- src/include/executor/executor.h | 3 +- src/test/regress/expected/copy2.out | 6 +- .../regress/expected/generated_stored.out | 2 +- .../regress/expected/generated_virtual.out | 2 +- src/test/regress/expected/privileges.out | 104 +++++++++--------- src/test/regress/expected/updatable_views.out | 88 +++++++-------- 8 files changed, 154 insertions(+), 119 deletions(-) diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index dbbf6eb89ba..6f9ccd4930b 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -88,7 +88,8 @@ static void ExecutePlan(QueryDesc *queryDesc, DestReceiver *dest); static bool ExecCheckPermissionsModified(Oid relOid, Oid userid, Bitmapset *modifiedCols, - AclMode requiredPerms); + AclMode requiredPerms, + AttrNumber *missingColumn); static void ExecCheckXactReadOnly(PlannedStmt *plannedstmt); static void EvalPlanQualStart(EPQState *epqstate, Plan *planTree); static void ReportNotNullViolationError(ResultRelInfo *resultRelInfo, @@ -621,15 +622,26 @@ ExecCheckPermissions(List *rangeTable, List *rteperminfos, foreach(l, rteperminfos) { RTEPermissionInfo *perminfo = lfirst_node(RTEPermissionInfo, l); + AttrNumber missingColumn = InvalidAttrNumber; Assert(OidIsValid(perminfo->relid)); - result = ExecCheckOneRelPerms(perminfo); + result = ExecCheckOneRelPerms(perminfo, &missingColumn); if (!result) { if (ereport_on_violation) - aclcheck_error(ACLCHECK_NO_PRIV, - get_relkind_objtype(get_rel_relkind(perminfo->relid)), - get_rel_name(perminfo->relid)); + { + if (AttributeNumberIsValid(missingColumn)) + aclcheck_error_col(ACLCHECK_NO_PRIV, + OBJECT_COLUMN, + get_rel_name(perminfo->relid), + get_attname(perminfo->relid, + missingColumn, + false)); + else + aclcheck_error(ACLCHECK_NO_PRIV, + get_relkind_objtype(get_rel_relkind(perminfo->relid)), + get_rel_name(perminfo->relid)); + } return false; } } @@ -643,9 +655,11 @@ ExecCheckPermissions(List *rangeTable, List *rteperminfos, /* * ExecCheckOneRelPerms * Check access permissions for a single relation. + * If the user has column-level privileges, missingColumn will be set + * so the caller can give a specific error message. */ bool -ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) +ExecCheckOneRelPerms(RTEPermissionInfo *perminfo, AttrNumber *missingColumn) { AclMode requiredPerms; AclMode relPerms; @@ -656,6 +670,9 @@ ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) requiredPerms = perminfo->requiredPerms; Assert(requiredPerms != 0); + if (missingColumn) + *missingColumn = InvalidAttrNumber; + /* * userid to check as: current user unless we have a setuid indication. * @@ -685,30 +702,29 @@ ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) /* * Check to see if we have the needed privileges at column level. - * - * Note: failures just report a table-level error; it would be nicer - * to report a column-level error if we have some but not all of the - * column privileges. */ if (remainingPerms & ACL_SELECT && !ExecCheckPermissionsModified(relOid, userid, perminfo->selectedCols, - ACL_SELECT)) + ACL_SELECT, + missingColumn)) return false; if (remainingPerms & ACL_INSERT && !ExecCheckPermissionsModified(relOid, userid, perminfo->insertedCols, - ACL_INSERT)) + ACL_INSERT, + missingColumn)) return false; if (remainingPerms & ACL_UPDATE && !ExecCheckPermissionsModified(relOid, userid, perminfo->updatedCols, - ACL_UPDATE)) + ACL_UPDATE, + missingColumn)) return false; } return true; @@ -721,7 +737,8 @@ ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) */ static bool ExecCheckPermissionsModified(Oid relOid, Oid userid, Bitmapset *modifiedCols, - AclMode requiredPerms) + AclMode requiredPerms, + AttrNumber *missingColumn) { int col = -1; @@ -763,7 +780,13 @@ ExecCheckPermissionsModified(Oid relOid, Oid userid, Bitmapset *modifiedCols, { if (pg_attribute_aclcheck(relOid, attno, userid, requiredPerms) != ACLCHECK_OK) + { + /* set the missing column only in case the user has column-level privileges */ + if (missingColumn && pg_attribute_aclcheck_all(relOid, userid, requiredPerms, + ACLMASK_ANY) == ACLCHECK_OK) + *missingColumn = attno; return false; + } } } return true; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index d19800ad6a5..54ccd5a372b 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -959,12 +959,23 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, { RTEPermissionInfo *perminfo; bool result; + AttrNumber missingColumn = InvalidAttrNumber; perminfo = getRTEPermissionInfo(parse->rteperminfos, rte); - result = ExecCheckOneRelPerms(perminfo); + result = ExecCheckOneRelPerms(perminfo, &missingColumn); if (!result) - aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_VIEW, - get_rel_name(perminfo->relid)); + { + if (AttributeNumberIsValid(missingColumn)) + aclcheck_error_col(ACLCHECK_NO_PRIV, + OBJECT_COLUMN, + get_rel_name(perminfo->relid), + get_attname(perminfo->relid, + missingColumn, + false)); + else + aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_VIEW, + get_rel_name(perminfo->relid)); + } } } diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 07f4b1f7490..240a84d0698 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -246,7 +246,8 @@ extern void standard_ExecutorEnd(QueryDesc *queryDesc); extern void ExecutorRewind(QueryDesc *queryDesc); extern bool ExecCheckPermissions(List *rangeTable, List *rteperminfos, bool ereport_on_violation); -extern bool ExecCheckOneRelPerms(RTEPermissionInfo *perminfo); +extern bool ExecCheckOneRelPerms(RTEPermissionInfo *perminfo, + AttrNumber *missingColumn); extern void CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation, OnConflictAction onConflictAction, List *mergeActions); diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 01101c71051..85168560ee3 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -720,12 +720,12 @@ RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION regress_rls_copy_user_colperms; -- attempt all columns (should fail) COPY rls_t1 TO stdout; -ERROR: permission denied for table rls_t1 +ERROR: permission denied for column "c" of relation "rls_t1" COPY rls_t1 (a, b, c) TO stdout; -ERROR: permission denied for table rls_t1 +ERROR: permission denied for column "c" of relation "rls_t1" -- try to copy column with no privileges (should fail) COPY rls_t1 (c) TO stdout; -ERROR: permission denied for table rls_t1 +ERROR: permission denied for column "c" of relation "rls_t1" -- subset of columns (should succeed) COPY rls_t1 (a) TO stdout; 2 diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index 2385573d913..f9d321ce846 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -621,7 +621,7 @@ INSERT INTO gtest12 VALUES (1, 10), (2, 20); GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11; SET ROLE regress_user11; SELECT a, b FROM gtest11; -- not allowed -ERROR: permission denied for table gtest11 +ERROR: permission denied for column "b" of relation "gtest11" SELECT a, c FROM gtest11; -- allowed a | c ---+---- diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 9365fd3c57b..311c314c160 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -615,7 +615,7 @@ DETAIL: Virtual generated columns that make use of user-defined functions are n --GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11; SET ROLE regress_user11; SELECT a, b FROM gtest11; -- not allowed -ERROR: permission denied for table gtest11 +ERROR: permission denied for column "b" of relation "gtest11" SELECT a, c FROM gtest11; -- allowed a | c ---+---- diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 7069e9febb8..57026ba3ae0 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -761,7 +761,7 @@ SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 4)); INSERT INTO atest5 VALUES (1,2,3); SET SESSION AUTHORIZATION regress_priv_user4; SELECT * FROM atest5; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT one FROM atest5; -- ok one ----- @@ -771,13 +771,13 @@ SELECT one FROM atest5; -- ok COPY atest5 (one) TO stdout; -- ok 1 SELECT two FROM atest5; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" COPY atest5 (two) TO stdout; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT atest5 FROM atest5; -- fail ERROR: permission denied for table atest5 COPY atest5 (one,two) TO stdout; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT 1 FROM atest5; -- ok ?column? ---------- @@ -791,13 +791,13 @@ SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok (1 row) SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok @@ -813,39 +813,39 @@ SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok (1 row) SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT * FROM (atest5 a JOIN atest5 b USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT a.* FROM (atest5 a JOIN atest5 b USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)); -- fail ERROR: permission denied for table atest5 SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SELECT 1 FROM atest5 WHERE two = 2; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT * FROM atest1, atest5; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT atest1.* FROM atest1, atest5; -- ok a | b ---+----- @@ -861,7 +861,7 @@ SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok (2 rows) SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok a | b | one ---+-----+----- @@ -870,12 +870,12 @@ SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); - (2 rows) SELECT one, two FROM atest5; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (one,two) ON atest6 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still -ERROR: permission denied for table atest5 +ERROR: permission denied for column "two" of relation "atest5" SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (two) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; @@ -887,23 +887,23 @@ SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now -- test column-level privileges for INSERT and UPDATE INSERT INTO atest5 (two) VALUES (3); -- ok COPY atest5 FROM stdin; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" COPY atest5 (two) FROM stdin; -- ok INSERT INTO atest5 (three) VALUES (4); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" INSERT INTO atest5 VALUES (5,5,5); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" UPDATE atest5 SET three = 10; -- ok UPDATE atest5 SET one = 8; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" UPDATE atest5 SET three = 5, one = 2; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" -- Check that column level privs are enforced in RETURNING -- Ok. INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; -- Error. No SELECT on column three. INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three; -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" -- Ok. May SELECT on column "one": INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one; one @@ -916,11 +916,11 @@ INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RE INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; -- Error. No select rights on three INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" -- Check that column level privileges are enforced for ON CONFLICT ... WHERE -- Ok. we may select one INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.one = 1 RETURNING atest5.two; @@ -931,7 +931,7 @@ INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.one -- Error. No select rights on three INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.three = 1 RETURNING atest5.two; -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" -- Check that ON CONFLICT ... SELECT FOR UPDATE/SHARE requires an updatable column SET SESSION AUTHORIZATION regress_priv_user1; REVOKE UPDATE (three) ON atest5 FROM regress_priv_user4; @@ -949,14 +949,14 @@ INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURN -- Check that the columns in the inference require select privileges INSERT INTO atest5(four) VALUES (4); -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "four" of relation "atest5" SET SESSION AUTHORIZATION regress_priv_user1; GRANT INSERT (four) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT) -ERROR: permission denied for table atest5 +ERROR: permission denied for column "four" of relation "atest5" INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT) -ERROR: permission denied for table atest5 +ERROR: permission denied for column "four" of relation "atest5" INSERT INTO atest5(four) VALUES (4); -- ok SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (four) ON atest5 TO regress_priv_user4; @@ -968,9 +968,9 @@ REVOKE ALL (one) ON atest5 FROM regress_priv_user4; GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT one FROM atest5; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" UPDATE atest5 SET one = 1; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" SELECT atest6 FROM atest6; -- ok atest6 -------- @@ -997,21 +997,21 @@ WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); -ERROR: permission denied for table msource +ERROR: permission denied for column "b" of relation "msource" -- fail (s.b used in the INSERTed values) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = 'x' WHEN NOT MATCHED THEN INSERT VALUES (a, b); -ERROR: permission denied for table msource +ERROR: permission denied for column "b" of relation "msource" -- fail (s.b used in the WHEN quals) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED AND s.b = 'x' THEN UPDATE SET b = 'x' WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); -ERROR: permission denied for table msource +ERROR: permission denied for column "b" of relation "msource" -- this should be ok since only s.a is accessed BEGIN; MERGE INTO mtarget t USING msource s ON t.a = s.a @@ -1040,21 +1040,21 @@ WHEN MATCHED THEN UPDATE SET b = t.b WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); -ERROR: permission denied for table mtarget +ERROR: permission denied for column "b" of relation "mtarget" -- fail (no UPDATE on t.a) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b, a = t.a + 1 WHEN NOT MATCHED THEN INSERT VALUES (a, b); -ERROR: permission denied for table mtarget +ERROR: permission denied for column "a" of relation "mtarget" -- fail (no SELECT on t.b) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED AND t.b IS NOT NULL THEN UPDATE SET b = s.b WHEN NOT MATCHED THEN INSERT VALUES (a, b); -ERROR: permission denied for table mtarget +ERROR: permission denied for column "b" of relation "mtarget" -- ok BEGIN; MERGE INTO mtarget t USING msource s ON t.a = s.a @@ -1156,7 +1156,7 @@ SET SESSION AUTHORIZATION regress_priv_user4; SELECT atest6 FROM atest6; -- fail ERROR: permission denied for table atest6 SELECT one FROM atest5 NATURAL JOIN atest6; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 DROP COLUMN three; SET SESSION AUTHORIZATION regress_priv_user4; @@ -1180,7 +1180,7 @@ SELECT 1 FROM atest6; -- fail ERROR: permission denied for table atest6 SET SESSION AUTHORIZATION regress_priv_user3; DELETE FROM atest5 WHERE one = 1; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "one" of relation "atest5" DELETE FROM atest5 WHERE two = 2; -- ok -- check inheritance cases SET SESSION AUTHORIZATION regress_priv_user1; @@ -1211,7 +1211,7 @@ SELECT tableoid FROM atestp2; -- ok (0 rows) SELECT fy FROM atestc; -- fail -ERROR: permission denied for table atestc +ERROR: permission denied for column "fy" of relation "atestc" SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; @@ -3202,7 +3202,7 @@ select * from graph_table (ptg1 match (a is atest5) COLUMNS (a.four)) limit 0; - (0 rows) select * from graph_table (ptg1 match (v is lttc) COLUMNS (v.lttck)) limit 0; -- fail -ERROR: permission denied for table atest5 +ERROR: permission denied for column "three" of relation "atest5" -- access property graph through security definer view set session role regress_priv_user4; create view atpgv1 as select * from graph_table (ptg1 match (is atest1) COLUMNS (1 as value)) limit 0; diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 9cea538b8e8..490b3e3d5f7 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -1439,23 +1439,23 @@ MERGE INTO rw_view2 t ERROR: permission denied for table base_tbl UPDATE base_tbl SET a=a, c=c; -- ok UPDATE base_tbl SET b=b; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view1 SET bb=bb, cc=cc; -- ok UPDATE rw_view1 SET aa=aa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET aa=aa, cc=cc; -- ok UPDATE rw_view2 SET bb=bb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb, cc = cc; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa, cc = cc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" DELETE FROM base_tbl; -- not allowed ERROR: permission denied for table base_tbl DELETE FROM rw_view1; -- not allowed @@ -1724,20 +1724,20 @@ SELECT * FROM rw_view1; -- ok UPDATE base_tbl SET a=a, c=c; -- ok UPDATE base_tbl SET b=b; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view1 SET cc=cc; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET cc = cc; -- ok UPDATE rw_view1 SET aa=aa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view1 SET bb=bb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" SET SESSION AUTHORIZATION regress_view_user1; GRANT INSERT, DELETE ON base_tbl TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; @@ -1812,12 +1812,12 @@ SELECT * FROM rw_view1; -- ok UPDATE rw_view1 SET aa=aa, bb=bb; -- ok UPDATE rw_view1 SET cc=cc; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "c" of relation "base_tbl" MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa, bb = bb; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET cc = cc; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "c" of relation "base_tbl" SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for view rw_view1 @@ -1864,30 +1864,30 @@ SELECT * FROM rw_view2; -- ok (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl @@ -1905,16 +1905,16 @@ SELECT * FROM rw_view2; -- ok (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok RESET SESSION AUTHORIZATION; @@ -1935,16 +1935,16 @@ SELECT * FROM rw_view2; -- ok (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok SET SESSION AUTHORIZATION regress_view_user3; @@ -1955,16 +1955,16 @@ SELECT * FROM rw_view2; -- ok (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok RESET SESSION AUTHORIZATION; @@ -1973,14 +1973,14 @@ SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl @@ -1995,16 +1995,16 @@ SELECT * FROM rw_view2; -- ok (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" UPDATE rw_view2 SET bbb=bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "aa" of relation "rw_view1" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "b" of relation "base_tbl" MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok RESET SESSION AUTHORIZATION; @@ -3835,20 +3835,20 @@ grant update (bb) on rw_view1 to regress_view_user2; set session authorization regress_view_user2; insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do update set bb = excluded.cc; -- Not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "cc" of relation "rw_view1" insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "cc" of relation "rw_view1" insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do update set bb = excluded.bb; -- OK insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set cc = 3.0; -- Not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "cc" of relation "rw_view1" insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do select for update returning cc; -- Not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "cc" of relation "rw_view1" insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do select for update returning aa, bb; aa | bb @@ -3871,10 +3871,10 @@ set session authorization regress_view_user2; create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- Not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "c" of relation "base_tbl" insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do select returning 1; -- Not allowed -ERROR: permission denied for table base_tbl +ERROR: permission denied for column "c" of relation "base_tbl" create view rw_view3 as select b as bb, a as aa from base_tbl; insert into rw_view3 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- OK @@ -3896,10 +3896,10 @@ set session authorization regress_view_user2; create view rw_view4 as select aa, bb, cc FROM rw_view1; insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- Not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "cc" of relation "rw_view1" insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do select returning 1; -- Not allowed -ERROR: permission denied for view rw_view1 +ERROR: permission denied for column "cc" of relation "rw_view1" create view rw_view5 as select aa, bb FROM rw_view1; insert into rw_view5 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- OK -- 2.40.1
From 6111d2e02487e6de6726c6a5fe2746ce7f9d559f Mon Sep 17 00:00:00 2001 From: steve-chavez <[email protected]> Date: Mon, 23 Mar 2026 17:08:41 -0500 Subject: [PATCH] refactor ExecCheckPermissionsModified for ACL_SELECT Currently the code on ExecCheckOneRelPerms duplicates the logic in ExecCheckPermissionsModified. This change accommodates ExecCheckPermissionsModified to handle ACL_SELECT and makes ExecCheckOneRelPerms reuse code. It also merges similar comments. Main benefit is that it reduces LOCs and centralizes column privilege logic. --- src/backend/executor/execMain.c | 70 +++++++++++---------------------- 1 file changed, 24 insertions(+), 46 deletions(-) diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 58b84955c2b..c1cc8251186 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -676,8 +676,6 @@ ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) remainingPerms = requiredPerms & ~relPerms; if (remainingPerms != 0) { - int col = -1; - /* * If we lack any permissions that exist only as relation permissions, * we can fail straight away. @@ -692,45 +690,13 @@ ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) * to report a column-level error if we have some but not all of the * column privileges. */ - if (remainingPerms & ACL_SELECT) - { - /* - * When the query doesn't explicitly reference any columns (for - * example, SELECT COUNT(*) FROM table), allow the query if we - * have SELECT on any column of the rel, as per SQL spec. - */ - if (bms_is_empty(perminfo->selectedCols)) - { - if (pg_attribute_aclcheck_all(relOid, userid, ACL_SELECT, - ACLMASK_ANY) != ACLCHECK_OK) - return false; - } - - while ((col = bms_next_member(perminfo->selectedCols, col)) >= 0) - { - /* bit #s are offset by FirstLowInvalidHeapAttributeNumber */ - AttrNumber attno = col + FirstLowInvalidHeapAttributeNumber; - - if (attno == InvalidAttrNumber) - { - /* Whole-row reference, must have priv on all cols */ - if (pg_attribute_aclcheck_all(relOid, userid, ACL_SELECT, - ACLMASK_ALL) != ACLCHECK_OK) - return false; - } - else - { - if (pg_attribute_aclcheck(relOid, attno, userid, - ACL_SELECT) != ACLCHECK_OK) - return false; - } - } - } + if (remainingPerms & ACL_SELECT && + !ExecCheckPermissionsModified(relOid, + userid, + perminfo->selectedCols, + ACL_SELECT)) + return false; - /* - * Basically the same for the mod columns, for both INSERT and UPDATE - * privilege as specified by remainingPerms. - */ if (remainingPerms & ACL_INSERT && !ExecCheckPermissionsModified(relOid, userid, @@ -750,7 +716,7 @@ ExecCheckOneRelPerms(RTEPermissionInfo *perminfo) /* * ExecCheckPermissionsModified - * Check INSERT or UPDATE access permissions for a single relation (these + * Check SELECT, INSERT or UPDATE access permissions for a single relation (these * are processed uniformly). */ static bool @@ -760,9 +726,11 @@ ExecCheckPermissionsModified(Oid relOid, Oid userid, Bitmapset *modifiedCols, int col = -1; /* - * When the query doesn't explicitly update any columns, allow the query - * if we have permission on any column of the rel. This is to handle - * SELECT FOR UPDATE as well as possible corner cases in UPDATE. + * When the query doesn't explicitly reference any columns (for + * example, SELECT COUNT(*) FROM table or INSERT DEFAULT VALUES), + * allow the query if we have permission on any column of the rel, as per SQL spec. + * + * This handles SELECT FOR UPDATE as well as possible corner cases in UPDATE. */ if (bms_is_empty(modifiedCols)) { @@ -776,10 +744,20 @@ ExecCheckPermissionsModified(Oid relOid, Oid userid, Bitmapset *modifiedCols, /* bit #s are offset by FirstLowInvalidHeapAttributeNumber */ AttrNumber attno = col + FirstLowInvalidHeapAttributeNumber; + /* Whole-row reference, must have priv on all cols */ if (attno == InvalidAttrNumber) { - /* whole-row reference can't happen here */ - elog(ERROR, "whole-row update is not implemented"); + + /* In the case of SELECT * we have to check for all column permissions */ + if (requiredPerms == ACL_SELECT) + { + if (pg_attribute_aclcheck_all(relOid, userid, requiredPerms, + ACLMASK_ALL) != ACLCHECK_OK) + return false; + } + else + /* whole-row reference can't happen here */ + elog(ERROR, "whole-row update is not implemented"); } else { -- 2.40.1
