Merge ~andersson123/ubuntu-qa-website:database-backup into ubuntu-qa-website:main

Tested it now, it all works! moving to needs review

Need to amend the script to remove old backups too

I think Steve is talking about the "wal_e" juju configuration options here.

On Fri, Jan 26, 2024 at 07:19:43PM -0000, Brian Murray wrote:
> I think Steve is talking about the "wal_e" juju configuration options here.

I'm looking at these options in 'juju config postgresql':

  backup_dir:
    default: /var/lib/postgresql/backups
    description: |
      Directory to place backups in.
    source: default
    type: string
    value: /var/lib/postgresql/backups
  backup_retention_count:
    default: 7
    description: Number of backups to retain.
    source: default
    type: int
    value: 7
  backup_schedule:
    default: 13 4 * * *
    description: Cron-formatted schedule for regular database backups.
    source: default
    type: string
    value: 13 4 * * *

Alarmingly, they also seem not to have had any effect since Dec 8, which is
concerning. I have no idea why since /etc/cron.d/juju-postgresql is still
present on the system.

Heh. Cron mail:

Traceback (most recent call last):
  File "/var/lib/postgresql/scripts/pgkillidle.py", line 114, in <module>
    sys.exit(main())
  File "/var/lib/postgresql/scripts/pgkillidle.py", line 55, in main
    con = psycopg2.connect(options.connect_string)
  File "/usr/lib/python3/dist-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: Peer authentication failed for user "postgres"

So needs some pg_hba amending...

I changed the juju config:
```
juju config postgresql extra_pg_auth="local all all md5 map=qatracker"
```

I'll amend the script to grab the backups from /var/lib/postgresql/backups instead :)

 I don't know if what I did was correct, but I will check for backups soon

well, actually, I'll try create a backup now

-_-

```
stg-image-testing-tracker@ubuntu-qa-bastion-ps5:~$ juju run-action postgresql/1 wal-e-backup --wait
unit-postgresql-1:
  UnitId: postgresql/1
  id: "16"
  message: Backup failed
  results:
    Stdout: |
      sudo: /snap/bin/wal-e.envdir: command not found
    backup-return-code: "1"
    wal-e-backup-cmd: /snap/bin/wal-e.envdir /etc/postgresql/12/main/wal-e.env /snap/bin/wal-e
      backup-push /var/lib/postgresql/12/main
    wal-e-prune-cmd: None
  status: failed
  timing:
    completed: 2024-01-29 11:06:16 +0000 UTC
    enqueued: 2024-01-29 11:06:13 +0000 UTC
    started: 2024-01-29 11:06:14 +0000 UTC
```

Looks like wal-e wasn't actually installed as a snap, so I'm doing that

wal-e backups go straight to cloud storage, they don't seem to have an option to backup to filesystem. I'm looking more.

Oh okay, I broke the qatracker...

I fixed it again, and there's some issues in prod that I'm going to try and amend today.

To sum up the issue, the pg_hba.conf has two conflicting lines:
```
local all all peer
local all all peer map=qatracker # extra_pg_auth config
```

The first one isn't sufficient to allow connections to the db, and it must be removed, with just this line present for the pg_hba.conf to be correct:
```
local all all peer map=qatracker # extra_pg_auth config
```

but the charm writes that file periodically (multiple times a minute) so one must delete the bad line and restart the postgresql service before the file is re-written.

I'm going to look into the juju config options and see if I can amend this.

and I broke the qatracker simply by restarting the postgresql service. My guess is someone in the past removed the bad line and restarted the service before the file could be re-written. I'm also guessing this has something to do with the backups not working. I'll fix it

For now, what I've done, is create
/home/ubuntu/pg_hba.conf

and modified /etc/postgresql/12/main/postgresql.conf to point to the above file, without the difficult line. postgresql.conf isn't getting periodically re-written so this is a temporary fix - @brian let's discuss in standup today.

ah, and this seems to have fixed the backups :)

(really hating that the backups and killing of dead connections are cronjobs and not systemd)

Now I'll amend the script to use these backups.

This is fully amended and tested now.

On Mon, Jan 29, 2024 at 01:24:06PM -0000, Tim Andersson wrote:
> For now, what I've done, is create
> /home/ubuntu/pg_hba.conf

> and modified /etc/postgresql/12/main/postgresql.conf to point to the above
> file, without the difficult line. postgresql.conf isn't getting
> periodically re-written so this is a temporary fix - @brian let's discuss
> in standup today.

Please move this file under /etc/postgresql/12/main, even if it's under a
different name. There's a privilege escalation attack by having it in
/home/ubuntu, which is that the ubuntu user can delete the file despite it
being owned by root, then replace it with contents of its choice.

I moved it to:
/etc/postgresql/12/main/pg_hba_qatracker.conf

and I also pointed
/etc/postgresql/12/main/postgresql.conf

to the correct filepath

So this is ready for review!

Thanks - I don't have permissions to merge though