Thread: Limiting user from changing its own attributes

On Fri, Apr 10, 2015 at 9:01 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

Hi,

In PostgreSQL a user can alter itself to change its user level parameters. e.g. I can alter the user to change work_mem - 

psql -U user1 -d postgres

postgres=# alter user user user1 set work_mem to '1024000';

​Is this a typo? - the above has a syntax error...​

ALTER ROLE

postgres=#

Is there a way I restrict this behavior? or atleast put a restriction on the certain parameters e.g. work_mem to be not set to too high?

​Not that I'm aware of - and the ability to change parameters is not limited to ALTER ROLE.

Setting "work_mem" too low can be just as problematic as setting it too high.  This one could probably be solved readily enough but you sound like you are looking for some blanket capability to either add targeted security about GUCs or setup a way to alter generically the "upper_bound, lower_bound" ​properties of numeric variables. 

Upper is somewhat easier but currently the system would only recognize a global constraint.

​David J.​

On 4/12/15 11:55 PM, Sameer Kumar wrote:
>
> On Mon, 13 Apr 2015 11:35 Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>> wrote:
>
>     On 4/11/15 4:11 PM, Sameer Kumar wrote:
>      >     Pg_settings currently has an upper bound column - though it is a
>      >     view and that value cannot be changed that I know of.
>      >
>      >
>      > I guess that upper bound column is more of the limit that is
>     imposed by
>      > system which you can have for a parameter i.e. the system imposed
>     limit
>      > or valid range if values for a parameter. I don't think one can
>     update that.
>
>     Correct.
>
>      >     But if it could I suspect that whatever catalog you would
>     change to
>      >     affect it would only cause a global change. There is no alter
>      >     database, role, or postgresql way to change that value.
>      >
>      > Oh ok... anyway of achieving that? There no EVENT trigger for
>     "alter user"?
>
>     There is not, but as David mentioned there's way more ways to modify
>     settings than just ALTER ROLE. Attempting to lock that down won't help
>     you at all.
>
>     Unfortunately, there's no hook support for doing something special when
>     GUCs change, though it might be possible to do something here via
>     planner hooks. That would be pretty complicated and would need to be
>     done in C.
>
>     It doesn't look like SELinux would help either.
>
>     So basically, there is currently no way to restrict someone changing
>     GUCs, other than GUCs that are marked as superuser-only.
>
> Is there anything ecpected in any of the near future release?

No. I suspect the community would support at least a hook for GUC
changes, if not a full-on permissions system. A hook would make it
fairly easy to add event trigger support.

On Sun, Apr 12, 2015 at 10:23 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

On Mon, Apr 13, 2015 at 1:03 PM Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

No. I suspect the community would support at least a hook for GUC
changes, if not a full-on permissions system. A hook would make it
fairly easy to add event trigger support.

I hope someone out there is listening :)

I hope I have made my concern clear, I currently don't have a way to control users from changing the parameter values for their own settings, which allows each user to set in-appropriate values e.g. for work_mem.

If work_mem is the only example you can describe then I'm doubtful that any kind of urgency is going to be associated with this request.

  Your actual request does nothing because the same user can simply issue "SET work_mem" at session start and bypass the user defaults that you want to prevent

You haven't provided enough meat for anyone to offer advice regarding the scenario you are encountering that you think has "restrict alter role" as a solution. 

If you want to take the time to actually paint us a picture then maybe suggestions or motivation for change will result.  But, in the end, the current architecture of PostgreSQL means that people with credentials to the database have the capability to DoS the server.  work_mem is simply one possible avenue and, in reality, one where an inappropriate value can be either too large or too small.

The useful solution here is not restring work_mem but rather having a process in place that provides data regarding excessive memory utilization AND disk I/O and associating that data with the work_mem value and executing user.  The event triggers would also allow for monitoring, without setting an excessive log_statements level, changes and their values.

If you really care about their "set role" aspect you can at least setup cron shell script to query the catalog and report any undesirable settings and maybe even remove the offending entry.  You are still open to "SET work_mem" during the session though...

David J.

 

On Mon, May 4, 2015 at 10:23 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

Sorry about the long silence on this.

On Mon, Apr 13, 2015 at 3:34 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Sun, Apr 12, 2015 at 10:23 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

On Mon, Apr 13, 2015 at 1:03 PM Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

No. I suspect the community would support at least a hook for GUC
changes, if not a full-on permissions system. A hook would make it
fairly easy to add event trigger support.

I hope someone out there is listening :)

I hope I have made my concern clear, I currently don't have a way to control users from changing the parameter values for their own settings, which allows each user to set in-appropriate values e.g. for work_mem.

If work_mem is the only example you can describe then I'm doubtful that any kind of urgency is going to be associated with this request.

I guess any parameter which can be altered at the user level should have some limitations e.g. one may not want to allow a user to reset its own level of client messages. 

As an admin one may want to have control over what a user can alter and what the user can not alter. 

e.g. 

"

ALTER USER my_app_user  restrict set for client_min_messages;

​I make a point below but the server should not care what level of logging messages the client wants to receive...is there some kind of security issue with a overly verbose specification here?  Are you concerned about resource utilization by said clients?​

  Your actual request does nothing because the same user can simply issue "SET work_mem" at session start and bypass the user defaults that you want to prevent

 

My point is the limitations imposed should not be just at user level but should also be inherited by all sessions made by that user.

​While that can be inferred it is good to be explicit in order to communicate understanding of the current reality.​

 

You haven't provided enough meat for anyone to offer advice regarding the scenario you are encountering that you think has "restrict alter role" as a solution. 

To put it very simply as a DBA one would want to be in control of what the users in that environment can change about themselves and what they can not.

​OK.  This is already the case for numerous things and a blanket statement like this doesn't help others understand where we are lacking.​

 

If you want to take the time to actually paint us a picture then maybe suggestions or motivation for change will result.  But, in the end, the current architecture of PostgreSQL means that people with credentials to the database have the capability to DoS the server.  work_mem is simply one possible avenue and, in reality, one where an inappropriate value can be either too large or too small.

Yes! Plus as an user I can change the logging parameter for my account (which as well is risky in many environment)

​You seem to need a better understanding of what limitations are already in place.  While it is true you can alter "client_min_messages" you cannot alter "log_min_messages" (in addition to quite a few other log related settings).

​http://www.postgresql.org/docs/9.4/static/runtime-config-logging.html​

You make a claim that altering client_min_messages is "risk in many environment [sic]" but provide zero substantiation for that claim.

The useful solution here is not restring work_mem but rather having a process in place that provides data regarding excessive memory utilization AND disk I/O and associating that data with the work_mem value and executing user.  The event triggers would also allow for monitoring, without setting an excessive log_statements level, changes and their values.

But that is more of a cure, won't it be a good idea to have some level of preventive measures to ensure DBAs can control which user can change which "user-level" value (and even better if we can attach a limit to it).

​Most likely such a patch would be accepted by the community.  If you are depending on someone else writing it the muted response to this thread should be discouraging.

David J.