Permission error when relating S3 integrator with postgresql-k8s charm

## Steps to reproduce

Cannot reproduce the case in other models but problem is reproduceable in our model.
1. Deploy 3 units of the PostgreSQL charm: `juju deploy postgresql-k8s --channel 14/stable --revision 281 --trust postgresql-k8s-new`
2. Deplot 1 unit of the S3 integrator charm and configure it: `juju deploy s3-integrator --channel=latest/stable --revision=27`
3. Relate them: `juju relate s3-integrator postgresql-k8s-new`
4. Remove the relation `juju remove-relation s3-integrator postgresql-k8s-new`
5. Re-relate them:` juju relate s3-integrator postgresql-k8s-new`

## Expected behavior
No error (stanza should be initialised correctly).

## Actual behavior

```
2025-01-06T10:33:28.254Z [container-agent] 2025-01-06 10:33:28 ERROR juju-log s3-parameters:247: non-zero exit code 50 executing ['pgbackrest', '--stanza=prod-data-visualization-34.patroni-postgresql-k8s-new', 'stanza-create'], stdout='', stderr="ERROR: [050]: unable to acquire lock on file '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock': Permission denied\n HINT: does 'postgres:postgres' running pgBackRest have permissions on the '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock' file?\n"
2025-01-06T10:33:28.254Z [container-agent] Traceback (most recent call last):
2025-01-06T10:33:28.254Z [container-agent] File "/var/lib/juju/agents/unit-postgresql-k8s-new-2/charm/src/backups.py", line 368, in _initialise_stanza
2025-01-06T10:33:28.254Z [container-agent] self._execute_command(["pgbackrest", f"--stanza={self.stanza_name}", "stanza-create"])
2025-01-06T10:33:28.254Z [container-agent] File "/var/lib/juju/agents/unit-postgresql-k8s-new-2/charm/src/backups.py", line 263, in _execute_command
2025-01-06T10:33:28.254Z [container-agent] ).wait_output()
2025-01-06T10:33:28.254Z [container-agent] File "/var/lib/juju/agents/unit-postgresql-k8s-new-2/charm/venv/ops/pebble.py", line 1559, in wait_output
2025-01-06T10:33:28.254Z [container-agent] raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
2025-01-06T10:33:28.254Z [container-agent] ops.pebble.ExecError: non-zero exit code 50 executing ['pgbackrest', '--stanza=prod-data-visualization-34.patroni-postgresql-k8s-new', 'stanza-create'], stdout='', stderr="ERROR: [050]: unable to acquire lock on file '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock': Permission denied\n HINT: does 'postgres:postgres' running pgBackRest have permissions on the '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock' file?\n"
```

## Versions

Operating system: Ubuntu 22.04.5 LTS

Juju CLI: 3.6.1-ubuntu-amd64

Juju agent: 3.4.4

Charm revision: 281

## Log output

