Feature Request: Integrate with Cloud SQL Proxy

[alyssaruth]

Description

It would be great to be able to configure a Postgres instance hosted in GCloud, where access can only be granted using the Cloud SQL Proxy. Something like this was raised with the google provider already, but was closed (rightly) as it would need to be implemented in this provider instead. In particular, see this comment: https://github.com/terraform-providers/terraform-provider-google/issues/2094#issuecomment-425688564

Currently, the only solutions available to us that I can see are:

• Configure postgres by hand after terraform has run (not declarative), OR
• Make the postgres provider point at localhost, and have a requirement that the cloud SQL proxy is running locally whenever you want to run a terraform apply (not ideal)

Terraform Configuration Files

For example, I want to be able to reference my cloud SQL instance in the provider (probably passing some additional details) and have it automatically know to connect using the cloud SQL proxy. Something like:

provider postgresql {
  host = google_sql_database_instance.my_instance.???
  port = 5432
  database = google_sql_database.my_instance.name
  username = google_sql_user.my_administrator.name
  password = google_sql_user.my_administrator.password
}

References

• https://github.com/terraform-providers/terraform-provider-google/issues/2094

[sterlingdeng]

Agree. I've ran into the same issue but will most likely solve this with #1 which you listed above (doing it manually).

[mltsy]

This would be super useful. I thought about trying to set this up with a local_exec provisioner, but ... it seemed pretty messy to get it setup and torn down correctly for a config where I need to connect to and configure several separate postgres servers.

[benfdking]

We have been having this issue as well. It would be massively useful for us to be able to be able to connect to a managed Postgres instance and declarivetely add databases, users, etc ...

Having thought about it a little, I have come up with a potential solution that is slightly different to the one mentioned above but I think achieves the same aim. https://gocloud.dev/ is a library that in Go abstracts the connection to differing cloud providers for things like databases. We use it to easily connect to the Google SQL server without having to run the proxy in parrallel. I was thinking it could be used for the exact use case described above and even more as it could equally be used for AWS.

I have made a stab at an implementation but I must say this is very much a first draft and I am struggling to get it up and running ... But in theory it should work. I would love some help/to help more.

https://github.com/benfdking/terraform-provider-postgresql/pull/1

[mltsy]

Ooh I see, you're referring to the way it implements handling of the custom CAs and authentication scheme for you within Go rather than running a separate proxy process (to handle them and expose a localhost listener, the usual way): https://gocloud.dev/howto/sql/

That's interesting! I don't know much Go, so I'm not going to be much help, but that does sound like a great plan! I see most of the 973 changes in your PR are just vendored library files, so that's a relief 😄

[benfdking]

🎉 I have now got it up and running in here https://github.com/benfdking/terraform-provider-postgresql/pull/1 with a very simple example. Requires terraform 0.12, couldn't get the setup with the plugins to work with terraform 0.13. It definetely needs some tidying but works pretty much as expected.

[tun0]

I have now got it up and running in here benfdking#1 with a very simple example. Requires terraform 0.12, couldn't get the setup with the plugins to work with terraform 0.13. It definetely needs some tidying but works pretty much as expected.

Awesome! Do you plan to (continue to) work on 0.13 support?

[benfdking]

@tun0 I don't think it's necessarily the fact that it doesn't work in terraform 0.13. In fact there is nothing that to me would indicate it shouldn't: I just couldn't get it setup correctly with the new provider definition, whereas in 0.12 I just placed it in the root folder of where it runs.

We are using it in production and have placed it here for the time being. Our aim is to keep it up to date with the added functionality of being able to proxy seamlessly to Google & Amazon Postgres instances. It does still need a bit of love to tidy it up but definetely functional from our limited experience with it.

[tun0]

After reading up a bit on the subject, it seems that as of 0.13 using custom provider got quite a bit more complex indeed. Being rather new to Terraform in general, I doubt I could make it work either :wink:

[benfdking]

For use with terraform 0.13 and just generally easier use https://registry.terraform.io/providers/tumelohq/postgresql/latest

Note that the documentation isn't there yet, the best I can do is point to the example.

[tun0]

Awesomeness! Gonna take it for a spin when I find some spare minutes. The provided example should be enough to get started.

[ademariag]

@benfdking is the intention to have a PR and merge into this provider?

[benfdking]

Hey @ademariag

With the ever so slight bias of being part of the @tumelohq team that created it, I do think this solution is a nice step forward and it has given us oodles of flexibility which could benefit others, among them:

• it feels cloud agnostic
• we can test the setup locally against databases
• none of have a background really in database administration and so this feels more natural with what we know and love with terraform
• it's one less tool we have to learn or build a script for

From my point of our point of view, I think we would be very proud if this concept would be merged into the provider. It would force us slighlty to polish it up or maybe even start fresh, but we think it would for the better and would make it more accessible to others.

That said I don't really think that's our call to make and would defer to the maintainers: I am sure they have many more difficult considerations that go way past what I have mentioned. If they feel like this would be valuable for them though, I and I am sure some others on the team would gladly spend some time to help make that a reality.

[ademariag]

@benfdking understood thank you for your response.

I think it's still worth creating a pull request and give an opportunity to the maintainers to weight in and decide whether they are ok with it or not 😊