Message-ID: <d4ccaf3658cb3c281ec88c851a09733cd9482f22.camel@j-davis.com>
Subject: MAINTAIN privilege -- what do we need to un-revert it?
From: Jeff Davis <pgsql@j-davis.com>
To: pgsql-hackers@postgresql.org
Cc: Joe Conway <mail@joeconway.com>, Robert Haas <robertmhaas@gmail.com>,
	Nathan Bossart <nathandbossart@gmail.com>
Date: Wed, 14 Feb 2024 10:20:28 -0800
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d4ccaf3658cb3c281ec88c851a09733cd9482f22.camel%40j-davis.com>
Precedence: bulk

The MAINTAIN privilege was reverted during the 16 cycle because of the
potential for someone to play tricks with search_path.

For instance, if user foo does:

   CREATE FUNCTION mod7(INT) RETURNS INT IMMUTABLE
     LANGUAGE plpgsql AS $$ BEGIN RETURN mod($1, 7); END; $$;
   CREATE TABLE x(i INT);
   CREATE UNIQUE INDEX=C2=A0x_mod7_idx ON x (mod7(i));
   GRANT MAINTAIN ON x TO bar;

Then user bar can create their own function named "bar.mod(int, int)",
and "SET search_path =3D bar, pg_catalog", and then issue a "REINDEX x"
and cause problems.

There are several factors required for that to be a problem:

  1. foo hasn't used a "SET search_path" clause on their function
  2. bar must have the privileges to create a function somewhere
  3. bar must have privileges on table x

There's an argument that we should blame factor #1. Robert stated[1]
that users should use SET search_path clauses on their functions, even
SECURITY INVOKER functions. And I've added a search_path cache which
improves the performance enough to make that more reasonable to do
generally.

There's also an argument that #2 is to blame. Given the realities of
our system, best practice is that users shouldn't have the privileges
to create objects, even in their own schema, unless required. (Joe made
this suggestion in an offline discussion.)

There's also an arugment that #3 is not specific to the MAINTAIN
privilege. Clearly similar risks exist for other privileges, like
TRIGGER. And even the INSERT privilege, in the above example, would
allow bar to violate the unique constraint and corrupt the index[2].

If those arguments are still unconvincing, then the next idea is to fix
the search_path for all maintenance commands[3]. I tried this during
the 16 cycle, but due to timing issues it was also reverted. I can
proceed with this approach again, but I'd like a clear endorsement, in
case there were other reasons to doubt the approach.

Regards,
	Jeff Davis

[1]
https://www.postgresql.org/message-id/CA+TgmoYEP40iBW-A9nPfDp8AhGoekPp3aPDF=
zTgBUrqmfCwZzQ@mail.gmail.com

[2]
https://www.postgresql.org/message-id/fff566293c9165c69bb4c555da1ac02c63660=
664.camel@j-davis.com

[3]
https://www.postgresql.org/message-id/e44327179e5c9015c8dda67351c04da552066=
017.camel@j-davis.com