Re: security_definer_search_path GUC

On Sat, May 29, 2021, at 22:10, Marko Tiikkaja wrote:
> On Sat, May 29, 2021 at 11:06 PM Joel Jacobson <joel(at)compiler(dot)org> wrote:
>> __
>> Glad you bring this problem up for discussion, something should be done to improve the situation.
>>
>> Personally, as I really dislike search_path and consider using it an anti-pattern.
>> I would rather prefer a GUC to hard-code search_path to a constant default value of just ‘public’ that cannot be changed by anyone or any function. Trying to change it to a different value would raise an exception.
>
> That would work, too! I think it's a nice idea, perhaps even better than what I proposed. I would be happy to see either one incorporated.

Another idea would be to create an extension that removes the search_path feature entirely,
not sure though if the current hooks would allow creating such an extension.

Maybe "extensions" that only removes unwanted core features could be by convention be prefixed with "no_"?

CREATE EXTENSION no_search_path;

That way, a company with a company-wide policy against using search_path,
could add this to all their company .control extension files:

requires = 'no_search_path'

If some employee would try to `DROP EXTENSION no_search_path` they would get an error:

# DROP EXTENSION no_search_path;
ERROR: cannot drop extension no_search_path because other objects depend on it
DETAIL: extension acme_inc depends on extension no_search_path

This would be especially useful when a company has a policy to use some extension,
instead of relying on the built-in functionality provided.
I'm not using "zson" myself, but perhaps it could be a good example to illustrate my point:

Let's say a company has decided to use zson instead of json/jsonb,
the company would then ensure nothing is using json/jsonb
via the top-level .control file for the company's own extension:

requires = 'no_json, no_jsonb, zson'

Or if not shipping the company's product as an extension,
they could instead add this to the company's install script:

CREATE EXTENSION no_json;
CREATE EXTENSION no_jsonb;
CREATE EXTENSION zson;

Maybe this is out of scope for extensions, since I guess extensions are supposed to add features?

If so, how about a new separate command `CREATE REDUCTION` specifically to remove unwanted core features,
which then wouldn't need the "no_" prefix since it would be implicit and in a different namespace:

E.g.

CREATE REDUCTION search_path;

and

CREATE REDUCTION json;
CREATE REDUCTION jsonb;
CREATE EXTENSION zson;

/Joel