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?
Best How To :
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
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.