From: torikoshia <torikoshia@oss.nttdata.com>
To: Michael Paquier <michael@paquier.xyz>
To: Kasahara Tatsuhito <kasahara.tatsuhito@gmail.com>
To: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Subject: Re: Creating a function for exposing memory usage of backend process
Date: Mon, 24 Aug 2020 13:01:42 +0900
Message-ID: <c9515ae497b4a077360a31547782acb8@oss.nttdata.com> (raw)
In-Reply-To: <20200822121641.GE2108@paquier.xyz>
References: <261eebda3e1909475162b968e5a4ad2b@oss.nttdata.com>
<66ae5b88-208d-96fa-c20a-fb539b597859@oss.nttdata.com>
<20200819135545.GC19121@paquier.xyz>
<1414992.1597849297@sss.pgh.pa.us>
<20200819225343.c7rxteydi35nhrdp@alap3.anarazel.de>
<1554676.1597886946@sss.pgh.pa.us>
<20200820014301.5pr7qjugnvpovu62@alap3.anarazel.de>
<d97ad430-444d-9ec8-21dd-a1584569309b@oss.nttdata.com>
<CAP0=ZVJ5VZsUYLFkc-E5_JHTc1ZdemX28VgQqUXJLTywC=9hfA@mail.gmail.com>
<a8f3c385e9edeba4833c712270d6e6d9@oss.nttdata.com>
<20200822121641.GE2108@paquier.xyz>
On 2020-08-22 21:18, Michael Paquier wrote:
Thanks for reviewing!
> On Fri, Aug 21, 2020 at 11:27:06PM +0900, torikoshia wrote:
>> OK. Added a regression test on sysviews.sql.
>> (0001-Added-a-regression-test-for-pg_backend_memory_contex.patch)
>>
>> Fujii-san gave us an example, but I added more simple one considering
>> the simplicity of other tests on that.
>
> What you have sent in 0001 looks fine to me. A small test is much
> better than nothing.
>
>> Added a patch for relocating the codes to mcxtfuncs.c.
>> (patches/0001-Rellocated-the-codes-for-pg_backend_memory_contexts-.patch)
>
> The same code is moved around line-by-line.
>
>> Of course, this restriction makes pg_backend_memory_contexts hard to
>> use
>> when the user of the target session is not granted pg_monitor because
>> the
>> scope of this view is session local.
>>
>> In this case, I imagine additional operations something like
>> temporarily
>> granting pg_monitor to that user.
>
> Hmm. I am not completely sure either that pg_monitor is the best fit
> here, because this view provides information about a bunch of internal
> structures. Something that could easily be done though is to revoke
> the access from public, and then users could just set up GRANT
> permissions post-initdb, with pg_monitor as one possible choice. This
> is the safest path by default, and this stuff is of a caliber similar
> to pg_shmem_allocations in terms of internal contents.
I think this is a better way than what I did in
0001-Rellocated-the-codes-for-pg_backend_memory_contexts-.patch.
Attached a patch.
>
> It seems to me that you are missing one "REVOKE ALL on
> pg_backend_memory_contexts FROM PUBLIC" in patch 0003.
>
> By the way, if that was just for me, I would remove used_bytes, which
> is just a computation from the total and free numbers. I'll defer
> that point to Fujii-san.
> --
> Michael
On 2020/08/20 2:59, Kasahara Tatsuhito wrote:
>>> I totally agree that it's not *enough*, but in contrast to you I
>>> think
>>> it's a good step. Subsequently we should add a way to get any
>>> backends
>>> memory usage.
>>> It's not too hard to imagine how to serialize it in a way that can be
>>> easily deserialized by another backend. I am imagining something like
>>> sending a procsignal that triggers (probably at CFR() time) a backend
>>> to
>>> write its own memory usage into pg_memusage/<pid> or something
>>> roughly
>>> like that.
>>
>> Sounds good. Maybe we can also provide the SQL-callable function
>> or view to read pg_memusage/<pid>, to make the analysis easier.
> +1
I'm thinking about starting a new thread to discuss exposing other
backend's memory context.
Regards,
--
Atsushi Torikoshi
NTT DATA CORPORATION
Attachments:0001-Previously-pg_backend_memory_contexts-doesn-t-have-a.patch (text/x-diff, 1.7K) download
From dc4fade9111dc3f91e992c4d5af393dd5ed03270 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Mon, 24 Jul 2020 11:14:32 +0900
Subject: [PATCH] Previously pg_backend_memory_contexts doesn't have any
restriction and anyone could access it. However, this view contains some
internal information of the memory context. This policy could cause security
issues. This patch revokes all on pg_shmem_allocations from public and only
the superusers can access it.
---
doc/src/sgml/catalogs.sgml | 4 ++++
src/backend/catalog/system_views.sql | 3 +++
2 files changed, 7 insertions(+)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 1232b24e74..9fe260ecff 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -9697,6 +9697,10 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</tgroup>
</table>
+ <para>+ By default, the <structname>pg_backend_memory_contexts</structname> view can be+ read only by superusers.+ </para>
</sect1>
<sect1 id="view-pg-config">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sqlindex ba5a23ac25..a2d61302f9 100644--- a/src/backend/catalog/system_views.sql+++ b/src/backend/catalog/system_views.sql@@ -557,6 +557,9 @@ REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;
CREATE VIEW pg_backend_memory_contexts AS
SELECT * FROM pg_get_backend_memory_contexts();
+REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;+REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;+
-- Statistics views
CREATE VIEW pg_stat_all_tables AS
--
2.18.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: torikoshia@oss.nttdata.com, michael@paquier.xyz, kasahara.tatsuhito@gmail.com, masao.fujii@oss.nttdata.com, andres@anarazel.de, tgl@sss.pgh.pa.us, robertmhaas@gmail.com
Subject: Re: Creating a function for exposing memory usage of backend process
In-Reply-To: <c9515ae497b4a077360a31547782acb8@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox