Thread: search_path wildcard?

search_path wildcard?

From
Ron Johnson
Date:
This doesn't work, and I've found nothing similar:
ALTER ROLE foo SET SEARCH_PATH  = '*';

Is there a single SQL statement which will generate a search path based on information_schema.schemata, or do I have to write an anonymous DO procedure?
SELECT schema_name FROM information_schema.schemata WHERE schema_name != 'information_schema' AND schema_name NOT LIKE 'pg_%';

Re: search_path wildcard?

From
"David G. Johnston"
Date:
On Wed, May 22, 2024, 10:36 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
This doesn't work, and I've found nothing similar:
ALTER ROLE foo SET SEARCH_PATH  = '*';

Correct, you cannot do that.

David J.

Re: search_path wildcard?

From
Ron Johnson
Date:
On Wed, May 22, 2024 at 12:53 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, May 22, 2024, 10:36 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
This doesn't work, and I've found nothing similar:
ALTER ROLE foo SET SEARCH_PATH  = '*';

Correct, you cannot do that.

That would be a helpful feature for administrators, when there are multiple schemas in multiple databases, on multiple servers: superusers get ALTER ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

Re: search_path wildcard?

From
Tom Lane
Date:
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> That would be a helpful feature for administrators, when there are multiple
> schemas in multiple databases, on multiple servers: superusers get ALTER
> ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

... and they're pwned within five minutes by any user with the wits
to create a trojan-horse function or operator.  Generally speaking,
you want admins to run with a minimal search path not a maximal one.

            regards, tom lane



Re: search_path wildcard?

From
Ron Johnson
Date:
On Wed, May 22, 2024 at 1:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> That would be a helpful feature for administrators, when there are multiple
> schemas in multiple databases, on multiple servers: superusers get ALTER
> ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

... and they're pwned within five minutes by any user with the wits
to create a trojan-horse function or operator.  Generally speaking,
you want admins to run with a minimal search path not a maximal one.
 
Missing tables when running "\t" is a bigger hassle.

Re: search_path wildcard?

From
Pavel Stehule
Date:


st 22. 5. 2024 v 19:54 odesílatel Ron Johnson <ronljohnsonjr@gmail.com> napsal:
On Wed, May 22, 2024 at 12:53 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, May 22, 2024, 10:36 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
This doesn't work, and I've found nothing similar:
ALTER ROLE foo SET SEARCH_PATH  = '*';

Correct, you cannot do that.

That would be a helpful feature for administrators, when there are multiple schemas in multiple databases, on multiple servers: superusers get ALTER ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

It can be pretty dangerous, because you don't specify order of schemas 

Regards

Pavel

Re: search_path wildcard?

From
Pavel Stehule
Date:


st 22. 5. 2024 v 21:13 odesílatel Ron Johnson <ronljohnsonjr@gmail.com> napsal:
On Wed, May 22, 2024 at 1:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> That would be a helpful feature for administrators, when there are multiple
> schemas in multiple databases, on multiple servers: superusers get ALTER
> ROLE foo SET SEARCH_PATH  = '*'; and they're done with it.

... and they're pwned within five minutes by any user with the wits
to create a trojan-horse function or operator.  Generally speaking,
you want admins to run with a minimal search path not a maximal one.
 
Missing tables when running "\t" is a bigger hassle.

what is hard on \dt *.*

or you can define own 

 dtall = '\\dt *.*'

:dtall

The problem is not on search path, but maybe on design backslash commands - but there should be some level of consistency

Regards

Pavel