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:
- Connect to the Cloud SQL database using our
terraform
user (member ofcloudsqlsuperuser
) - Create the new user
- Set the
CONNECT
,CREATE
andTEMPORARY
permissions on theapi_v1
database to the new user - This applies correctly
- 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:
- Why is
new_user
not even listed in the/du
output? Why can that user connect to the database? - I'm guessing the first entry in the
\l
output is "every user that is part of thepostgres
group"? - I'm also guessing that whatever comes after the slash is the group name?
- 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?