permission denied to set role "web_anon" on CloudSQL

[igorclark]

Hi there, I'm trying to deploy a PostgREST application to Google Cloud Platform using CloudSQL.

I've set up the database by importing literally everything from a pg_dumpall export (of the database from a fully/correctly-functioning app running in a local Docker setup), and at the psql shell everything seems to be in order. In the GCP container log I get Attempting to connect to the database... Connection successful - but I'm obviously missing something, because all REST API & RPC requests (even basics like /table?id=eq.1) just return 401 with this error message:

{"code":"42501","details":null,"hint":null,"message":"permission denied to set role \"web_anon\""}

Everything works perfectly in local Docker using the same image, but this happens on GCP, so I'm guessing it must be some PostgreSQL setup issue or some config difference in CloudSQL, but I'm at a loss - would really appreciate any pointers!

In case it helps:

=> \dn+
                                          List of schemas
    Name    |       Owner       |           Access privileges            |       Description
------------+-------------------+----------------------------------------+-------------------------
 public     | cloudsqlsuperuser | cloudsqlsuperuser=UC/cloudsqlsuperuser+| standard public schema
            |                   | =UC/cloudsqlsuperuser                 +|
            |                   | web_anon=U/cloudsqlsuperuser           |

Thanks,
Igor

[laurenceisla]

Hi, looks like it's missing a permission for the authenticator to switch to the web_anon role. That is:

grant web_anon to authenticator;

[igorclark]

Hi @laurenceisla, thanks very much for getting back to me! Unfortunately that was already set, running it just now made no difference:

=> grant web_anon to authenticator;
NOTICE:  role "authenticator" is already a member of role "web_anon"
GRANT ROLE

I'm very much not a PostgREST wizard, but I think missing that would stop it working elsewhere, and as I did a full postgres dump from a fully-(PostgREST-)working system and loaded it onto this one, my gut feeling is it must be something to do with the Cloud SQL setup. I've tried loading the same pg_dumpall file into a completely fresh postgresql container elsewhere and it works as expected.

I also tried creating a minimal repro today, I made a completely fresh Cloud SQL for PostgreSQL database, ran all the auth steps in the Get it running section from the docs, and pointed a fresh PostgREST instance at it, and it gave me exactly the same error message, permission denied to set role \"web_anon\".

I wonder if it's anything to do with how I set up users/schemas/db on Cloud SQL? Is there anything I can run in psql on the target Cloud SQL db to show any relevant info?

Cheers!

[igorclark]

Okaaaay - I found this comment on SO which suggested granting web_anon to the account I'm using to auth. I tried that and it worked - but is it a terrible idea security-wise? Feels like it!

[laurenceisla]

Hi, sorry for the late follow up.

...granting web_anon to the account I'm using to auth.

Does your db-uri configuration specify authenticator as the user connecting to the database? or is it another user already set by CloudSQL? If it's the latter then yes, what you did is the solution. But we suggest creating the authenticator user that can only login and switch roles, and use that in the db-uri.

[igorclark]

Hi @laurenceisla, no worries and thanks a lot for following up! Yes I'm connecting with credentials for a separate user, which I've created in Cloud SQL, and to which I've granted web_anon. Sounds like that's OK. Thanks very much for your help 👍