Figure out how to get access to the FPDCC’s GIS Database from heroku

[fgregg]

Depends on #58

[hancush]

It seems like you can whitelist IPs to specified users with passwords in the usual way on MediaOcean's managed Postgres service. Whoops, it seems that way because it's documentation about how to do it in the usual way, lol.

It does actually look like you can whitelist specific IPs in the settings of the managed Postgres instance. Not sure about a per-user control, but I'll look into it. Could just naively accept connections as any user from the specified sources.

So, I think we should be able to assign static IPs to our Heroku instances with QuotaGuard or Fixie, then ask them to allow inbound connections from our IPs and provide us credentials. We should also make sure to enable SSL for the fp_postgis database.

Anything raise your hackles about that, @fgregg?

[hancush]

How to create a new user: https://docs.digitalocean.com/products/databases/postgresql/how-to/manage-users-and-databases/

New users will receive the permissions of the doadmin user by default. You can’t change these permissions from the control panel, but you can modify user privileges using third-party tools like psql.

Pretty crazy new users receive admin privileges by default, lol. I would recommend clearing default privs and granting us readonly access to the schemas/tables we need.

[hancush]

Readonly access docs here: https://docs.digitalocean.com/products/databases/postgresql/how-to/modify-user-privileges/#modify-postgresql-user-permissions

[hancush]

We could get 20k (QuotaGuard) or 25k (Fixie) requests per month for $20. But, does that mean, like, all requests to and from our app, or can we selectively proxy only the GIS database outbound requests?

[fgregg]

i wonder if it would be enough to connect over ssl, without requiring a whitelist. https://www.digitalocean.com/community/questions/how-can-i-obtain-the-client-certificate-and-client-private-key-for-the-managed-postgresql Looks like we have a couple of options now. Next stage is to talk to Garret and see what makes sense to him.

…

On Fri, Dec 3, 2021 at 5:12 PM hannah cushman garland < ***@***.***> wrote: We could get 20k (QuotaGuard) or 25k (Fixie) requests per month for $20. But, does that mean, like, all requests to and from our app, or can we selectively proxy only the GIS database outbound requests? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#59 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEDC3P7JNKXPXTI3ZLQS3TUPE6FRANCNFSM5IP4I4LQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[hancush]

Ooh, great idea, @fgregg!

[hancush]

It looks like managed Postgres instances are open to any inbound connection by default. So, we should find out if CCFP has locked it down or maintains the default open setting. If it is open, then we should just be able to connect over SSL with a username and password.

If it is not open, then I don't see a way around whitelisting, either a fixed Heroku IP, or some passthrough server we maintain. @fgregg What do you think?

[hancush]

Got creds from Garret. Unfortunately, not able to connect from the staging app dyno using psql as I would expect. I think this is because Fixie proxies HTTP requests, but Postgres doesn't make HTTP requests to the specified hosts, it uses sockets.

If you’re using PostgreSQL, by default (empty HOST), the connection to the database is done through UNIX domain sockets (‘local’ lines in pg_hba.conf). If your UNIX domain socket is not in the standard location, use the same value of unix_socket_directory from postgresql.conf. If you want to connect through TCP sockets, set HOST to ‘localhost’ or ‘127.0.0.1’ (‘host’ lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available.

I'm at the edge of my networking knowledge here, but there is a Heroku add-on called Fixie Socks that can proxy TCP connections. @fgregg Are you available to discuss/troubleshoot with me next week?

[fgregg]

absolutely!

…

On Thu, Dec 9, 2021 at 4:14 PM hannah cushman garland < ***@***.***> wrote: Got creds from Garret. Unfortunately, not able to connect from the staging app dyno using psql as I would expect. I think this is because Fixie proxies HTTP requests, but Postgres doesn't make HTTP requests to the specified hosts, it uses sockets <https://docs.djangoproject.com/en/3.2/ref/settings/#std:setting-HOST>. If you’re using PostgreSQL, by default (empty HOST), the connection to the database is done through UNIX domain sockets (‘local’ lines in pg_hba.conf). If your UNIX domain socket is not in the standard location, use the same value of unix_socket_directory from postgresql.conf. If you want to connect through TCP sockets, set HOST to ‘localhost’ or ‘127.0.0.1’ (‘host’ lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available. I'm at the edge of my networking knowledge here, but there *is* a Heroku add-on called Fixie Socks that can proxy TCP connections. @fgregg <https://github.com/fgregg> Are you available to discuss/troubleshoot with me next week? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#59 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEDC3ID2B5ZIZP3DRCH5PLUQEL3FANCNFSM5IP4I4LQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[hancush]

K, I've set up Fixie Socks. We think that we'll be able to use a library like pysocks to create a socket connected to the Fixie Socks relay, then configure Django to use that socket, but we can't find a good example, so we'll pair on this from the Heroku shell once Garret whitelists the new IPs.

[hancush]

Summary of the ultimate approach from #91:

---

Outside of version control, I provisioned a QuotaGuard Static add-on for the staging app, asked Garret to whitelist the provided IPs as trusted traffic sources for GIS database, then configured the tunnel following these instructions. The resulting config is committed to version control in this PR to remove our dependency on the QuotaGuard site. I also added the GIS_DATABASE_URL to review app config vars in the Heroku UI.

As this approach relies on static IPs being available, it will only work for apps to which we've attached the QuotaGuard Static add-on. By default, this excludes review apps, and if it's possible, I think it's probably a good idea to write the GIS integration such that the app is resilient enough for testing without access to the GIS database.

For scenarios where we do want GIS access for a review app, QuotaGuard Static instances can be shared between apps, so we can do heroku:attach -a ${REVIEW_APP} quotaguardstatic after the app is initialized. I've gone ahead and tested this approach with the review app created by this PR. Worked as expected!