Re: How to revoke privileged from PostgreSQL's superuser

I think you may be conflating two things.

The "superuser" (or root) in an operating system is a special user (with uid = 0 in unix/linux systems) that bypasses many checks. Ideally sysadmins and regular users will never log in as root - they'll either use the existing privilege framework (e.g., groups and set-uid programs) or briefly use privilege escalation via a program like 'sudo'.

In postgresql the equivalent user is 'postgres'. Nobody should ever be logged in as that user once you've created the initial user(s). What postgresql calls a 'superuser' is just a user with a few permissions set by default. It's easy to grant the same privileges to any user, or drop them from someone created as a superuser.

Since they're regular users it's easy to revoke privileges. E.g., I think the command you want is 'revoke all on database x from [ role | public]'  or 'revoke all on schema x from [ role | public ]'. I don't know if you can revoke their admin privileges on a single database. If not then it's just a small hurdle since they could grant themselves the necessary privilege, do their work, and then revoke them so they'll never know unless a periodic audit of privileges catches them in the act.*

However it's important that you make sure you don't block normal operations. E.g., you'll probably want a user who has access to your tables in order to perform backups. (This user could be limited to SELECT privileges.) You'll also need a user who can perform a restoration from backups - that's normally the DBA. It's tempting to limit these privileges to the only times they're actually required, e.g., the backup script could start by granting SELECT privileges to the backup user and finish by revoking those privileges, but that's arguably worse since it requires a second user with the admin privileges required for that.

Finally I think you may be asking the wrong question. You need a DBA and you need to trust the people the people who have DBA rights. If you don't trust them then they shouldn't hold that job. All of the security standards I'm familiar accept that some people will have high levels of access and the focus is on vetting those people for trustworthiness.

(*) If you want to be really paranoid you could put a trigger on the pg_* tables that contain privileges and triggers. That would prevent anyone from giving themselves extra privileges, or disabling the trigger that would alert you if they give themselves extra privileges, without at least some warning. Heck, the trigger could even prevent them from making these changes. But that's pretty advanced dba-fu.