Thread: search_path wildcard?
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_%';
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.
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.
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
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.
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
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