PostgreSQL : Restrict (another) superuser from accessing database

postgresql

I'm new in PostgreSQL and looking to find if this is possible.

There are two superusers, 'postgres' (which is default superuser from PostgreSQL) and 'super2'.

Superuser 'super2' creates a new database named 'Movies' and being the owner of it.

Is it possible to restrict superuser 'postgres' to access the database 'Movies' so that only superuser 'super2' could access it, because he is the owner of the database.

If it is possible, then how to make it happen?

Best Answer

francs is entirely correct. superuser means just that. They're all-powerful. They can do anything, including load additional code into the database, modify tables on disk directly, etc. See CREATE ROLE and the documentation on client authentication for more information.

If you don't trust them, don't give them superuser rights. In this case, it sounds like you should've just done a CREATE DATABASE movies WITH OWNER the_other_user and given them a normal, non-superuser login. Or if they need to create their own databases, you could give them CREATEDB rights.

The only way to restrict a superuser is by changing the C code inside PostgreSQL directly. Even then you'd probably be wasting your time, as a determined user could get around restrictions like a ProcessUtility_hook filter if they have superuser access.

Remove their superuser access. Unless they've had the foresight to backdoor your system in a way that'll let them regain access (unlikely, and not trivial) you should be OK.

ALTER USER the_user WITH NOSUPERUSER;

You can add CREATEDB rights if you want them to have the ability to create databases.

Related Question