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
On Thu, Oct 5, 2017 at 2:37 PM, Joshua D. Drake <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.
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 Thu, Oct 5, 2017 at 3:05 PM, 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.
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.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
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
Show quoted text
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