Обсуждение: [HACKERS] search path security issue?
-hackers, Please see the below: """ postgres=# create user foo; CREATE ROLE postgres=# create schema foo; CREATE SCHEMA postgres=# alter role foo set search_path to 'foo'; ALTER ROLE postgres=# \q jd@jd-wks:~$ psql -U foo postgres psql (9.6.5) Type "help" for help. postgres=> show search_path; search_path ------------- foo (1 row) postgres=> alter role foo set search_path to default; ALTER ROLE postgres=> show search_path; search_path ------------- foo (1 row) postgres=> \q jd@jd-wks:~$ psql -U foo postgres psql (9.6.5) Type "help" for help. postgres=> show search_path; search_path ----------------- "$user", public (1 row) I get being able to change my search_path on the fly but it seems odd that as user foo I can change my default search path? JD -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc PostgreSQL Centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://pgconf.us ***** Unless otherwise stated, opinions are my own. ***** -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
"Joshua D. Drake" <jd@commandprompt.com> writes:
> I get being able to change my search_path on the fly but it seems odd
> that as user foo I can change my default search path?
Why is that odd? It's a USERSET variable.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I get being able to change my search_path on the fly but it seems odd that as user foo I can change my default search path?
Seems down-right thoughtful of us to allow users to change their own defaults instead of forcing them to always change things on-the-fly or bug a DBA to change the default for them.
David J.
On 10/05/2017 02:54 PM, David G. Johnston wrote: > On Thu, Oct 5, 2017 at 2:37 PM, Joshua D. Drake <jd@commandprompt.com > <mailto:jd@commandprompt.com>>wrote: > > I get being able to change my search_path on the fly but it seems > odd that as user foo I can change my default search path? > > > Seems down-right thoughtful of us to allow users to change their own > defaults instead of forcing them to always change things on-the-fly or > bug a DBA to change the default for them. It seems that if a super user changes the search path with ALTER USER/ROLE, then the user itself should not (assuming not an elevated privilege) should not be able to change it. Again, I get being able to do it with SET but a normal user shouldn't be able to reset a super user determined setting. Shrug, JD > > David J. > -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc PostgreSQL Centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://pgconf.us ***** Unless otherwise stated, opinions are my own. ***** -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
On 10/05/2017 02:54 PM, David G. Johnston wrote:On Thu, Oct 5, 2017 at 2:37 PM, Joshua D. Drake <jd@commandprompt.com <mailto:jd@commandprompt.com>>wrote:
I get being able to change my search_path on the fly but it seems
odd that as user foo I can change my default search path?
Seems down-right thoughtful of us to allow users to change their own defaults instead of forcing them to always change things on-the-fly or bug a DBA to change the default for them.
It seems that if a super user changes the search path with ALTER USER/ROLE, then the user itself should not (assuming not an elevated privilege) should not be able to change it. Again, I get being able to do it with SET but a normal user shouldn't be able to reset a super user determined setting.
If the superuser really wanted to enforce it they could, a bit verbosely, set the default for the user for every database explicitly so that the database+role setting takes precedence over the role-only setting.
I get where you are coming from but there is no demonstrated security-related reason to enforce such a restriction and so adding the book-keeping necessary to track "who done it" (i.e. mere presence of a value is an insufficient distinguishing characteristic) has a cost but no benefit (if the superuser is upset that someone changed their own default search_path the fact that said user is entrusted with login credentials should be questioned).
David J.
On Fri, Oct 6, 2017 at 12:05 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
On 10/05/2017 02:54 PM, David G. Johnston wrote:On Thu, Oct 5, 2017 at 2:37 PM, Joshua D. Drake <jd@commandprompt.com <mailto:jd@commandprompt.com>>wrote:
I get being able to change my search_path on the fly but it seems
odd that as user foo I can change my default search path?
Seems down-right thoughtful of us to allow users to change their own defaults instead of forcing them to always change things on-the-fly or bug a DBA to change the default for them.
It seems that if a super user changes the search path with ALTER USER/ROLE, then the user itself should not (assuming not an elevated privilege) should not be able to change it. Again, I get being able to do it with SET but a normal user shouldn't be able to reset a super user determined setting.
This is in no way specific to search_path.
It would be a nice feature to have in general, like a "basic guc permissions" thing. At least allowing a superuser to prevent exactly this. You could argue the same thing for example for memory parameters and such. We have no permissions at all when it comes to userset gucs today -- and of course, if something should be added about this, it should be done in a way that works for all the userset variables, not just search_path.
On 10/06/2017 12:52 AM, Magnus Hagander wrote: > It would be a nice feature to have in general, like a "basic guc > permissions" thing. At least allowing a superuser to prevent exactly > this. You could argue the same thing for example for memory parameters > and such. We have no permissions at all when it comes to userset gucs > today -- and of course, if something should be added about this, it > should be done in a way that works for all the userset variables, not > just search_path. +1 I have wished for exactly that more than once before. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
On Sun, Oct 8, 2017 at 3:34 PM, Joe Conway <mail@joeconway.com> wrote:
>
> On 10/06/2017 12:52 AM, Magnus Hagander wrote:
> > It would be a nice feature to have in general, like a "basic guc
> > permissions" thing. At least allowing a superuser to prevent exactly
> > this. You could argue the same thing for example for memory parameters
> > and such. We have no permissions at all when it comes to userset gucs
> > today -- and of course, if something should be added about this, it
> > should be done in a way that works for all the userset variables, not
> > just search_path.
>
> +1
>
> I have wished for exactly that more than once before.
>
+1
I have a multi-user and schema database with some user restrictions because it's a shared environment (i.e: statement_timeout, lock_timeout). But some "smart" users change to the GUC default and sometimes we suffer with it. Would be nice to have some kind of guc permissions.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello