Re: [HACKERS] SuperUser check in pg_stat_statements
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 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
Re: [HACKERS] SuperUser check in 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
Re: [HACKERS] SuperUser check in pg_stat_statements
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();
Re: [HACKERS] SuperUser check in pg_stat_statements
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
Re: [HACKERS] SuperUser check in pg_stat_statements
On Mon, Oct 19, 2015 at 3:12 PM, Jim Nasby 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
Re: [HACKERS] SuperUser check in pg_stat_statements
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
Re: [HACKERS] SuperUser check in pg_stat_statements
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
Re: [HACKERS] SuperUser check in pg_stat_statements
Rajan, * rajan (vgmon...@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: http://www.postgresql.org/message-id/flat/20150508042928.gp30...@tamriel.snowman.net#20150508042928.gp30...@tamriel.snowman.net With the latest patch here: http://www.postgresql.org/message-id/2015093020.gm3...@tamriel.snowman.net Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] SuperUser check in pg_stat_statements
On Mon, Oct 19, 2015 at 5:15 PM, rajan 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
[HACKERS] SuperUser check in pg_stat_statements
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. Can this be posted as a patch to postgresql? -- View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589.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