AWS AmazonRDS: Aurora PostgreSQL: document pg_columnmask cursor masking window
Summary
Reworks the dynamic masking best-practices page: adds a new 'Cursor behavior with pg_columnmask' section explaining that masking is resolved at DECLARE CURSOR time (so open/WITH HOLD cursors keep returning cleartext after a restrictive role is revoked), consolidates masking-function guidance into early binding, schema qualification and input validation (with a schema-qualified, STRICT example using pg_catalog functions), softens trigger-leak wording to 'disclosing', and updates trigger error/output examples (new 'predicates on masked columns are not allowed' hint).
Security assessment
The added cursor section documents a concrete data-disclosure weakness: masking is evaluated once at DECLARE, so revoking an exempt role (REVOKE cleartext_reader FROM data_analyst1) does not affect already-open cursors, which continue to return cleartext PII (names, emails) until the cursor is closed or re-declared. It also reinforces masking-function hardening (BEGIN ATOMIC early binding, schema-qualified object references to defeat search-path manipulation, STRICT/NULL handling) and reworded trigger examples that disclose unmasked credit card values via RAISE NOTICE, all of which are authorization/least-privilege and data-exposure guidance.
Evidence
+ -- Cursor still returns cleartext because masking was resolved at DECLARE time
Diff
diff --git a/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Security.DynamicMasking.BestPractices.md b/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Security.DynamicMasking.BestPractices.md index 715235a..16a5340 100644 --- a//AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Security.DynamicMasking.BestPractices.md +++ b//AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Security.DynamicMasking.BestPractices.md @@ -7 +7 @@ -Security architectureMasking function developmentDML trigger behavior +Security architectureMasking function developmentCursor behaviorDML trigger behavior @@ -15 +15 @@ The following section provides security best practices for implementing `pg_colu - * Develop masking functions that prevent security vulnerabilities + * Develop masking functions that prevent unintended data disclosure @@ -18,0 +19,2 @@ The following section provides security best practices for implementing `pg_colu + * Understand cursor behavior and the masking resolution window + @@ -28 +30 @@ Create dedicated roles that align with organizational functions rather than gran -###### Example of creating an organzational role heirarchy +###### Example of creating an organizational role hierarchy @@ -52 +54 @@ The following example creates an organizational role hierarchy with dedicated ro -Implement the principle of least privilege by granting only the minimum permissions necessary for each role. Avoid granting broad permissions that could be exploited if credentials are compromised. +Implement the principle of least privilege by granting only the minimum permissions necessary for each role. Avoid granting broad permissions that an unauthorized user could leverage to perform inappropriate actions if they gain access to credentials. @@ -68 +70 @@ Assign the policy admin role to specific individuals rather than groups. This ta -Develop masking functions using early binding semantics to ensure proper dependency tracking and prevent late binding vulnerabilities such as search path modification during runtime. It is recommended to use `BEGIN ATOMIC` syntax for SQL functions to enable compile-time validation (that is, early binding) and dependency management. +Follow these three practices when developing masking functions: @@ -69,0 +72 @@ Develop masking functions using early binding semantics to ensure proper depende + * **Early binding** – Use `BEGIN ATOMIC` syntax to enable compile-time validation and dependency tracking. This prevents late binding issues such as search path modification at runtime. @@ -71,12 +74,3 @@ Develop masking functions using early binding semantics to ensure proper depende - -- Example - Secure masking function with early binding - CREATE OR REPLACE FUNCTION secure_mask_ssn(input_ssn TEXT) - RETURNS TEXT - LANGUAGE SQL - IMMUTABLE PARALLEL SAFE STRICT - BEGIN ATOMIC - SELECT CASE - WHEN input_ssn IS NULL THEN NULL - WHEN length(input_ssn) < 4 THEN repeat('X', length(input_ssn)) - ELSE repeat('X', length(input_ssn) - 4) || right(input_ssn, 4) - END; - END; + * **Schema qualification** – Schema-qualify all object references to make functions immune to search path changes. + + * **Input validation** – Use `STRICT` to return NULL for NULL input, and validate input formats to handle edge cases. @@ -84 +77,0 @@ Develop masking functions using early binding semantics to ensure proper depende -Alternatively, create functions that are immune to search path changes by explicitly schema qualifying all object references, ensuring consistent behavior across different user sessions. @@ -87,16 +79,0 @@ Alternatively, create functions that are immune to search path changes by explic - -- Function immune to search path changes - CREATE OR REPLACE FUNCTION data_masking.secure_phone_mask(phone_number TEXT) - RETURNS TEXT - LANGUAGE SQL - IMMUTABLE PARALLEL SAFE STRICT - AS $$ - SELECT CASE - WHEN phone_number IS NULL THEN NULL - WHEN public.length(public.regexp_replace(phone_number, '[^0-9]', '', 'g')) < 10 THEN 'XXX-XXX-XXXX' - ELSE public.regexp_replace( - phone_number, - '([0-9]{3})[0-9]{3}([0-9]{4})', - public.concat('\1-XXX-\2') - ) - END; - $$; @@ -104 +81 @@ Alternatively, create functions that are immune to search path changes by explic -Implement input validation within masking functions to handle edge cases and prevent unexpected behavior. Always include NULL handling and validate input formats to ensure consistent masking behavior. +The following example demonstrates all three practices: @@ -107,2 +84,3 @@ Implement input validation within masking functions to handle edge cases and pre - -- Robust masking function with comprehensive input validation - CREATE OR REPLACE FUNCTION secure_mask_phone(phone_number TEXT) + -- Secure masking function combining early binding, schema qualification, + -- and input validation + CREATE OR REPLACE FUNCTION data_masking.secure_mask_phone(phone_number TEXT) @@ -112,0 +91 @@ Implement input validation within masking functions to handle edge cases and pre + -- STRICT returns NULL automatically for NULL input @@ -114,4 +93,6 @@ Implement input validation within masking functions to handle edge cases and pre - WHEN phone_number IS NULL THEN NULL - WHEN length(trim(phone_number)) = 0 THEN phone_number - WHEN length(regexp_replace(phone_number, '[^0-9]', '', 'g')) < 10 THEN 'XXX-XXX-XXXX' - ELSE regexp_replace(phone_number, '([0-9]{3})[0-9]{3}([0-9]{4})', '\1-XXX-\2') + -- Input validation: fully mask values with fewer than 10 digits + WHEN pg_catalog.length(pg_catalog.regexp_replace(phone_number, '[^0-9]', '', 'g')) < 10 + THEN 'XXX-XXX-XXXX' + -- Show only the last four digits; schema-qualified calls are not affected by search path changes + ELSE pg_catalog.concat('XXX-XXX-', + pg_catalog.right(pg_catalog.regexp_replace(phone_number, '[^0-9]', '', 'g'), 4)) @@ -120,0 +102,45 @@ Implement input validation within masking functions to handle edge cases and pre +## Cursor behavior with pg_columnmask + +`pg_columnmask` resolves masking at `DECLARE CURSOR` time, not on each `FETCH`. This is consistent with PostgreSQL semantics, where privileges and row-level security are evaluated once at `DECLARE`. + + * **Role changes after declaration** – A cursor's masking behavior is fixed at declaration time. Changes to role membership after declaration, whether granting a restrictive role or revoking an exempt role, do not affect data returned by that cursor. + + * **WITH HOLD cursors** – `WITH HOLD` cursors survive `COMMIT` and retain the masking context from declaration until the cursor is closed or the session ends. + + + + +Open cursors retain the masking context from declaration. Only newly declared cursors reflect current role membership. + +###### Example of cursor retaining cleartext after concurrent role revocation + + + -- Session 1 (data_analyst1): + BEGIN; + DECLARE customer_cursor CURSOR FOR SELECT id, name, email FROM customers; + + -- Cursor returns cleartext because data_analyst1 holds the cleartext_reader role + FETCH 1 FROM customer_cursor; + id | name | email + ----+----------+------------------ + 1 | John Doe | [email protected] + + -- Session 2 (security_admin): + REVOKE cleartext_reader FROM data_analyst1; + + -- Session 1 (data_analyst1): + -- Cursor still returns cleartext because masking was resolved at DECLARE time + FETCH 1 FROM customer_cursor; + id | name | email + ----+------------+------------------ + 2 | Jane Smith | [email protected] + + -- Only a newly declared cursor reflects the updated role membership + COMMIT; + BEGIN; + DECLARE customer_cursor CURSOR FOR SELECT id, name, email FROM customers; + FETCH 1 FROM customer_cursor; + id | name | email + ----+----------+------------------ + 1 | XXXXXXXX | [email protected] + @@ -214 +240 @@ Triggers are passed a transition table which contains the old and new version of -Trigger creator leaks unmasked data to user if they are not careful about the statements they use in their trigger body. For example using a `RAISE NOTICE ‘%’, masked_column;` prints the column to current user. +A trigger can unintentionally disclose unmasked data to the current user if the trigger creator is not careful about the statements they use in the trigger body. For example using a `RAISE NOTICE ‘%’, masked_column;` prints the column to current user. @@ -217,2 +243,2 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - -- Example showing table trigger leaking column value to current user - CREATE OR REPLACE FUNCTION leaky_trigger_func() + -- Example showing table trigger disclosing column value to current user + CREATE OR REPLACE FUNCTION notice_trigger_func() @@ -228 +254 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - CREATE TRIGGER leaky_trigger + CREATE TRIGGER notice_trigger @@ -231 +257 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - EXECUTE FUNCTION leaky_trigger_func(); + EXECUTE FUNCTION notice_trigger_func(); @@ -249 +275 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - -- Update will fail if trying to read data from the table + -- Update fails because the WHERE clause references a masked column @@ -253 +279,2 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - ERROR: permission denied for table cc_table + ERROR: predicates on masked columns are not allowed + HINT: Add the column to the predicate allow list using pgcolumnmask.alter_masking_policy. @@ -256 +283 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - -- But leaky update trigger will still print the entire row even though + -- But notice_trigger will still print the unmasked values even though @@ -259,2 +286,3 @@ Trigger creator leaks unmasked data to user if they are not careful about the st - NOTICE: Old credit_card_no was: 4532015112830366 - NOTICE: New credit_card_no is 4532015112830366 + NOTICE: Old credit card number was: 4532015112830366 + NOTICE: New credit card number is 4532015112830366 + UPDATE 1