Horton

Thread

  1. A modest proposal: get rid of GUC's USERLIMIT variable category

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-09T22:27:14Z

    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_duration
 log_executor_stats
 log_min_duration_statement
 log_min_error_statement
 log_min_messages
 log_parser_stats
 log_planner_stats
 log_statement
 log_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

  2. Re: A modest proposal: get rid of GUC's USERLIMIT variable category

    Greg Stark <gsstark@mit.edu> — 2004-11-10T05:19:58Z

    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

  3. Re: A modest proposal: get rid of GUC's USERLIMIT variable

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-11-10T05:24:45Z

    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, Pennsylvania 19073

  4. Re: A modest proposal: get rid of GUC's USERLIMIT

    Andrew McMillan <andrew@catalyst.net.nz> — 2004-11-10T10:47:24Z

    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/
-------------------------------------------------------------------------

  5. Re: A modest proposal: get rid of GUC's USERLIMIT variable

    Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-11-10T14:54:50Z

    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.

  6. Re: A modest proposal: get rid of GUC's USERLIMIT variable category

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-10T16:45:39Z

    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

  7. Re: A modest proposal: get rid of GUC's USERLIMIT variable category

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-10T17:13:25Z

    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

  8. Re: A modest proposal: get rid of GUC's USERLIMIT variable category

    Greg Stark <gsstark@mit.edu> — 2004-11-11T04:45:52Z

    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

  9. Re: A modest proposal: get rid of GUC's USERLIMIT

    Andrew McMillan <andrew@catalyst.net.nz> — 2004-11-11T23:20:24Z

    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
-------------------------------------------------------------------------

  10. Re: A modest proposal: get rid of GUC's USERLIMIT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-11-12T04:05:05Z

    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, Pennsylvania 19073

  11. Re: A modest proposal: get rid of GUC's USERLIMIT

    Andrew McMillan <andrew@catalyst.net.nz> — 2004-11-13T09:56:52Z

    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
-------------------------------------------------------------------------

  12. Re: A modest proposal: get rid of GUC's USERLIMIT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-11-13T11:06:40Z

    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, Pennsylvania 19073

  13. Re: A modest proposal: get rid of GUC's USERLIMIT

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-14T19:36:05Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, Tom please go ahead with the patch.

Done.

			regards, tom lane