Hi every one,

Is there a way for joe user (not superuser) to see current query on HIS
database on pg_stat_activity.

ISTM that only superuser can see current query for all.

As fr security, it doesn't seem right that select * from pg_stat_activity
shows all databases.

What do you think?

-- 
Olivier PRENANT        	        Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

Mon, 7 Jul 2003 17:39:44 +0200 (MET DST)
ohp@pyrenet.fr
pg_stat_activity

You can create function with security definer as super user
sth like this
create or replace function func () returns setof pg_stat_activity as '
select * from pg_stat_activity ; ' language SQL SECURITY DEFINER;

and if you want to se only query of session user you need to replace
* to columns from view pg_stat_activity , and to column query
you need to use CASE , when username == SESSION_USER : cur_query,
else null;

On Mon, 7 Jul 2003 ohp@pyrenet.fr wrote:

> Hi every one,
>
> Is there a way for joe user (not superuser) to see current query on HIS
> database on pg_stat_activity.
>
> ISTM that only superuser can see current query for all.
>
> As fr security, it doesn't seem right that select * from pg_stat_activity
> shows all databases.
>
> What do you think?
>
> --
> Olivier PRENANT        	        Tel: +33-5-61-50-97-00 (Work)
> 6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
> 31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
> FRANCE                          Email: ohp@pyrenet.fr
> ------------------------------------------------------------------------------
> Make your life a dream, make your dream a reality. (St Exupery)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Mon, 7 Jul 2003 21:21:32 +0200 (CEST)
ivan <iv@psycho.pl>
Re: pg_stat_activity
Hi ivan,

Sounds like a plan, I'll try it, many thanks!!

Still, shouldn't there be a view like this : select * from db_activity
wher it returns stat_activity only for current database??

Regaerds
On Mon, 7 Jul 2003, ivan wrote:

> Date: Mon, 7 Jul 2003 21:21:32 +0200 (CEST)
> From: ivan <iv@psycho.pl>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] pg_stat_activity
>
>
> You can create function with security definer as super user
> sth like this
> create or replace function func () returns setof pg_stat_activity as '
> select * from pg_stat_activity ; ' language SQL SECURITY DEFINER;
>
> and if you want to se only query of session user you need to replace
> * to columns from view pg_stat_activity , and to column query
> you need to use CASE , when username == SESSION_USER : cur_query,
> else null;
>
> On Mon, 7 Jul 2003 ohp@pyrenet.fr wrote:
>
> > Hi every one,
> >
> > Is there a way for joe user (not superuser) to see current query on HIS
> > database on pg_stat_activity.
> >
> > ISTM that only superuser can see current query for all.
> >
> > As fr security, it doesn't seem right that select * from pg_stat_activity
> > shows all databases.
> >
> > What do you think?
> >
> > --
> > Olivier PRENANT        	        Tel: +33-5-61-50-97-00 (Work)
> > 6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
> > 31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
> > FRANCE                          Email: ohp@pyrenet.fr
> > ------------------------------------------------------------------------------
> > Make your life a dream, make your dream a reality. (St Exupery)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>

-- 
Olivier PRENANT        	        Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

Mon, 7 Jul 2003 22:34:09 +0200 (MET DST)
ohp@pyrenet.fr
Re: pg_stat_activity
ivan wrote:
> 
> You can create function with security definer as super user
> sth like this
> create or replace function func () returns setof pg_stat_activity as '
> select * from pg_stat_activity ; ' language SQL SECURITY DEFINER;
> 
> and if you want to se only query of session user you need to replace
> * to columns from view pg_stat_activity , and to column query
> you need to use CASE , when username == SESSION_USER : cur_query,
> else null;

This won't work for what he's asking for.  The problem is that
pg_stat_activity is actually a view that uses an internal function to
retrieve the command list.  The problem is that the internal function
itself is what contains the logic that decides whether or not to show the
command based on whether or not the user issuing the query is superuser.

I created a patch to address this (so that users can see their own
commands), and 7.4 incorporates the patch.  You can probably retrieve
it by searching the pgsql-patches archives, but I can also email the
patch to whomever's interested.


-- 
Kevin Brown					      kevin@sysexperts.com

Mon, 7 Jul 2003 15:48:06 -0700
Kevin Brown <kevin@sysexperts.com>
Re: pg_stat_activity
In 7.4 CVS I believe joe users can see their own queries in
pg_stat_activity.

Chris

----- Original Message ----- 
From: <ohp@pyrenet.fr>
To: "ivan" <iv@psycho.pl>
Cc: "pgsql-hackers list" <pgsql-hackers@postgresql.org>
Sent: Tuesday, July 08, 2003 4:34 AM
Subject: Re: [HACKERS] pg_stat_activity


> Hi ivan,
>
> Sounds like a plan, I'll try it, many thanks!!
>
> Still, shouldn't there be a view like this : select * from db_activity
> wher it returns stat_activity only for current database??
>
> Regaerds
> On Mon, 7 Jul 2003, ivan wrote:
>
> > Date: Mon, 7 Jul 2003 21:21:32 +0200 (CEST)
> > From: ivan <iv@psycho.pl>
> > To: ohp@pyrenet.fr
> > Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> > Subject: Re: [HACKERS] pg_stat_activity
> >
> >
> > You can create function with security definer as super user
> > sth like this
> > create or replace function func () returns setof pg_stat_activity as '
> > select * from pg_stat_activity ; ' language SQL SECURITY DEFINER;
> >
> > and if you want to se only query of session user you need to replace
> > * to columns from view pg_stat_activity , and to column query
> > you need to use CASE , when username == SESSION_USER : cur_query,
> > else null;
> >
> > On Mon, 7 Jul 2003 ohp@pyrenet.fr wrote:
> >
> > > Hi every one,
> > >
> > > Is there a way for joe user (not superuser) to see current query on
HIS
> > > database on pg_stat_activity.
> > >
> > > ISTM that only superuser can see current query for all.
> > >
> > > As fr security, it doesn't seem right that select * from
pg_stat_activity
> > > shows all databases.
> > >
> > > What do you think?
> > >
> > > --
> > > Olivier PRENANT                Tel: +33-5-61-50-97-00 (Work)
> > > 6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
> > > 31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
> > > FRANCE                          Email: ohp@pyrenet.fr
> >
> --------------------------------------------------------------------------
----
> > > Make your life a dream, make your dream a reality. (St Exupery)
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> > >
> >
>
> -- 
> Olivier PRENANT                Tel: +33-5-61-50-97-00 (Work)
> 6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
> 31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
> FRANCE                          Email: ohp@pyrenet.fr
> --------------------------------------------------------------------------
----
> Make your life a dream, make your dream a reality. (St Exupery)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>


Tue, 8 Jul 2003 09:55:38 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: pg_stat_activity