0

I need to create a new Postgres user for an application that needs to connect to an existing applications database.

The original database was created using the Cloud SQL dashboard, the new user is being created using the Terraform Postgres Provider by:

  1. Connect to the Cloud SQL database using our terraform user (member of cloudsqlsuperuser)
  2. Create the new user
  3. Set the CONNECT, CREATE and TEMPORARY permissions on the api_v1 database to the new user
  4. This applies correctly
  5. On next plan, terraform realizes that the user does not in fact have these permissions.

Since I can't see the actual queries that are being sent, I only have the following logs from Cloud SQL:

[2-1] db=api_v1,user=terraform WARNING:  no privileges were granted for "api_v1"
[1-1] db=api_v1,user=terraform WARNING:  no privileges could be revoked for "api_v1"
[1-1] db=api_v1,user=terraform WARNING:  no privileges could be revoked for "api_v1"

The interesting thing is that the user works as expected: I can connect to the api_v1 database (both using the application and using gcloud sql connect) and I can create new tables.

Here are some (shortened) outputs to give more context:

> \l
Role name |  Attributes  | Member of
-----------------------------------------------
api_v1    | Create role, Create DB        | {}
new_user  | Password valid until infinity | {}
terraform | Create role, Create DB        | {cloudsqlsuperuser}


> \du
Name   |  Owner   | Access privileges
----------------------------------------------
api_v1 | postgres | =Tc/postgres             +
                  | postgres=CTc/postgres    +
                  | api_v1=CTc/postgres      +

> \dp users
Schema | Name | Type  | Access privileges | Column privileges | Policies
------------------------------------------------------------------------
public | users | table | api_v1=arwdDxt/api_v1  +|   |   |
       |       |       | new_user=arwdDxt/api_v1 |   |   |

I have multiple questions:

  1. Why is new_user not even listed in the /du output? Why can that user connect to the database?
  2. I'm guessing the first entry in the \l output is "every user that is part of the postgres group"?
  3. I'm also guessing that whatever comes after the slash is the group name?
  4. The warnings read to me like the issue is not granting the permissions to the new user, but granting them on the existing api_v1 database. Is that something only the database owner can do?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.