Thread: A modest proposal: get rid of GUC's USERLIMIT variable category
I'd like to propose that we get rid of GUC's USERLIMIT category and convert all the variables in it to plain SUSET. In my mind, USERLIMIT is a failed experiment: it's way too complicated, and it still doesn't do quite what it was intended to do, because there are times when it can't check whether you're a superuser. The only variables that are in the category are log-verbosity-related: regression=# select name from pg_settings where context = 'userlimit'; name ----------------------------log_durationlog_executor_statslog_min_duration_statementlog_min_error_statementlog_min_messageslog_parser_statslog_planner_statslog_statementlog_statement_stats (9 rows) What the USERLIMIT code tries to do is allow non-superusers to "increase" but not "decrease" the logging verbosity for their sessions only. (For instance, a non-superuser could turn log_duration on, but can't turn it off if the DBA has turned it on.) However, the usefulness of this capability is really pretty debatable. A non-superuser presumably doesn't have access to the postmaster log file anyhow, so why does he need to be able to turn up the logging? You could even argue that being able to flood the logs with stuff the DBA doesn't want is a mild form of DOS attack. If we do get rid of USERLIMIT, another benefit accrues: we can assume that ALTER USER and ALTER DATABASE settings were fully checked when they were installed, and thereby accept them at session start without any extra permissions check. This would mean that, for example, a superuser could use ALTER USER to set these variables on a per-user basis for non-superusers, and it would actually work. Right now the value is rechecked as if the setting were being issued by the non-superuser, and so it may fail. For more discussion see this thread in pgsql-bugs: http://archives.postgresql.org/pgsql-bugs/2004-11/msg00101.php regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I'd like to propose that we get rid of GUC's USERLIMIT category and > convert all the variables in it to plain SUSET. In my mind, USERLIMIT > is a failed experiment: it's way too complicated, and it still doesn't > do quite what it was intended to do, because there are times when it > can't check whether you're a superuser. > > The only variables that are in the category are log-verbosity-related: Would that mean I wouldn't be able to change the logging level on the fly at all? That would disappoint at least one user, myself. I've found the best debugging compromise is to leave log_statement off in general but have a magic parameter I can pass to the application that will set log_statement = true for a single transaction. That way I can look at what queries transpired in my session without having to dig through hundreds of other queries from other sessions. And have the complete logs for the session I'm debugging without the performance impact in the normal case. -- greg
Greg Stark wrote: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > I'd like to propose that we get rid of GUC's USERLIMIT category and > > convert all the variables in it to plain SUSET. In my mind, USERLIMIT > > is a failed experiment: it's way too complicated, and it still doesn't > > do quite what it was intended to do, because there are times when it > > can't check whether you're a superuser. > > > > The only variables that are in the category are log-verbosity-related: > > Would that mean I wouldn't be able to change the logging level on the fly at > all? > > That would disappoint at least one user, myself. I've found the best debugging > compromise is to leave log_statement off in general but have a magic parameter > I can pass to the application that will set log_statement = true for a single > transaction. > > That way I can look at what queries transpired in my session without having to > dig through hundreds of other queries from other sessions. And have the > complete logs for the session I'm debugging without the performance impact in > the normal case. Yes, this would not be possible for non-super users with the new proposal. You could set the setting for non-super users per-user but not per-session. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 2004-11-09 at 17:27 -0500, Tom Lane wrote: > > What the USERLIMIT code tries to do is allow non-superusers to > "increase" but not "decrease" the logging verbosity for their sessions > only. (For instance, a non-superuser could turn log_duration on, but > can't turn it off if the DBA has turned it on.) However, the usefulness > of this capability is really pretty debatable. A non-superuser > presumably doesn't have access to the postmaster log file anyhow, so why > does he need to be able to turn up the logging? You could even argue > that being able to flood the logs with stuff the DBA doesn't want is a > mild form of DOS attack. Hi Tom, While it may be true that a user can't see those logs, in a narrow sense, it is not true in a wider sense. How many systems/accounts are you currently logged into yourself? The set of "people who control SQL queries" does have a large overlap with other sets like "people who write and debug software" and "people who have access to system logs". When tracking down gnarly problems in heavily multi-user applications enabling higher log levels at selective points has the potential to help _a lot_ with diagnostic detail, without smothering you in _every_ detail. Regards, Andrew McMillan. ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Where are they now? http://schoolreunions.co.nz/ -------------------------------------------------------------------------
On Tue, 10 Nov 2004, Greg Stark wrote: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > I'd like to propose that we get rid of GUC's USERLIMIT category and > > convert all the variables in it to plain SUSET. In my mind, USERLIMIT > > is a failed experiment: it's way too complicated, and it still doesn't > > do quite what it was intended to do, because there are times when it > > can't check whether you're a superuser. > > > > The only variables that are in the category are log-verbosity-related: > > Would that mean I wouldn't be able to change the logging level on the fly at > all? I would think you'd still be able to do it through a security definer wrapper function owned by a superuser.
Andrew McMillan <andrew@catalyst.net.nz> writes:
> When tracking down gnarly problems in heavily multi-user applications
> enabling higher log levels at selective points has the potential to help
> _a lot_ with diagnostic detail, without smothering you in _every_
> detail.
Sure. As I pointed out in the other thread, if you want to allow an app
to do this, you can make available a SECURITY DEFINER function that
performs the desired SET on its behalf. By setting execute permissions
on the function and/or including restrictions in the function's code,
you can make this as tight or as loose a loophole as you like. So it's
certainly possible to do what you want in any case. I think the issue
at hand is what's appropriate to provide as hard-wired functionality.
regards, tom lane
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I'd like to propose that we get rid of GUC's USERLIMIT category and
>> convert all the variables in it to plain SUSET.
> Would that mean I wouldn't be able to change the logging level on the fly at
> all?
No, it would mean that you'd need to be superuser to change it.
regards, tom lane
Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > Would that mean I wouldn't be able to change the logging level on the fly at > > all? > > I would think you'd still be able to do it through a security definer > wrapper function owned by a superuser. Oh yeah, well that would be sufficient for my purposes. I must say I thought the behaviour of being able to raise but not lower logging levels beyond what the system had set was pretty slick when I first found out about it. But it's not the most important thing in the world, as long as there's an escape hatch. -- greg
On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote: > Andrew McMillan <andrew@catalyst.net.nz> writes: > > When tracking down gnarly problems in heavily multi-user applications > > enabling higher log levels at selective points has the potential to help > > _a lot_ with diagnostic detail, without smothering you in _every_ > > detail. > > Sure. As I pointed out in the other thread, if you want to allow an app > to do this, you can make available a SECURITY DEFINER function that > performs the desired SET on its behalf. By setting execute permissions > on the function and/or including restrictions in the function's code, > you can make this as tight or as loose a loophole as you like. So it's > certainly possible to do what you want in any case. I think the issue > at hand is what's appropriate to provide as hard-wired functionality. That sounds excellent - I hadn't realised that this workaround would be possible, and indeed with this in place that will provide even better control over the facility. Regards, Andrew. ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 The secret of being a bore is to say everything-- Voltaire -------------------------------------------------------------------------
Andrew McMillan wrote: -- Start of PGP signed section. > On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote: > > Andrew McMillan <andrew@catalyst.net.nz> writes: > > > When tracking down gnarly problems in heavily multi-user applications > > > enabling higher log levels at selective points has the potential to help > > > _a lot_ with diagnostic detail, without smothering you in _every_ > > > detail. > > > > Sure. As I pointed out in the other thread, if you want to allow an app > > to do this, you can make available a SECURITY DEFINER function that > > performs the desired SET on its behalf. By setting execute permissions > > on the function and/or including restrictions in the function's code, > > you can make this as tight or as loose a loophole as you like. So it's > > certainly possible to do what you want in any case. I think the issue > > at hand is what's appropriate to provide as hard-wired functionality. > > That sounds excellent - I hadn't realised that this workaround would be > possible, and indeed with this in place that will provide even better > control over the facility. OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you were the person who originally mentioned the problem. You don't think the function creation is hard. Perhaps that's the way to go then. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, 2004-11-11 at 23:05 -0500, Bruce Momjian wrote: > Andrew McMillan wrote: > -- Start of PGP signed section. > > On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote: > > > Andrew McMillan <andrew@catalyst.net.nz> writes: > > > > When tracking down gnarly problems in heavily multi-user applications > > > > enabling higher log levels at selective points has the potential to help > > > > _a lot_ with diagnostic detail, without smothering you in _every_ > > > > detail. > > > > > > Sure. As I pointed out in the other thread, if you want to allow an app > > > to do this, you can make available a SECURITY DEFINER function that > > > performs the desired SET on its behalf. By setting execute permissions > > > on the function and/or including restrictions in the function's code, > > > you can make this as tight or as loose a loophole as you like. So it's > > > certainly possible to do what you want in any case. I think the issue > > > at hand is what's appropriate to provide as hard-wired functionality. > > > > That sounds excellent - I hadn't realised that this workaround would be > > possible, and indeed with this in place that will provide even better > > control over the facility. > > OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you > were the person who originally mentioned the problem. You don't think > the function creation is hard. Perhaps that's the way to go then. Yes, I agree - it seems good. Also, I don't see that this function would need to be "written under stress" as ISTR you suggested elsewhere - any analysis like this is going to be following on from review of other statistics - I think it would normally be a well-planned process. Cheers, Andrew. ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 How many things I can do without! --Socrates -------------------------------------------------------------------------
Andrew McMillan wrote: > > > That sounds excellent - I hadn't realised that this workaround would be > > > possible, and indeed with this in place that will provide even better > > > control over the facility. > > > > OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you > > were the person who originally mentioned the problem. You don't think > > the function creation is hard. Perhaps that's the way to go then. > > Yes, I agree - it seems good. > > Also, I don't see that this function would need to be "written under > stress" as ISTR you suggested elsewhere - any analysis like this is > going to be following on from review of other statistics - I think it > would normally be a well-planned process. OK, Tom please go ahead with the patch. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, Tom please go ahead with the patch.
Done.
regards, tom lane