Pullable database stream dump SSH endpoints for postgresql/mysql

oysteikt commented

2025-12-22 04:21:08 +01:00

Owner

After the bicep incident, we are not able to store another copy of the database due to limited storage space on the VM. We should create SSH endpoints with restricted commands that only lets the requester receive a streamed database dump through the tunnel. That way, principal can still receive database dumps without us ever storing it on the disk

oysteikt added the services label 2025-12-22 04:21:08 +01:00

oysteikt added this to the Kanban project 2025-12-22 04:21:08 +01:00

oysteikt moved this to High priority in Kanban on 2025-12-22 07:50:20 +01:00

felixalb commented

2025-12-23 09:56:23 +01:00

Owner

I assume you mean something like principal running ssh psql@bicep "pg_dump dbnavn | gzip" > bicep_pgdump.sql.gz in the backup script?

For anyone implementing; "SSH endpoints with restricted commands" can look like this

I assume you mean something like principal running `ssh psql@bicep "pg_dump dbnavn | gzip" > bicep_pgdump.sql.gz` in the backup script? For anyone implementing; "SSH endpoints with restricted commands" can look [like this](https://git.pvv.ntnu.no/Drift/pvv-nixos-config/src/branch/main/hosts/bekkalokk/services/website/fetch-gallery.nix#L10-L12)

felixalb referenced this issue

2025-12-23 19:46:06 +01:00

Add SSH key for pulling latest postgres dump with rsync from principal #252

oysteikt added the nixos label 2025-12-24 06:25:52 +01:00

oysteikt added the backup label 2026-01-22 04:54:34 +01:00

oysteikt self-assigned this 2026-01-28 11:44:25 +01:00

oysteikt moved this to Ongoing in Kanban on 2026-01-28 11:45:11 +01:00

oysteikt commented

2026-01-29 19:22:43 +01:00

Author

Owner

I assume you mean something like principal running ssh psql@bicep "pg_dump dbnavn | gzip" > bicep_pgdump.sql.gz in the backup script?

No, this was explicitly not what I meant. What I meant was to have the connection pipe the output of pg_dump through the SSH socket directly, without storing it to disk first (keyword here is stream), so that we wouldn't run into storage issues with the limited space we have on the current bicep instance.

However, after I played around with it, I realized that the backup files takes much less storage than I had feared, so this issue is no longer relevant :)

> I assume you mean something like principal running ssh psql@bicep "pg_dump dbnavn | gzip" > bicep_pgdump.sql.gz in the backup script? No, this was explicitly not what I meant. What I meant was to have the connection pipe the output of `pg_dump` through the SSH socket directly, without storing it to disk first (keyword here is *stream*), so that we wouldn't run into storage issues with the limited space we have on the current bicep instance. However, after I played around with it, I realized that the backup files takes much less storage than I had feared, so this issue is no longer relevant :)

oysteikt closed this issue

2026-01-29 19:22:43 +01:00

oysteikt moved this to Done / Moved / Other in Kanban on 2026-01-29 19:23:19 +01:00

danio commented

2026-01-30 05:53:43 +01:00

Owner

No, this was explicitly not what I meant. What I meant was to have the connection pipe the output of pg_dump through the SSH socket directly

That's what he wrote, principal runs ssh psql@bicep pg_dump dbnavn | gzip which would then output the streamed gzip-compressed dump to stdout, which is then redirected on principal to bicep_pgdump.sql.gz

> No, this was explicitly not what I meant. What I meant was to have the connection pipe the output of `pg_dump` through the SSH socket directly That's what he wrote, principal runs `ssh psql@bicep pg_dump dbnavn | gzip` which would then output the streamed gzip-compressed dump to stdout, which is then redirected _on principal_ to `bicep_pgdump.sql.gz`

Pullable database stream dump SSH endpoints for postgresql/mysql #257