How can I disable a superuser in postgres

Question

On a server I have two databases ( say db1 and db2 ). I have a superuser called user1.

My requirement is to disable user1(super user) for database db1. So that using user1 I can only connect to db2 and not to db1.

How can this be done.

Note : postgres version is 8.0 and both the databases are on same database cluster.

Answer 1

Remove their superuser rights entirely. Make them the owner of db2 (ALTER DATABASE db2 OWNER TO whatever_user), so they can do anything to db2 except limited superuser-only operations like loading C extensions.

You cannot restrict superusers. That's the point. Superuser-only operations are ones that break through the usual access control rules. For example, loading a user-defined C function allows you to write and load a function that opens pg_hba.conf and rewrites it, or just manipulates the system catalogs directly. Similarly, the adminpack functions let you do direct file system access, so they're superuser-only.

If they're a superuser, they can just read pg_hba.conf, see that your user ID has the right to log in to db1, then change your password then log in as you.

Asking to limit a superuser to one DB is like asking if you can make a user root, but only for one subdirectory. (OK, so with SELinux you can kind-of do that, but it's complicated).

If you truly must do this, the only way to do it is to split db1 and db2 into different PostgreSQL servers running under different unpriveleged system user IDs. Each has its own separate shared_buffers, data directory, listening (ip-address, port) combo, WAL, user IDs, database lists, etc. Since they're running under different system users they don't have the right to read or write each others' data directories, so they are isolated. They must listen on different ports and/or different IP addresses, though you can use PgBouncer to make them appear to be the same server to external clients.