---
name: databases-for-postgresql-howto-pganon
title: Data masking using PostgreSQL Anonymizer
description: Databases for PostgreSQL Anonymizer is a PostgreSQL extension designed to anonymize data by masking or replacing personally identifiable and sensitive business information in a Databases for PostgreSQL database.
last-updated: 2026-11-02
---

> ## Documentation Index
> The table of contents for this documentation set is at https://cloud.ibm.com/docs/databases-for-postgresql?format=markdown
> The index for all IBM Cloud docs is at: https://cloud.ibm.com/docs/llms.txt
> Use these files to discover more information as needed.

# Data masking using PostgreSQL Anonymizer
{: #data-masking}

Databases for PostgreSQL Anonymizer is a PostgreSQL extension designed to anonymize data by masking or replacing personally identifiable and sensitive business information in a Databases for PostgreSQL database.

The extension supports the following masking strategies, each suitable for different use cases:

- **Static masking**: Remove the PII according to the rules.
- **Dynamic masking**: Hide PII only for the masked users.
- **Anonymous dumps**: Simply export the masked data into an SQL file.
- **Generalization**: Replaces specific values with broader, less precise categories or range.

## Static masking
{: #static-masking}

Static masking is a data anonymization technique that irreversibly replaces sensitive information with synthetically generated,
format-preserving values. This process ensures the complete and permanent removal of the original data, thereby eliminating
the possibility of re-identification or reconstruction.
For more information, see [Static masking](https://postgresql-anonymizer.readthedocs.io/en/stable/tutorials/1-static_masking).

### Enabling static masking
{: #enabling-static-masking}

1. Install the Anonymizer Extension.

    ```sh
    CREATE EXTENSION IF NOT EXISTS anon;
    ```
    {: pre}

2. Initialize the Anonymizer.

    ```sh
    select public.run_anon_init();
    ```
    {: pre}

3. Define masking rules.

    ```sh
    SECURITY LABEL FOR anon
      ON COLUMN <table_name>.<column_name>
      IS 'MASKED WITH FUNCTION anon.<masking_function>()';
    ```
    {: pre}

4. Verify the applied masking rules.

    ```sh
    SELECT objoid::regclass, provider, label
    FROM pg_seclabel
    WHERE provider = 'anon' AND objoid = '<table_name>'::regclass;
    ```
    {: pre}

5. Apply the rules permanently. The following command replaces the original data in the specified table with the masked data.

    ```sh
    SELECT anon.anonymize_table('<table_name>');
    ```
    {: pre}


  - Static masking is a slow process. The principle of static masking is to update all lines of all tables containing at least one masked column. This means that PostgreSQL will rewrite all the data on disk. Depending on the database size, the hardware, and the instance configuration, it may be faster to export the anonymized data and reload it into the database.

  - To apply masking to newly inserted rows in a table, execute the following command.

    ```sh
    SELECT anon.anonymize_table('<table_name>');
    ```
    {: pre}

    This command ensures that any new data added to the table is masked according to the configured anonymization rules.
    {: note}

## Dynamic masking
{: #dynamic-masking}

Dynamic masking allows the database owner to obscure sensitive data for specific users,
while permitting authorized users to access and modify the original, unmasked values.
For more information, see [Dynamic masking](https://postgresql-anonymizer.readthedocs.io/en/stable/tutorials/2-dynamic_masking/).

### Enabling dynamic masking
{: #enabling-dynamic-masking}

1. Install the Anonymizer Extension.

    ```sh
    CREATE EXTENSION IF NOT EXISTS anon;
    ```
    {: pre}

2. Initialize the Anonymizer.

    ```sh
    select public.run_anon_init();
    ```
    {: pre}

3. Activate the dynamic masking engine.

    ```sh
    show anon.transparent_dynamic_masking;

    select public.enable_dynamic_masking ('<database_name>');
    ```
    {: pre}

    Restart your session if anon.transparent_dynamic_masking is showing as off.
    {: note}

4. Create the role for masked access.

    ```sh
    CREATE ROLE <role_name> LOGIN;
    ```
    {: pre}

5. Mark the role as a masked user.

    ```sh
      SECURITY LABEL FOR anon ON ROLE <role_name> IS 'MASKED';
    ```
    {: pre}

6. Grant permissions on the masking table to a role.

    ```sh
      grant select on <schema_name>.<table_name> to <role_name> ;
    ```
    {: pre}

7. Declare the masking rules.

    ```sh
    SECURITY LABEL FOR anon
      ON COLUMN <table_name>.<column_name>
      IS 'MASKED WITH FUNCTION anon.<masking_function>()';
    ```
    {: pre}

8. Verify the masking rules.

    ```sh
    SELECT objoid::regclass, provider, label
    FROM pg_seclabel
    WHERE provider = 'anon' AND objoid = '<table_name>'::regclass;
    ```
    {: pre}

9. Connect to the database by using the masked role, and query the table. The query returns the data masked according to the rules that you declared.

    ```sh
    select * from <table_name>;
    ```
    {: pre}

Notes:

- The anonymizer masks data based on who is querying.
- Masked roles should not be allowed to insert, update, or delete data.
- You can mask table in multiple schemas.
- A masking rule may break data integrity. For instance, you can mask a column having a UNIQUE constraint with the value NULL. It is up to you to decide whether or not the mask users need data integrity.
- Masked roles are not allowed to use EXPLAIN.

## Anonymous dumps
{: #anonymous-dumps}

An anonymous dump refers to the process of exporting a database that contains anonymized or de-identified data, rather than the original sensitive values. This is typically done to safely share, migrate, or analyze data without
exposing personally identifiable information (PII) or sensitive attributes. For more information, see [Anonymous dumps](https://postgresql-anonymizer.readthedocs.io/en/latest/anonymous_dumps/).

### Enabling anonymous dumps
{: #enabling-anonymous-dumps}

1. Install the Anonymizer Extension.

    ```sh
    CREATE EXTENSION IF NOT EXISTS anon;
    ```
    {: pre}

2. Initialize the Anonymizer.

    ```sh
    select public.run_anon_init();
    ```
    {: pre}

3. Create a dedicated schema for custom masking functions.

    ```sh
     CREATE SCHEMA IF NOT EXISTS <schema_name>;
    ```
    {: pre}


4. Create a role for exporting masked data.

    ```sh
     CREATE ROLE <role_name> LOGIN PASSWORD '<enter_password>';
    ```
    {: pre}

5. Enable masking for role.

    ```sh
    select  public.enable_masking_for_dump('<role_name>','<schema_name>');
    ```
    {: pre}

6. Define a custom masking function, as in the following example.

    ```sh
    CREATE OR REPLACE FUNCTION <schema_name>.remove_content(j JSONB)
    RETURNS JSONB
    AS $func$
      SELECT j - ARRAY['content']
    $func$
    LANGUAGE SQL;
    ```
    {: pre}

7. Create a masking rule.

    ```sh
    SECURITY LABEL FOR anon
      ON COLUMN <table_name>.<column_name>
      IS 'MASKED WITH FUNCTION <schema_name>.<custom_masking_function>()';
    ```
    {: pre}

8. Label the role as a masked role.

    ```sh
    SECURITY LABEL FOR anon ON ROLE <role_name> IS 'MASKED';
    ```
    {: pre}

9. Grant access to the masked schema.

    ```sh
    GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;
    ```
    {: pre}

10. Grant Select access on masked tables.

    ```sh
    grant select ON <schema_name>.<table_name> to <role_name>;
    ```
    {: pre}

11. Export the masked data by running `pg_dump` as the masked role.

    For more information, see [`pg_dump` commands](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-migrating&format=markdown).

    Run `pg_dump` as the masked role to export masked data. If the admin user runs `pg_dump`, the export contains the original, unmasked data.
    {: note}

## Generalization
{: #generalization}

The main idea of generalization is to blur the original data.
For more information, see [Generalization](https://postgresql-anonymizer.readthedocs.io/en/stable/tutorials/4-generalization/#).

### Enabling generalization
{: #enabling-generalization}

1. Install the anonymizer extension.

    ```sh
    CREATE EXTENSION IF NOT EXISTS anon;
    ```
    {: pre}

2. Initialize the anonymizer.

    ```sh
    select public.run_anon_init();
    ```
    {: pre}

3. Create a view using generalization functions.

    ```sh
    CREATE MATERIALIZED VIEW <view_name> AS
    SELECT
        anon.generalize_daterange(<date_column_1>, 'month') AS <generalized_date_1>,
        anon.generalize_daterange(<date_column_2>, 'month') AS <generalized_date_2>
    FROM <source_table>;
    ```
    {: pre}

4. Declare indirect identifiers.

    ```sh
    SECURITY LABEL FOR k_anonymity
      ON COLUMN <view_name>.<generalized_date_1> IS 'INDIRECT IDENTIFIER';
    
    SECURITY LABEL FOR k_anonymity
      ON COLUMN <view_name>.<generalized_date_2> IS 'INDIRECT IDENTIFIER';
    ```
    {: pre}

5. Grant access to k-anonymity.

    ```sh
    select public.k_anonymity();
    ```
    {: pre}

6. Run the k-anonymity check on the view or table.

    ```sh
    SELECT anon.k_anonymity('<view_name/table_name>');
    ```
    {: pre}


## Unmask a role
{: #unmask-role}

Use the following command to unmask a role.

```sh
SECURITY LABEL FOR anon ON ROLE <role_name> IS NULL;
```
{: pre}

## Disable masking rule
{: #disable-masking}

Use the following command to disable the masking rule for a column.

```sh
SECURITY LABEL FOR anon
  ON COLUMN <table_name>.<column_name>
  IS NULL;
```
{: pre}

## Upgrading PostgreSQL Anonymizer
{: #anon-upgrading}

PostgreSQL Anonymizer does not provide upgrade scripts, so `ALTER EXTENSION anon UPDATE` fails with the error `extension "anon" has no update path`. When your deployment provides a newer version of the extension, drop and re-create the extension in each database that uses it. Before you start, review the [PostgreSQL Anonymizer upgrade notes](https://postgresql-anonymizer.readthedocs.io/en/stable/UPGRADE/){: external} for the versions after the installed one.

1. Compare the installed version with the version that your deployment provides.

    ```sql
    SELECT e.extversion AS installed_version, a.default_version AS available_version
    FROM pg_catalog.pg_extension AS e
    JOIN pg_catalog.pg_available_extensions AS a ON a.name = e.extname
    WHERE e.extname = 'anon';
    ```
    {: pre}

    If both versions are the same, the database is up to date.

2. Drop the extension.

    ```sql
    DROP EXTENSION anon;
    ```
    {: pre}

    If views or other objects use `anon` functions, the command fails with the error `cannot drop extension anon because other objects depend on it`, and the error details list those objects. Save their definitions, drop them, and run the command again. Do not add `CASCADE` to the command, because `CASCADE` drops those objects permanently.

3. Re-create and initialize the extension.

    ```sql
    CREATE EXTENSION anon;
    SELECT public.run_anon_init();
    ```
    {: pre}

4. Re-create the objects that you dropped in step 2.

PostgreSQL stores masking rules, masked roles, and trusted schemas as security labels on your own objects. They remain in place when you drop and re-create the extension.
{: note}