Re: security_definer_search_path GUC

ne 30. 5. 2021 v 8:52 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> 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:
>

Maybe inverted design can work better - there can be GUC -
"qualified_names_required" with a list of schemas without enabled implicit
access.

The one possible value can be "all".

The advantage of this design can be the possibility of work on current
extensions.

I don't think so search_path can be disabled - but there can be checks that
disallow non-qualified names.

Pavel

> 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
>