Thread: Problems with PGOPTIONS

Hi all,

I have a devel server where i want to turn on collector statistics.
I want to enable the collection of STATS_ROW_LEVEL for special backends
by setting the environment-variable export PGOPTIONS='-c STATS_ROW_LEVEL=ON'.
After setting this variable i cant connect to my database.
Everytime the following error occurs:

Connection to database 'mydb' failed.
FATAL 1:  permission denied

After unset PGOPTIONS everything works without any problems.
I have tested the same with an example C-program found under http://www.postgresql.org/idocs/
but always got the same error if i want to send additional options.

Did i make some mistakes ?

Thanks in advance for help

as

Andre Schubert <andre@km3.de> writes:
> I want to enable the collection of STATS_ROW_LEVEL for special backends
> by setting the environment-variable export PGOPTIONS='-c STATS_ROW_LEVEL=ON'.
> After setting this variable i cant connect to my database.
> Everytime the following error occurs:

> Connection to database 'mydb' failed.
> FATAL 1:  permission denied

You cannot alter stats_row_level unless you're a superuser (and I think
even that exception doesn't work for PGOPTIONS, because it's processed
before the backend has determined whether you are a superuser).  You'd
get the same error if you tried

regression=> set STATS_ROW_LEVEL = on;
ERROR:  'stats_row_level': permission denied

            regards, tom lane

On Wed, 15 Jan 2003 10:01:27 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Andre Schubert <andre@km3.de> writes:
> > I want to enable the collection of STATS_ROW_LEVEL for special backends
> > by setting the environment-variable export PGOPTIONS='-c STATS_ROW_LEVEL=ON'.
> > After setting this variable i cant connect to my database.
> > Everytime the following error occurs:
>
> > Connection to database 'mydb' failed.
> > FATAL 1:  permission denied
>
> You cannot alter stats_row_level unless you're a superuser (and I think
> even that exception doesn't work for PGOPTIONS, because it's processed
> before the backend has determined whether you are a superuser).  You'd
> get the same error if you tried
>
> regression=> set STATS_ROW_LEVEL = on;
> ERROR:  'stats_row_level': permission denied
>

Ok, but is there another way to enable STATS_ROW_LEVEL for a special backend not
connecting as superuser ?

Regards, as

Andre Schubert <andre@km3.de> writes:
> Ok, but is there another way to enable STATS_ROW_LEVEL for a special
> backend not connecting as superuser ?

No: the code is deliberately designed to prevent that, on the grounds
that it'd be a security flaw (non-superusers should not be allowed to
hide their activities from the DBA).

There's nothing to stop you from making a private version with
stats_row_level downgraded to an ordinary unprotected SET variable,
though, if that seems more appropriate to your needs.  See its entry in
src/backend/utils/misc/guc.c and change it from SUSET to USERSET
category (from memory; check spelling in src/include/utils/guc.h).

            regards, tom lane

On Wed, 15 Jan 2003 11:19:29 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Andre Schubert <andre@km3.de> writes:
> > Ok, but is there another way to enable STATS_ROW_LEVEL for a special
> > backend not connecting as superuser ?
>
> No: the code is deliberately designed to prevent that, on the grounds
> that it'd be a security flaw (non-superusers should not be allowed to
> hide their activities from the DBA).
>

Lets say, that the statistics that i would collect come from several scripts
controlled and programmed by me.
All these scripts are connecting as different users to my db.
Is there a way to chnage these users to superusers and if yes would it be a
security flaw to change these users to superusers ?

> There's nothing to stop you from making a private version with
> stats_row_level downgraded to an ordinary unprotected SET variable,
> though, if that seems more appropriate to your needs.  See its entry in
> src/backend/utils/misc/guc.c and change it from SUSET to USERSET
> category (from memory; check spelling in src/include/utils/guc.h).
>

I will test this.

Thanks for the quick answer.

Regards, as