CODEWITHSUNDEEP
postgresqlazureazure-postgresqldata-masking
haxkd
haxkd

Reputation: 17

Getting permission denied while running query in azure PostgreSQL flexible server

Firstly I have been created a PostgreSQL 16 database on azure flexible server and then I login with the admin account and trying to use postgresql-anonymizer to mask the data but while following the doc for Anonymous Dumps by https://postgresql-anonymizer.readthedocs.io/en/stable/anonymous_dumps/ But I was not able to run a query,
ALTER ROLE dump_anon SET anon.transparent_dynamic_masking = True;
It shows ERROR: permission denied to set parameter "anon.transparent_dynamic_masking"
what's the issue and how to resolve it?

Requirement: I want to dump masked/anonymized data. and without that query the dump is getting plain data.

Upvotes: 1

Views: 105

Answers (1)

Bhavani
Bhavani

Reputation: 5317

enter image description here

Only superusers can change this setting.

If you try with any other instead of superuser then you may get above error. But According to the MS document super user is not accessible is not accessible in azure PostgreSQL - Flexible Server.

I want to dump masked/anonymized data. and without that query the dump is getting plain data.

But you can follow below procedure to achieve your requirement:

Go to Settings section, select Server parameters in Azure Postgres SQL server, search for the shared_preload_libraries parameter, select the ANON library and click on save as shown below:

enter image description here

After that search for azure. Extensions Select ANON, save it as shown below:

enter image description here

Initialize dynamic masking using below query in required database:

SELECT anon.start_dynamic_masking();

Create user dump_anon and assign anon masking for the user/role using below query:

CREATE USER dump_anon WITH PASSWORD '<password>';
SECURITY LABEL FOR anon on ROLE dump_anon IS 'MASKED';

Grant permission to the user on required table and enable data masking on requird column using below column:

SECURITY LABEL FOR anon ON COLUMN <tableName>.<columnName> IS 'MASKED WITH FUNCTION anon.partial(<columnName>,2,$$*****$$,2)';

After that login to the table with created user, you will be able show the difference between previous user and newly created user while viewing table as shown below:

Previous user:

enter image description here

New user:

enter image description here

For more information you can refer to the MS document.

Upvotes: 1

Related Questions