2

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?

Yousuf Sultan
  • 3,055
  • 8
  • 35
  • 63
  • 1
    Why do you want to do this? What is the underlying problem you are trying to solve here? – Craig Ringer Jun 19 '15 at 03:28
  • We have different modules in our project, for each of the modules, the DBs are on the same server. So want to make the superusers module specific therefore wanted to apply it on individual basis – Yousuf Sultan Jun 19 '15 at 03:39
  • 1
    OK, but why do you need them to be superuser? Are you sure you don't just need them to be the database owner? – Craig Ringer Jun 19 '15 at 06:13

2 Answers2

4

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.
Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778