Thread: pg_stat_activity
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)
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)
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 >
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
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 >