Use external PostgreSQL database

[Mmoncadaisla]

Context

NOTE: Related to #64 and maybe to #32

The eoAPI helm chart allows enabling the pgstac service to spin up a PostgreSQL database, which is nice.

However, for production-ready environments it'd be awesome if it was possible to directly pass the connection parameters to the pgstac user in an existent database for titiler.pgstac, stac_fastapi.pgstac and tipg.pgstac to use.

In our case specifically, we aim to connect to a Cloud Native PG database, we understand that other users would benefit from connecting to Cloud SQL, Amazon RDS for PostgreSQL, etc too.

Question

Perhaps (hopefully) it is straightforward to achieve this and I missed the configuration steps to achieve it. Is it possible to connect to an external database instead of using the existent db service?

If so, what are the requirements? we understand that a minimum version plus pgstac configured is a must (e.g: Postgresql>=13 and PostGIS>=3. with best performance using PostGIS>=3.1 according to PgSTAC requirements).

[jrip-geo]

@Mmoncadaisla have you been able to figure this out, I'm interested in connecting the stac deployment to a cloud sql instance as well.

[Mmoncadaisla]

@MathewNWSH I have not continued to explore the possibility to connect eoapi-k8s to an external PostgreSQL database yet. I think it'd be best if the team from developmentseed can take a look at this topic given that there's interest from others, perhaps @vincentsarago @ranchodeluxe can give a hand?

OTOH, if your goal is to leverage PgSTAC only then you can create the relations (schema, roles, tables and UDFs) via PyPgSTAC in any database, including a PostgreSQL database managed via Cloud Native PG operator.

[ranchodeluxe]

Sorry @Mmoncadaisla and @MathewNWSH: It's been brought to my attention that I'm missing discussion items (and also not getting alerted about them somehow 🥸). Thanks for having this discussion and using the helm chart.

This repository will probably be getting some love soon (though not sure by whom). As discussed soon here I think the goal will be to have the charts installing/using Crunchy's Postgres Operator by default for k8s-specific high availability solution

Setting up cloud provider storage access shouldn't be too hard but still needs to be documented. In terms of AWS (which I am more familiar with) the general steps would be:

• without IRSA (see last item for context of what that means) the pods should leverage the EC2's node IAM role. So you can add RDS policies grants to them like we do for other things such as s3 here. This now enables every pod by default to talk to the cloud provider storage

• make sure each service optionally takes an "extraConfig" or "extraSecrets" param where folks can arbitrarily pass secret/configmap key/value pairs (or possibly just point to existing ones they've setup) to be mounted into the pods like JH does. This would still have to be added to our helm configs

• decide if we want to leverage ServiceRole IAM bridge for pods (IRSA) or not and add it to the Terraform optionally https://github.com/developmentseed/eoapi-k8s-terraform

In the future instead of creating a discussion for something like this I'd just create an issue and ask for thing you want! We also are open to PRs

[Mmoncadaisla]

Hi @ranchodeluxe thanks a lot for your attention!

NOTE: perhaps the quick-access actions should be replaced then as it points to creating a new discussion for feature requests.

Per your comment it seems that eoapi-k8s could be more committed to choosing an operator for HA rather than allow users to configure an external PostgreSQL database (that might or might not leverage the Crunchy Data Operator) with some prerequisites. Should we go ahead and create an issue in this repo as a feature request then?

[ranchodeluxe]

NOTE: perhaps the quick-access actions should be replaced then as it points to creating a new discussion for feature requests.

Good point 😉

Should we go ahead and create an issue in this repo as a feature request then?

There already is an existing issue created last week that can be fleshed out: #77

[ranchodeluxe]

Okay, at least the issue template is fixed now, thanks for the feedback

[pantierra]

This can be done by now and is documented here.