Re: pgsql: Fix search_path to a safe value during maintenance operations.

On Thu, 2023-06-29 at 20:53 -0400, Tom Lane wrote:
> I think that's a seriously awful kluge.  It will mean that things
> behave
> differently for the owner than for MAINTAIN grantees, which pretty
> much
> destroys the use-case for that privilege, as well as being very
> confusing
> and hard to debug.

In version 15, try this:

CREATE USER foo;
CREATE SCHEMA foo AUTHORIZATION foo;
CREATE USER bar;
CREATE SCHEMA bar AUTHORIZATION bar;
\c - foo
CREATE FUNCTION foo.mod10(INT) RETURNS INT IMMUTABLE
LANGUAGE plpgsql AS $$ BEGIN RETURN mod($1,10); END; $$;
CREATE TABLE t(i INT);
-- units digit must be unique
CREATE UNIQUE INDEX t_idx ON t (foo.mod10(i));
INSERT INTO t VALUES(7); -- success
INSERT INTO t VALUES(17); -- fails
GRANT USAGE ON SCHEMA foo TO bar;
GRANT INSERT ON t TO bar;
\c - bar
CREATE FUNCTION bar.mod(INT, INT) RETURNS INT IMMUTABLE
LANGUAGE plpgsql AS $$ BEGIN RETURN $1 + 1000000; END; $$;
SET search_path = bar, pg_catalog;
INSERT INTO foo.t VALUES(7); -- succeeds
\c - foo
SELECT * FROM t;
i
---
7
7
(2 rows)

I'm not sure that everyone in this thread realizes just how broken it
is to depend on search_path in a functional index at all. And doubly so
if it depends on a schema other than pg_catalog in the search_path.

Let's also not forget that logical replication always uses
search_path=pg_catalog, so if you depend on a different search_path for
any function attached to the table (not just functional indexes, also
functions inside expressions or trigger functions), then those are
already broken in version 15. And if a superuser is executing
maintenance commands, there's little reason to think they'll have the
same search path as the user that created the table.

At some point in the very near future (though I realize that point may
come after version 16), we need to lock down the search path in a lot
of cases (not just maintenance commands), and I don't see any way
around that.

Regards,
Jeff Davis