Seems not possible to anonymize on Postgres cloudsql instance

[fredFoucart]

Hello all,

I work with a postgres instance deployed on a GCP cloudsql.

I enabled the related flag cloudsql.anon and the extension is initialized

select anon.init();

NOTICE: The anon extension is already initialized. init

t

(1 row)

I defined some rules but when I trigger the anonymization, I've the following:

SELECT anon.anonymize_table('talent');

ERROR: permission denied for sequence first_name_oid_seq

CONTEXT: SQL function "fake_first_name" statement 1

SQL statement "UPDATE public.talent SET first_name = anon.fake_first_name(),last_name = anon.fake_last_name()"

PL/pgSQL function anon.anonymize_table(regclass) line 10 at EXECUTE

It seems a permission issue, knowing that the sql user has the highest possible role on cloudsql (cloudsqlsuperuser)

Thks a lot in advance for your support.

[ms4446]

The error message you're seeing suggests that the account being used doesn't have sufficient permissions to use the sequence first_name_oid_seq. The sequence is most likely being used for auto-generating values in your table.

In PostgreSQL, sequences are separate objects with their own permissions. Even if a user has permissions on a table, they might not have the necessary permissions on the sequence associated with that table.

You might need to explicitly grant permissions on the sequence to the user. This could be done with a command like the following:

GRANT USAGE, SELECT ON SEQUENCE first_name_oid_seq TO your_user;

Replace your_user with the actual user that needs access to the sequence. This command should be executed by a superuser or an account with sufficient privileges.

Please note that you should replace first_name_oid_seq with the correct sequence name if it's different. The sequence name is often the column name followed by _seq but it may vary based on your database design.

[fredFoucart]

Hello,

Thks very much for the answer. I already did the necessary for sequence permissions with the following command:

GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA public TO "my-user";

-> always the same issue.

And when I do the sequence list, the sequences coming from the anon extension are not present 

\ds
                            List of relations
 Schema |              Name               |   Type   |       Owner        
--------+---------------------------------+----------+--------------------
 public | order_item_number_seq           | sequence | my-user
 public | order_line_number_seq           | sequence | my-user
 public | orders_number_seq               | sequence | my-user
 public | organization_contact_number_seq | sequence | my-user
 public | process_number_seq              | sequence | my-user
(5 rows)

It seems that my user is not allowed to use sequences which are inside the anon extension. And not able to access them with a supercloudsqluser role.

The anon extension is proposed by the GCP available extensions and when I read the extension documentation, I don't see what is missing regarding the installation

[ms4446]

Here are a few additional things you could try:

1. Check the schema of the anon extension sequences:  It's possible that these sequences are not in the public schema. You can check this by querying the pg_sequences system catalog view with something like the following query:

   SELECT sequencename, sequence_schema FROM pg_sequences WHERE sequencename LIKE 'anon%';

   This will give you a list of all sequences whose names start with 'anon' and the schemas they belong to. If these sequences are in a different schema, you'll need to grant permissions on that schema.

2. Explicitly grant permissions on the anon extension sequences: If you're able to find the names of the sequences used by the anon extension, you could try granting permissions on these sequences explicitly. You'd do this with a similar command to the one you used before, but with the specific sequence names.

   GRANT USAGE, SELECT ON SEQUENCE first_name_oid_seq TO "my-user";

[fredFoucart · Accepted solution]

Ok thanks to your hints, I found the solution. I'm really grateful🙏

I did the following :

SELECT * FROM pg_sequences; 
 schemaname |          sequencename           |   sequenceowner    | data_type |  
------------+---------------------------------+--------------------+-----------+-
 public     | orders_number_seq               | my-user             | integer   | 
 public     | order_item_number_seq           | my-user             | integer   | 
 public     | process_number_seq              | my-user             | integer   | 
 public     | order_line_number_seq           | my-user             | integer   | 
 public     | organization_contact_number_seq | my-user             | integer   | 
 anon       | address_oid_seq                 | root               | integer   | 
 anon       | city_oid_seq                    | root               | integer   | 
 anon       | company_oid_seq                 | root               | integer   | 
 anon       | country_oid_seq                 | root               | integer   | 
 anon       | email_oid_seq                   | root               | integer   | 
 anon       | first_name_oid_seq              | root               | integer   | 

Indeed the anon schema sequences have their own.

So as a root user I apply the grants like this :

GRANT SELECT ON ALL SEQUENCES IN SCHEMA anon TO "my-user";

 Then I retry the anonymization logged with "my-user" and it works 👍

[daamien]

This is a known bug introduced in version 1.0 

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

It will be fixed in the upcoming version 1.2