Dynamic masking with postgresql-anonymizer on cloud sql doesn't seem to be masking for any roles

[JorisEnzo]

Hi all,

I am configuring a Cloud SQL postgres instance to use dynamic masking functionality in the cloudsql.anon (postgresql-anonymizer) extension. So far the masking functions work fine (ie. with select custom_mask_function(resource) from ...). Running the static table masking function (anon.anonymize_table) on any masked tables also works fine, so the masking rules seem to be working as well.

However, whenever I try a select resource from ... with a masked user on a table masked by a rule, I get an unfiltered result. Dynamic masking seems to be not enabled though select

anon.start_dynamic_masking(); gives me 'Anon extension is already initiliazed' and no errors.

Unfortunately logging is very limited but I did not find any errors in postgres.log.

Would really appreciate tips on further troubleshooting or resolving!

[ms4446]

It's challenging to determine the exact cause without additional information, I can suggest several common troubleshooting steps:

1. Check the version of PostgreSQL and the Anonymizer extension you're using. There might be compatibility issues between certain versions of PostgreSQL and the Anonymizer extension. Make sure that you're using versions that are known to work well together.

2. Verify the user privileges. The dynamic masking feature relies on the current user's privileges. Check if the user you're testing with has the necessary privileges to trigger the dynamic masking feature.

3. Double-check the masking policy. Make sure the masking policy is correctly defined for the table and the columns you're working with. If the masking policy isn't properly linked to the tables and columns, it won't be applied when querying the data.

4. Check session variables. Sometimes, the behavior of the Anonymizer extension can be affected by certain session variables. Ensure that these variables are set correctly for your session.

5. Test with multiple users and roles. Try the same queries with different users and roles to see if the issue is specific to a certain user or role.

6. Review your database configuration. Some database configurations may interfere with the Anonymizer extension's functionality. Review your database configuration to make sure it's compatible with the extension.

7. Search for known issues. Search the PostgreSQL Anonymizer extension's issue tracker for similar problems. It's possible that others have encountered the same issue and that there's a workaround or solution available.

8. Increase logging verbosity. Although you mentioned that logging is limited, you might be able to increase the logging verbosity to get more detailed information about what's happening when you try to apply dynamic data masking. Look into PostgreSQL's logging documentation to see if you can enhance your logging configuration.

[JorisEnzo]

@ms4446 thank you for your extensive reply. I think everything is pretty set up according to the requirements, and because static masking is working on the columns with rules when done with any of the masked roles, I think also the privileges seem to be ok. starting dynamic masking also gives no error, it just also doesn't do anything.

I have installed pgaudit now and can see all queries passing without errors when initialising the extension as well as when enabling dynamic masking. I also see the select query I run to test the masking on a table where a column has a simple mask (no custom function just value replacement), but that query is run without any rules attached. I am unsure what I should be seeing though and uncertain where to further dive in or which information would be relevant. 

Here my answers to your 8 points to start checking:

1. Check the version of PostgreSQL and the Anonymizer extension you're using: We are using postgres 14.5 with anonymizer 1.0.0. According to the documentation, this version works on all supported postgres versions

2. Verify the user privileges: According to the documentation, the privileges are setup correctly. schema grants are changed  by defining a role as masked, and output seems working. Further, the masked role was able to anonymise a table through static masking

3. Double-check the masking policy: I am assuming this is correct, as statically masking a table worked

4. Session variables, i have checked the anon variables which are set correctly. Are there others relevant?

5. Test with multiple users and roles, tested with 4 different roles, finally using the skynet role example from documentation, all with the same behaviour, functions work, static anonymising works, dynamic does not.

6. Review your database configuration: I could not find any documentation on what would not be compatible with the extension, however we use mostly the default settings provided by GCP cloud sql

7. Search for known issues: did that, but nothing seems similar, was hoping for experiences from the community running into similar issues and having resolved them 🙂 

8. I have enable pgaudit now to get more logging details, but don’t really know what to look for. What is odd is that the select statement on a masked column doesn’t seem to trigger any masking logic I would expect.

[ms4446]

Here are a few recommendations you might want to try:

1. Check whether you're trying to use dynamic masking on views or across multiple schemas. As per the GitLab issue and the documentation, these could potentially lead to problems.

See the following: 

https://gitlab.com/dalibo/postgresql_anonymizer/-/issues/166 

     2. Consider the possibility that dynamic masking might be slow with certain types of queries. If your queries involve joining tables on a masked key using hashing or pseudonymization, this could be causing delays or problems.

     3. If you're using a graphic tool, ensure that it's correctly querying the masked view rather than the original table.

[JorisEnzo]

@ms4446 Thank you for that link, that pointed me to something! So we have a patientjournal table in public with a json resource object with a field with information I want to mask. 

When logged in with the masked role (I am using the console through our proxy), if I run "SELECT resource FROM public.patientjournal;" The mask is not applied, however, if I run "SELECT resource FROM patientjournal;", ie without the schema, it is masked.

Checking the privileges though I don't have any for the masked role (using skynet right now) on the public schema:

Name | Owner | Access privileges | Description

public | cloudsqlsuperuser | cloudsqlsuperuser=UC/cloudsqlsuperuser+| standard public schema
| | =UC/cloudsqlsuperuser |

I reckon with masking working as it should, either running a query on patientjournal or on public.patientjournal should work the same, or access to run on public.patientjournal should not be allowed right?

[ms4446 · Accepted solution]

PostgreSQL Anonymizer works by creating a new schema and building a "mask" over the original data using views. These views are identical to the original tables but replace sensitive data with fake data. The masked user is only granted access to the new schema and not the original one, so they can only see the obscured data​.

However, there are several limitations and issues with the dynamic masking feature:

1. It only works with one schema at a time. If you need to mask data in multiple schemas, you would have to use the anon.anonymize() function or the anon.dump() function which work fine with multiple schemas​

2. It can be slow with some queries, especially if you try to join 2 tables on a masked key using hashing or pseudonymization​

3. It can cause issues with graphic tools like DBeaver or pgAdmin, which might directly query the original table instead of being "redirected" by the masking engine to the masked view. To view the masked data with a graphic tool, you might need to either run a SQL query like SELECT * FROM table_name or navigate to the masked view in the database navigator​

For more details see https://postgresql-anonymizer.readthedocs.io/en/latest/dynamic_masking/

[JorisEnzo]

Thank you @ms4446 for these clarifications, that was the piece I was missing. I was assuming that the mask_role script would remove all privileges for the masked roles so they would not be able to query the table directly. For some reason, the read rights were however not revoked. Now it makes sense.