

# 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
<a name="AuroraPostgreSQL.Security.DynamicMasking.BestPractices.architecture"></a>

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.  

```sql
-- 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.  

```sql
-- 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
<a name="AuroraPostgreSQL.Security.DynamicMasking.BestPractices.MaskingDevelopment"></a>

Follow these three practices when developing masking functions:
+ **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.
+ **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.

The following example demonstrates all three practices:

```sql
-- 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
<a name="AuroraPostgreSQL.Security.DynamicMasking.BestPractices.CursorBehavior"></a>

`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**  

```sql
-- 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
<a name="AuroraPostgreSQL.Security.DynamicMasking.BestPractices.DMLTriggerBehavior"></a>

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 INSERT` trigger only has new versions of the rows and empty old versions because there is no old version to refer.  
`pg_columnmask` does 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.  

```sql
-- 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.  

```sql
-- 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 OF` triggers 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.  

```sql
-- 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_users`
+ Type: `boolean`
+ Default: `false` (triggers are allowed to be executed)
Prevents trigger execution on masked tables for masked users when set to TRUE. `pg_columnmask` runs through the error.  
**Second GUC: Trigger firing restriction on views with masked tables**  
Specifications:  
+ Name: `pgcolumnmask.restrict_iot_triggers_for_masked_users`
+ Type: `boolean`
+ Default: `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.