Enhancement request to allow user level operator to execute backup

[howardlowndes]

Version

1.11.7

What browsers are you seeing the problem on?

This problem isn't browser related

What happened?

Can we have a menu option to enable selected users who login thru login.pl to run Backup DB and Backup Roles as they require without the DB admin being required to use setup.pl
Restore would still be a DB admin function.

What should have happened?

Limited delegation of DB admin functions to user level

[ehuelsmann]

This request doesn't fit our current authorization scheme very well: Nowhere in the application server (everything running under Starman) is a database password stored. This is a deliberate choice, because without passwords, there's no way to leak one. The password entered by the user is the password the web application uses to log into the database. The users on the application level also exist as users in the database. The database uses the "principle of least privilege" to guard against unauthorized access or leaking of data. However: in order to create a complete backup, the user needs full access (which is logical: they'll be able to access all data after downloading the backup...).

In order to be able to implement this, we need to change strategy and allow a super-user password to be stored on the server in order for users with the correct rights to be able to create and download a backup... This definitely needs more thought. A design.

[howardlowndes]

I see your point about security and it is sound. Might another option be postgresql replication to a NAS or similar.

[neilt]

Would another solution be using pg_cron or pgAgent, pgAgent tutorial to make regular backups based on a schedule created in setup.pl? No logins would be required. Backups would not be missed, making backups more reliable.

[ehuelsmann]

That would work. I hve some servers set up with a cron job which does a pg_dump; it then uploads the result to a NextCloud server so the customer can download the backup; or they can just leave the backup there if they want and download the backup when they need it. If someone wants to know how the script works I can post it here (minus the passwords :-) )

[howardlowndes]

I use ownCloud myself and it would be simple to set up a headless RPi, but that doesn't protect against an onsite disaster such as flood or fire, so maybe I need to look at Nextcloud too. I would appreciate a copy of your config, thanks.

…

On Tue, 9 Jan 2024 at 07:38, Erik Huelsmann ***@***.***> wrote: That would work. I hve some servers set up with a cron job which does a pg_dump; it then uploads the result to a NextCloud server so the customer can download the backup; or they can just leave the backup there if they want and download the backup when they need it. If someone wants to know how the script works I can post it here (minus the passwords :-) ) — Reply to this email directly, view it on GitHub <#7916 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMS2F7X3YR4RQJKNJ5YOCZ3YNRKKPAVCNFSM6AAAAABBQPGHKGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOBRG44DIMZQGM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- Howard. -- When you want a computer system that works, just choose Linux; When you want a computer system that works, just, choose Microsoft.