Обсуждение: 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.
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: http://www.postgresql.org/message-id/flat/20150508042928.GP30322@tamriel.snowman.net#20150508042928.GP30322@tamriel.snowman.net With the latest patch here: http://www.postgresql.org/message-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.
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
On Mon, Oct 19, 2015 at 3:12 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
You can solve this using a security definer method created by a superuser, see
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).
for a full example.
Lukas Fittl
Skype: lfittl
Phone: +1 415 321 0630
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.
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();
-- 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.
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
Skype: lfittl
Phone: +1 415 321 0630