Superuser Role Specific to certain Databases in PostgreSQL

Question

I have created a User Role with superuser privilege. I have around 30 Databases on my server. I want to assign this role to only only DB. The current role lets the user access all the DBs as super user. How can I restrict him from accessing other DBs as super user.

This is the that I have for assigning superuser:

CREATE ROLE fc LOGIN
   SUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Can someone help me with this?

Answer 1

As @Craig explained, you can't (and even if you could, it would be fairly pointless).

The usual way of implementing restricted superuser permissions is to connect as an existing superuser role, and create SECURITY DEFINER functions containing a limited set of approved commands. These functions will now be executed with the privileges of the creator rather than the caller.

But you need to be very careful not to open any injection vulnerabilities, because everything within the function will be run as superuser. E.g. the caller could write a custom = operator which grants them superuser rights, and put it in their search path, so you need to be absolutely sure that you're using the = in the pg_catalog schema.

At the very least, you should:

• Create all of these functions with the clause SECURITY DEFINER SET search_path TO pg_catalog, pg_temp. The pg_temp schema must always be included at the end of the list (if omitted, it will be implicitly included at the start).
• Schema-qualify any other tables, functions, etc. that your function references (e.g. public.MyTable instead of just MyTable), and make sure that all of these are superuser-owned (so that callers can't put malicious code in triggers, etc.).
• Never put user input in a dynamic query string (EXECUTE 'SELECT ...') without exhaustive validation.

Answer 2

There is no facility in PostgreSQL for a database-specific superuser.

It would not make sense anyway, since generally operations that are superuser-only are things that permit relatively easy escalation to greater control over the database system.