Juju debug log:
```
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: StringToSign:
AWS4-HMAC-SHA256
20250106T204344Z
20250106/default/s3/aws4_request
63b76bea231719ba8bfd46eb1700c25e5c0b05ae8b31c6765c502bae001cc2f7
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Signature:
159689f119558fb508fd38f820ac8c803a55a7c2f331c227244c854ce453edd2
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Event request-created.s3.HeadBucket: calling handler
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Sending http request:
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Certificate path: /var/lib/juju/agents/unit-postgresql-k8s-new-1/charm/venv/certifi/cacert.pem
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Starting new HTTPS connection (1): radosgw.ps6.canonical.com:443
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: https://radosgw.ps6.canonical.com:443 "HEAD /superset-db-backups-34 HTTP/1.1" 200 0
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Response headers: {'Date': 'Mon, 06 Jan 2025 20:43:44 GMT', 'Server': 'Apache/2.4.52 (Ubuntu)', 'X-RGW-Object-Count': '6792', 'X-RGW-Bytes-Used': '231329264721', 'X-RGW-Quota-User-Size': '-1', 'X-RGW-Quota-User-Objects': '-1', 'X-RGW-Quota-Max-Buckets': '1000', 'X-RGW-Quota-Bucket-Size': '-1', 'X-RGW-Quota-Bucket-Objects': '-1', 'x-amz-request-id': 'tx00000eb0ba15164ed194a-00677c4080-5afce5e-default'}
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Response body:
b''
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Event needs-retry.s3.HeadBucket: calling handler
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: No retry needed.
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Event needs-retry.s3.HeadBucket: calling handler >
unit-postgresql-k8s-new-1: 2025-01-06 20:43:44 INFO unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Bucket superset-db-backups-34 exists.
unit-postgresql-k8s-new-1: 2025-01-06 20:43:45 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: Starting new HTTP connection (1): postgresql-k8s-new-1.postgresql-k8s-new-endpoints:8008
unit-postgresql-k8s-new-1: 2025-01-06 20:43:45 DEBUG unit.postgresql-k8s-new/1.juju-log s3-parameters:252: http://postgresql-k8s-new-1.postgresql-k8s-new-endpoints:8008 "GET /cluster HTTP/1.1" 200 None
unit-postgresql-k8s-new-1: 2025-01-06 20:43:45 ERROR unit.postgresql-k8s-new/1.juju-log s3-parameters:252: non-zero exit code 50 executing ['pgbackrest', '--stanza=prod-data-visualization-34.patroni-postgresql-k8s-new', 'stanza-create'], stdout='', stderr="ERROR: [050]: unable to acquire lock on file '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock': Permission denied\n HINT: does 'postgres:postgres' running pgBackRest have permissions on the '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock' file?\n"
Traceback (most recent call last):
File "/var/lib/juju/agents/unit-postgresql-k8s-new-1/charm/src/backups.py", line 368, in _initialise_stanza
self._execute_command(["pgbackrest", f"--stanza={self.stanza_name}", "stanza-create"])
File "/var/lib/juju/agents/unit-postgresql-k8s-new-1/charm/src/backups.py", line 263, in _execute_command
).wait_output()
File "/var/lib/juju/agents/unit-postgresql-k8s-new-1/charm/venv/ops/pebble.py", line 1559, in wait_output
raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
ops.pebble.ExecError: non-zero exit code 50 executing ['pgbackrest', '--stanza=prod-data-visualization-34.patroni-postgresql-k8s-new', 'stanza-create'], stdout='', stderr="ERROR: [050]: unable to acquire lock on file '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock': Permission denied\n HINT: does 'postgres:postgres' running pgBackRest have permissions on the '/tmp/pgbackrest/prod-data-visualization-34.patroni-postgresql-k8s-new-archive.lock' file?\n"
unit-postgresql-k8s-new-1: 2025-01-06 20:43:45 INFO juju.worker.uniter.operation ran "s3-parameters-relation-changed" hook (via hook dispatching script: dispatch)
unit-postgresql-k8s-new-1: 2025-01-06 20:43:46 DEBUG unit.postgresql-k8s-new/1.juju-log ops 2.12.0 up and running.
unit-postgresql-k8s-new-1: 2025-01-06 20:43:46 DEBUG unit.postgresql-k8s-new/1.juju-log Emitting Juju event update_status.
unit-postgresql-k8s-new-1: 2025-01-06 20:43:46 DEBUG unit.postgresql-k8s-new/1.juju-log on_update_status early exit: Unit is in Blocked/Waiting status
unit-postgresql-k8s-new-1: 2025-01-06 20:43:46 INFO unit.postgresql-k8s-new/1.juju-log Kubernetes service 'postgresql-k8s-new' patched successfully
unit-postgresql-k8s-new-1: 2025-01-06 20:43:47 INFO juju.worker.uniter.operation ran "update-status" hook (via hook dispatching script: dispatch)
unit-s3-integrator-0: 2025-01-06 20:48:52 DEBUG unit.s3-integrator/0.juju-log ops 2.14.1 up and running.
unit-s3-integrator-0: 2025-01-06 20:48:52 DEBUG unit.s3-integrator/0.juju-log Emitting Juju event update_status.
unit-s3-integrator-0: 2025-01-06 20:48:53 INFO juju.worker.uniter.operation ran "update-status" hook (via hook dispatching script: dispatch)
unit-postgresql-k8s-new-1: 2025-01-06 20:49:35 DEBUG unit.postgresql-k8s-new/1.juju-log ops 2.12.0 up and running.
unit-postgresql-k8s-new-1: 2025-01-06 20:49:35 DEBUG unit.postgresql-k8s-new/1.juju-log Emitting Juju event update_status.
unit-postgresql-k8s-new-1: 2025-01-06 20:49:35 DEBUG unit.postgresql-k8s-new/1.juju-log on_update_status early exit: Unit is in Blocked/Waiting status
unit-postgresql-k8s-new-1: 2025-01-06 20:49:35 INFO unit.postgresql-k8s-new/1.juju-log Kubernetes service 'postgresql-k8s-new' patched successfully
unit-postgresql-k8s-new-1: 2025-01-06 20:49:35 INFO juju.worker.uniter.operation ran "update-status" hook (via hook dispatching script: dispatch)

```

## Additional context

Start with charm/src/backups.py at _initialise_stanza (line 368) and _execute_command (line 263), then reproduce the relation removal and re-creation using the Juju commands in the issue. Investigate ownership and permissions for the pgBackRest lock file under /tmp/pgbackrest during stanza-create. Done means re-relating s3-integrator no longer produces the permission error and the stanza initializes correctly.

Written by the indexing model from the issue text.