PostgreSQL should use model configs `juju-http-proxy`/`juju-https-proxy`/`juju-no-proxy` for backups

[mcarvalhor]

Hi everyone! 👋

Currently for backups, we integrate the charm with s3-integrator and then the charm communicates with the S3 endpoint directly.

Instead, the charm should use the model configs juju-http-proxy/juju-https-proxy/juju-no-proxy and use a HTTP proxy to communicate with the S3 endpoint, if those are set.

Steps to reproduce

1. juju model-config juju-http-proxy="http://my-proxy.internal:3128" # Example
2. juju model-config juju-https-proxy="http://my-proxy.internal:3128" # Example
3. juju model-config juju-no-proxy="127.0.0.1,localhost,::1,10.1.0.0/16" # Example
4. juju deploy postgresql --channel="14/stable" -n 3
5. juju deploy s3-integrator
6. juju relate s3-integrator postgresql
7. juju run s3-integrator sync-s3-credentials access-key="..." secret-key="..."

Expected behavior

Communication to S3 happens via a proxy set using Juju Model Configs juju-http-proxy/juju-https-proxy/juju-no-proxy.

Actual behavior

PostgreSQL try to perform the backups by connecting directly to the S3 endpoint, not using the proxy:
https://pastebin.canonical.com/p/VT8nVCsb7q/

Versions

Operating system: Ubuntu 24.04

Juju CLI: 3.6.8-ubuntu-amd64

Juju agent: 3.6.8

Charm revision: 14/stable - 553

LXD: N/A (using Openstack)

Log output

https://pastebin.canonical.com/p/VT8nVCsb7q/

Additional context

This is a blocker for all PostgreSQL deployments on PS7, PS8+.

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-7925.

This message was autogenerated

[dragomirp]

It does not seem that Pgbackrest is supporting proxies directly: pgbackrest/pgbackrest#2164

[mcarvalhor]

Is it possible to implement proxychain to support juju-http-proxy/juju-https-proxy/juju-no-proxy as stated on that GitHub Issue?

There's a network design change that prevents us from using the S3 endpoints directly starting on Prodstack 7+.

[taurus-forever]

Hi, I understand the need of proxy support here.
Sadly, it was never implemented for pgbackrest. I have re-qualified this from bug to enhancement.

Problem:

• pgbackrest (PostgreSQL backup/restore tool) doesn't support proxies officially.
• proxychain is a huge intrusion: Proxychains works by forcing dynamic linker to preload its shared library (libproxychains.so) into the target process via LD_PRELOAD.

Scope: we will have to develop it 4 times: (PG14+PG16) * (VM+K8s)

Risks:

• proxychain might surprise us on non-amd architectures.
• Logging/debugging could be a challenge....
• external TLS providers might be a problem... (TLS charms relations?). Juju spaces support...
• should proxychain be a separate subordinate charm or built-in into PG charm?
• ...

It is close to impossible to fit this into the current sprint.
Please request this feature officially to schedule with PMs. CC: @7annaba3l

P.S. IMHO, we need some quick tests to make sure proxychain works on Prodstack 7+ at all.
Can you try it from your side to LD_PRELOAD already deployed PG charm? Happy to assist your there. Tnx!

[tingdahl]

Would the use of aproxy be an option?
https://github.com/canonical/aproxy

[taurus-forever]

Would the use of aproxy be an option? https://github.com/canonical/aproxy

It requires investigation. @7annaba3l please add it to the next roadmap to consider PS7/PS8 support.

[7annaba3l]

So on a first sight, I can say the following:

• I do not think it is a good idea to have a single point of failure by placing a proxy between DBs and S3 targets. That proxy (whatever it is ) will fail one day and it will impact a wide number of charms at once. This is even more true for backups where the volume of traffic will be important. A proxy might make sense if it is deployed along the charm itself (like within a side container in a K8s environment). Yet, in that case I do not see a big added value (unless you would like to use the proxy to centralize some config changes ...).

• It feels to me that adding backups targeting S3 to the list of your exceptions is an option to consider.

• Next cycle, we might be able to consider supporting local backups that you can then offload to another storage location asynchronously.

[tingdahl]

I agree on your concern. That said, the way OpenStack is made does not allow Layer 3 connectivity given our instructions on network isolation that we have. This will be solved in future versions of OpenStack, probably PS10 and higher. Currently, there is no way to add exceptions to outside targets (outside of PS7): There are no routes to the outside. Period. Hence there is nothing I can filter.

IS has successfully done a POC with aproxy. We are now requesting a subordinate charm from the PlatEng team.

In the meanwhile, we have to ensure that there are sufficient alerts if backups fail.

[taurus-forever]

@tingdahl can you please post tech details about POC with aproxy, so we can consider it as a solution and unblock PS7 deployments. Tnx!

[tingdahl]

The POC was done by manually log into the database servers, install and configure the aproxy snap. Details on how that is done is found on the aproxy site. https://github.com/canonical/aproxy After the aproxy snap was set up, the traffic to the S3 endpoint was re-routed to the proxy, and we were able to successfully back up the server.

Since the POC, we have asked for a subordinate charm that enables automation of this: https://warthogs.atlassian.net/browse/ISD-4091