SuperUser check in pg_stat_statements

On Mon, Oct 19, 2015 at 5:15 PM, rajan <vgmonnet@gmail.com> wrote:

Hello,

When monitoring using pg_stat_satements I see that postgres by default
conceals queries executed by other users from the user who is selecting the
pg_stat_statements view.

I have edited the pg_stat_statements.c by disabling the superuser check
function so that all queries will be visible to all users.

Well, you could see that's by design. What problem are you trying to solve
with that?

Can this be posted as a patch to postgresql?

This is not going to be accepted.

--
Alex

Rajan,

* rajan (vgmonnet@gmail.com) wrote:

When monitoring using pg_stat_satements I see that postgres by default
conceals queries executed by other users from the user who is selecting the
pg_stat_statements view.

I have edited the pg_stat_statements.c by disabling the superuser check
function so that all queries will be visible to all users.

Can this be posted as a patch to postgresql?

We don't want that to be generally viewable but rather something where
an administrator can control who can see it.

The current proposal for that is to have a set of default roles, one of
which will have this ability.

The thread on that topic starts here:

/messages/by-id/20150508042928.GP30322@tamriel.snowman.net

With the latest patch here:

/messages/by-id/20150930111120.GM3685@tamriel.snowman.net

Thanks!

Stephen

Thanks Stephen and Shulgin for your response.

Will go through the patch and will try to solve my problem using that.

My scenario is that i need to have an user who cannot be a super user but a
monitor user, who will be able to see all the queries executed by all users.

--
View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870639.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10/19/15 3:48 PM, rajan wrote:

Thanks Stephen and Shulgin for your response.

Will go through the patch and will try to solve my problem using that.

My scenario is that i need to have an user who cannot be a super user but a
monitor user, who will be able to see all the queries executed by all users.

You can set that up today by defining a view on top of
pg_stat_statements (or maybe it needs a SECDEF SRF... been a while since
I've done it).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Oct 19, 2015 at 3:12 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 10/19/15 3:48 PM, rajan wrote:

Thanks Stephen and Shulgin for your response.

Will go through the patch and will try to solve my problem using that.

My scenario is that i need to have an user who cannot be a super user but
a
monitor user, who will be able to see all the queries executed by all
users.

You can set that up today by defining a view on top of pg_stat_statements
(or maybe it needs a SECDEF SRF... been a while since I've done it).

You can solve this using a security definer method created by a superuser,
see

https://gist.github.com/lfittl/9ee78ac200e4e7ebe33d

for a full example.

--
Lukas Fittl

Skype: lfittl
Phone: +1 415 321 0630

Jim,

I already tried to create a view upon the pg_stat_statements, but no luck.

--
View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870683.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

You can create a Security Definer Funtion which can then be executed by
then non-superuser monitoring role:

(Assuming you have a role monitoring and pg_stat_statements is installed in
schema public)

-- connected as a superuser
CREATE FUNCTION pg_stat_statements()
RETURNS SETOF pg_stat_statements
LANGUAGE SQL
SET search_path='public'
SECURITY DEFINER
AS
$BODY$
SELECT *
FROM pg_stat_statements;
$BODY$;

REVOKE ALL ON FUNCTION pg_stat_statements() FROM public;
GRANT EXECUTE ON FUNCTION pg_stat_statements() TO monitoring;

-- connected as monitoring
SELECT * FROM pg_stat_statements();

Hey Lukas,

Thanks. Able to see the queries from all users. Can you explain the
monitoring.get_stat_statements()?

--
View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870733.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Rajan,

I'll reply off-list since this isn't the right discussion for -hackers.

Best,
Lukas

On Tue, Oct 20, 2015 at 7:02 AM, rajan <vgmonnet@gmail.com> wrote:

Hey Lukas,

Thanks. Able to see the queries from all users. Can you explain the
monitoring.get_stat_statements()?

--
View this message in context:
http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870733.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Lukas Fittl

Skype: lfittl
Phone: +1 415 321 0630