Best practices for secure pg_columnmask implementation
The following section provides security best practices for implementing pg_columnmask in your Aurora PostgreSQL environment.
Follow these recommendations to:
Establish a secure role-based access control architecture
Develop masking functions that prevent unintended data disclosure
Understand and control trigger behavior with masked data
Understand cursor behavior and the masking resolution window
Role-based security architecture
Define a role hierarchy to implement access controls in your database. Aurora PostgreSQL pg_columnmask augments these
controls by providing an additional layer for fine-grained data masking within those roles.
Create dedicated roles that align with organizational functions rather than granting permissions to individual users. This approach provides better auditability and simplifies permission management as your organizational structure evolves.
Example of creating an organizational role hierarchy
The following example creates an organizational role hierarchy with dedicated roles for different functions, then assigns individual users to the appropriate roles. In this example, organizational roles (analyst_role, support_role) are created first, then individual users are granted membership in these roles. This structure allows you to manage permissions at the role level rather than for each individual user.
-- Create organizational role hierarchy CREATE ROLE data_admin_role; CREATE ROLE security_admin_role; CREATE ROLE analyst_role; CREATE ROLE support_role; CREATE ROLE developer_role; -- Specify security_admin_role as masking policy manager in the DB cluster parameter -- group pgcolumnmask.policy_admin_rolname = 'security_admin_role' -- Create specific users and assign to appropriate roles CREATE USER security_manager; CREATE USER data_analyst1, data_analyst2; CREATE USER support_agent1, support_agent2; GRANT security_admin_role TO security_manager; GRANT analyst_role TO data_analyst1, data_analyst2; GRANT support_role TO support_agent1, support_agent2;
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.
-- Grant specific table permissions rather than schema-wide access GRANT SELECT ON sensitive_data.customers TO analyst_role; GRANT SELECT ON sensitive_data.transactions TO analyst_role; -- Do not grant: GRANT ALL ON SCHEMA sensitive_data TO analyst_role;
Policy administrators require USAGE privileges on schemas where they manage masking policies.
Grant these privileges selectively, following the principle of least privilege.
Conduct regular reviews of schema access permissions to ensure only authorized personnel maintain policy management capabilities.
The policy admin role parameter configuration is restricted to database administrators only. This parameter cannot be modified at the database or session level, preventing unprivileged users from overriding policy admin assignments. This restriction ensures that masking policy control remains centralized and secure.
Assign the policy admin role to specific individuals rather than groups. This targeted approach ensures selective access to masking policy management, as policy administrators have the ability to mask all tables within the database.
Secure masking function development
Follow these three practices when developing masking functions:
-
Early binding – Use
BEGIN ATOMICsyntax to enable compile-time validation and dependency tracking. This prevents late binding issues such as search path modification at runtime. -
Schema qualification – Schema-qualify all object references to make functions immune to search path changes.
-
Input validation – Use
STRICTto return NULL for NULL input, and validate input formats to handle edge cases.
The following example demonstrates all three practices:
-- Secure masking function combining early binding, schema qualification, -- and input validation CREATE OR REPLACE FUNCTION data_masking.secure_mask_phone(phone_number TEXT) RETURNS TEXT LANGUAGE SQL IMMUTABLE PARALLEL SAFE STRICT BEGIN ATOMIC -- STRICT returns NULL automatically for NULL input SELECT CASE -- 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)) END; END;
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 HOLDcursors surviveCOMMITand 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 | john@example.com -- 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 | jane@example.com -- 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 | XXXX@XXXXXXX.com
DML Triggers behavior with pg_columnmask
For table triggers, transition tables will be fully unmasked. For view triggers(IOT), transition tables will be masked according to the current user's view permissions.
- Table triggers with pg_columnmask
Triggers are passed a transition table which contains the old and new version of the rows modified by the firing DML query. Depending upon when the trigger is fired, Aurora PostgreSQL populates the old and new rows. For example, a
BEFORE INSERTtrigger only has new versions of the rows and empty old versions because there is no old version to refer.pg_columnmaskdoes not mask transition tables inside triggers on tables. Triggers can use masked columns inside their body and it sees unmasked data. The trigger creator should make sure how the trigger gets executed for an user. The following example works correctly in this case.-- Example for table trigger uses masked column in its definition -- Create a table and insert some rows CREATE TABLE public.credit_card_table ( name TEXT, credit_card_no VARCHAR(16), is_fraud BOOL ); INSERT INTO public.credit_card_table (name, credit_card_no, is_fraud) VALUES ('John Doe', '4532015112830366', false), ('Jane Smith', '5410000000000000', true), ('Brad Smith', '1234567891234567', true); -- Create a role which will see masked data and grant it privileges CREATE ROLE intern_user; GRANT SELECT, DELETE ON public.credit_card_table TO intern_user; -- Trigger which will silenty skip delete of non fraudelent credit cards CREATE OR REPLACE FUNCTION prevent_non_fraud_delete() RETURNS TRIGGER AS $$ BEGIN IF OLD.is_fraud = false THEN RETURN NULL; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER prevent_non_fraud_delete BEFORE DELETE ON credit_card_table FOR EACH ROW EXECUTE FUNCTION prevent_non_fraud_delete(); CREATE OR REPLACE FUNCTION public.return_false() RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE PARALLEL SAFE STRICT BEGIN ATOMIC SELECT false; END; -- A masking policy that masks both credit card number and is_fraud column. -- If we apply masking inside trigger then prevent_non_fraud_delete trigger will -- allow deleting more rows to masked user (even non fraud ones). CALL pgcolumnmask.create_masking_policy( 'mask_credit_card_no_&_is_fraud'::NAME, 'public.credit_card_table'::REGCLASS, JSON_BUILD_OBJECT('credit_card_no', 'pgcolumnmask.mask_text(credit_card_no)', 'is_fraud', 'public.return_false()')::JSONB, ARRAY['intern_user']::NAME[], 10::INT ); -- Test trigger behaviour using intern_user BEGIN; SET ROLE intern_user; -- credit card number & is_fraud is completely masked from intern_user SELECT * FROM public.credit_card_table; name | credit_card_no | is_fraud ------------+------------------+---------- John Doe | XXXXXXXXXXXXXXXX | f Jane Smith | XXXXXXXXXXXXXXXX | f Brad Smith | XXXXXXXXXXXXXXXX | f (3 rows) -- The delete trigger lets the intern user delete rows for Jane and Brad even though -- intern_user sees their is_fraud = false, but the table trigger works with original -- unmasked value DELETE FROM public.credit_card_table RETURNING *; name | credit_card_no | is_fraud ------------+------------------+---------- Jane Smith | XXXXXXXXXXXXXXXX | f Brad Smith | XXXXXXXXXXXXXXXX | f (2 rows) COMMIT;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.-- Example showing table trigger disclosing column value to current user CREATE OR REPLACE FUNCTION notice_trigger_func() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'Old credit card number was: %', OLD.credit_card_no; RAISE NOTICE 'New credit card number is %', NEW.credit_card_no; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER notice_trigger AFTER UPDATE ON public.credit_card_table FOR EACH ROW EXECUTE FUNCTION notice_trigger_func(); -- Grant update on column is_fraud to auditor role -- auditor will NOT HAVE PERMISSION TO READ DATA CREATE ROLE auditor; GRANT UPDATE (is_fraud) ON public.credit_card_table TO auditor; -- Also add auditor role to existing masking policy on credit card table CALL pgcolumnmask.alter_masking_policy( 'mask_credit_card_no_&_is_fraud'::NAME, 'public.credit_card_table'::REGCLASS, NULL::JSONB, ARRAY['intern_user', 'auditor']::NAME[], NULL::INT ); -- Log in as auditor -- [auditor] -- Update fails because the WHERE clause references a masked column UPDATE public.credit_card_table SET is_fraud = true WHERE credit_card_no = '4532015112830366'; ERROR: predicates on masked columns are not allowed HINT: Add the column to the predicate allow list using pgcolumnmask.alter_masking_policy. -- [auditor] -- But notice_trigger will still print the unmasked values even though -- current user does not have permission to select from public.credit_card_table UPDATE public.credit_card_table SET is_fraud = true; NOTICE: Old credit card number was: 4532015112830366 NOTICE: New credit card number is 4532015112830366 UPDATE 1- Triggers on views with pg_columnmask (Instead of triggers)
You can create
INSTEAD OFtriggers only on views in PostgreSQL. They are used for running DML statements on views that are not updatable. Transit tables are always masked inside instead of trigger (IOT), because the view and the base tables used inside the view query could have different owners. In which case, base tables might have some masking policies applicable on the view owner and the view owner must always see masked data from base tables inside its triggers. This is different from triggers on tables because in that case the trigger creator and the data inside the tables are owned by the same user which is not the case here.-- Create a view over credit card table CREATE OR REPLACE VIEW public.credit_card_view AS SELECT * FROM public.credit_card_table; -- Truncate credit card table and insert fresh data TRUNCATE TABLE public.credit_card_table; INSERT INTO public.credit_card_table (name, credit_card_no, is_fraud) VALUES ('John Doe', '4532015112830366', false), ('Jane Smith', '5410000000000000', true), ('Brad Smith', '1234567891234567', true); CREATE OR REPLACE FUNCTION public.print_changes() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'Old row: name=%, credit card number=%, is fraud=%', OLD.name, OLD.credit_card_no, OLD.is_fraud; RAISE NOTICE 'New row: name=%, credit card number=%, is fraud=%', NEW.name, NEW.credit_card_no, NEW.is_fraud; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER print_changes_trigger INSTEAD OF UPDATE ON public.credit_card_view FOR EACH ROW EXECUTE FUNCTION public.print_changes(); GRANT SELECT, UPDATE ON public.credit_card_view TO auditor; -- [auditor] -- Login as auditor role BEGIN; -- Any data coming out from the table will be masked in instead of triggers -- according to masking policies applicable to current user UPDATE public.credit_card_view SET name = CONCAT(name, '_new_name') RETURNING *; NOTICE: Old row: name=John Doe, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: New row: name=John Doe_new_name, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: Old row: name=Jane Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: New row: name=Jane Smith_new_name, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: Old row: name=Brad Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: New row: name=Brad Smith_new_name, credit card number=XXXXXXXXXXXXXXXX, is fraud=f name | credit_card_no | is_fraud ---------------------+------------------+---------- John Doe_new_name | XXXXXXXXXXXXXXXX | f Jane Smith_new_name | XXXXXXXXXXXXXXXX | f Brad Smith_new_name | XXXXXXXXXXXXXXXX | f -- Any new data going into the table using INSERT or UPDATE command will be unmasked UPDATE public.credit_card_view SET credit_card_no = '9876987698769876' RETURNING *; NOTICE: Old row: name=John Doe, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: New row: name=John Doe, credit card number=9876987698769876, is fraud=f NOTICE: Old row: name=Jane Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: New row: name=Jane Smith, credit card number=9876987698769876, is fraud=f NOTICE: Old row: name=Brad Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f NOTICE: New row: name=Brad Smith, credit card number=9876987698769876, is fraud=f name | credit_card_no | is_fraud ------------+------------------+---------- John Doe | 9876987698769876 | f Jane Smith | 9876987698769876 | f Brad Smith | 9876987698769876 | f COMMIT;- Database/User level GuCs to control triggers behavior
Two configuration parameters control trigger execution behavior for users with applicable masking policies. Use these parameters to prevent triggers from executing on masked tables or views when additional security restrictions are required. Both parameters are disabled by default, allowing triggers to execute normally.
First GUC: Trigger firing restriction on masked tables
Specifications:
Name:
pgcolumnmask.restrict_dml_triggers_for_masked_usersType:
booleanDefault:
false(triggers are allowed to be executed)
Prevents trigger execution on masked tables for masked users when set to TRUE.
pg_columnmaskruns through the error.Second GUC: Trigger firing restriction on views with masked tables
Specifications:
Name:
pgcolumnmask.restrict_iot_triggers_for_masked_usersType:
booleanDefault:
false(triggers are allowed to be executed)
Prevents trigger execution on views that include masked tables in their definition for masked users when set to TRUE.
These parameters operate independently and are configurable like standard database configuration parameters.