Subject: Re: restrict pg_stat_ssl to superuser?
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Michael Paquier <michael@paquier.xyz>
References: <63117976-d02c-c8e2-3aef-caa31a5ab8d3@2ndquadrant.com>
 <20190212064007.GN1475@paquier.xyz>
 <71319f2d-a073-0499-4562-6003e026307e@2ndquadrant.com>
 <20190218035813.GH15532@paquier.xyz>
 <1d1bf3a7-955e-ce6b-de99-b900136ca789@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Openpgp: preference=signencrypt
Autocrypt: addr=peter.eisentraut@2ndquadrant.com; prefer-encrypt=mutual;
 keydata=
 xsFNBFUUt6kBEADZd9NNTBJbHmAXXJgPGOdd6Q73l80oifIVgn2tRxP9dYkCfTlo/yLqbKTl
 JNqTcyM6XrJDnB2sMEUGYCewYZXl+9C4bBJWyNqcEI1ae9u71vEq/fknFLArJ+2wN+f7QuRx
 Q8d94ZJAiJQ5g3fNk2HVeedCQCKP5dCzU+ZLt2dbsx1PDZLjTO9gf+JswWuGFb5wY5PZVM5V
 hq66Xk7ZJzHuARacza2RT7J2fkMmQBY+kr7L98Perb0GhafnlhXB0PvUB+N3xpmggMfwuhTu
 BngS/G/r3GvemxJuFxouw8xsMqrsfdksJXA2gLXYtwRCssdPV3PO0rlD9twqDo8URfIJ5fb6
 1g/MV8aTOKjg3Q8MpJPtkwJqN4o3PvbwYdedeFnOVkkYAJdu/aX1v1BeCBDu7Hy3sF52scD3
 pQbyQSl9vUDpTLarASAxNbAV0NRJE8dp4vfW6AH1ccryGiFqw/czPK/iN0+MT8wsjAW6WW4w
 hvjm5UPA1KTwi3ZT8eT5uycuH7QpuNk7LnGGXQ6KaRALFywzkr0A0V0AvFNa+l4wstMaXaB6
 dmMXyF0/hfryqyOeu1gAdq4q0/PNC2dPgb/6uoh4eMjN6A5GXcprilhz5DulmsilY21hfSoU
 S64ODed96pKhprsOxjZiDRsN1QM5MwB+uwVWUNfsX7fIxE0BrwARAQABzSdQZXRlciBFaXNl
 bnRyYXV0IDxwZXRlckBlaXNlbnRyYXV0Lm9yZz7CwXoEEwEIACQCGwMFCwkIBwMFFQoJCAsF
 FgIDAQACHgECF4AFAlUUuKQCGQEACgkQoZ8SQ+xBJp8llw//RbGoXnF72FZTlOFsnNyxnWbF
 vViPCfdAlZl1GGK9OYCvV1dp/956HuhB0wpaGQHiBmQfOPJf/jLOPjXFYm2iEChEycrBChuE
 j4TSYemMp5BSPILYk9Uwd16kVD/+U6fQfK6H5pOhfBvbM6asMnmpY2wQuvktxHvaGlytFw84
 EzFWNZtAZfxkw28w3AhIQkKe9hJanfyDL750RcbyEr8AAHty5IErrvS7tM5vQwIeh5BF26ND
 CyKPRdZgOgMpcwFfROKBDpAt052CgWIpNdhBdfIKlkCs4/ILxmC3rT/Mb/qZiHcoMMwEu6k+
 T7vRAFZZAk62Yrr/U8/vUGKlzRZQoo86fyojCfI0ibCIc0z8rwCVY9P40G9wD9BTCZS/z8Vh
 jjpBlNnIKw2hM6hbxavKndCJjFFJD08rXrTyrLF/of18xD37vNdYooP9VATB5QjOWFRrscSZ
 4a89e+to3m7YVnd4xdXwGp47dYusoax84C8tg1gZrH38ol9dPmnKX6GZrXeS7tH9k8VeUpJ8
 HQbRThQRWAT936QpYbLConBmOtmbLmjQOGtiXPo1oK57X/7U/WBja5vWmm2q+Yxtt+3uHGVg
 DdDU4CJpXo4lX0v2n3QV5EBSW9hjdF3bNfl6GFBb1pPEhqNcMDUc7zkx/2uENdAdk/BnqMgH
 umip7b0pVfvOwU0EVRS3qQEQAJf6p0P7D+hMYUgCPkvgrcz6OJYsAYdUMUmokfqmkKwK0oJM
 ew8TNGM53ocKAgQGma5f10rwbZNPlpVPNRX5hwUmwVlcwSut0oE+JfUBH7mpysWvpF05RKIy
 q9RCoJZrAbXS7Tig56A1QD8IcM00rERZMcCOlI1+q9m4u9AOI+0LyGGSH+VvoyDHD/Jew4R2
 TmPEF8Pu7lYiNYK88Q6u0kBkc+Tz3BNlyc6fi3HxnFfp0b75HLXPSTu2SwA/0Q31wHZXwrEU
 BR9KgSn/qbgy8ydPc97i82fhiFE3cbGexfPrQSMYmDGi+dcQ0s+AMQwC3XyMC0JkHpYUuFkR
 QdlIvaIhgFUUN2Vr4xF2ADaNTNFc4DYaiPHFAgHZw+3pZWqS4uIiWgJynrEaHw4F3QWU8/JB
 iyEWDhNq4WBrRA0u914TCCAoFOOKMnRdl8eJrRQKM/5/lXbHFjjlI/LDKhLRFsuQsnJJ3331
 RjCzpHPZG5/p5KR1RHiAXa0p/DW5lXyq21hS3kjDlleoHqOxCN/cy0Jgz7qLDq1vZoCl67Sy
 RAH60c2Xu4may9SVltmot0OzWVHdrfS+85FQGp9580DaeLio7YMFKy2bfEvwQJ/c9xD2QPB+
 oSb3V8ueSznXBG5+dyfOsZFRPxceA0wA2rSWZSjxLdKxvyGTj9vsCjaDu13rABEBAAHCwV8E
 GAEIAAkFAlUUt6kCGwwACgkQoZ8SQ+xBJp+HJhAAxmSTsC5y7p9Zu+nnLpucv/h88oEx985e
 qWeXzQiwM3pfRZS+PdTH9TyzHK0KOgKoAiubk0vt+BrT91D9d0G5kVQaTctAErGBoMkTaZJT
 XVEcNphiPzysdSqvprjojMp1y7rG8AP+Xd6m0rE1LRnkRRm8HyI8vdXsuLQPITGWXDLMjaNq
 AOV3BUnzkgtjMuIYw7SCr+12ZOb5dEKvaG4kjC4kaJ/TLbuBhVANcXkC90yp0AOZLs/cB1Dj
 OAXno/RUUruJ3r27a+pFv768UAi9QWcdw58yPxreMNMLqAgsS9oTHwHfGNWcB2dJ4UW+GMLJ
 hTf9TjQ2f+N/sIx5XMQ9pYw6CAJMK29qPx6/ZayrPxf22EXf0D3zTwIEZgwfSkynFVwV25L7
 awQq60fifdMYheSxq7GbhpsixmcD+e161cCmLIt75YDt5bQ0B1yf7EmxG8n7pGZAOM6LoNsU
 fkSldba5XVwh8LuN5mK0nQBELGW6Os4TDq4REggLpfcvFC215zpuo0iCGnmE/Bns4nowqUSP
 u4tkWL+nxW4qgv8wo8HJ52jd79EJ8iqfIBoidBfy9AIUiEUApGQzWPu6yWcI8fY0rN5D+tc+
 Yw1Zt6Mm+huYJpgCRsTcfzEcahnBn521CWcWvhW1ff3KFileFMtNmnBBhMncmRKHphtTKEpN
 3jI=
Organization: 2ndQuadrant
Message-ID: <3018acd9-e5d8-1e85-5ed7-47276cd77569@2ndquadrant.com>
Date: Wed, 20 Feb 2019 11:51:08 +0100
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <1d1bf3a7-955e-ce6b-de99-b900136ca789@2ndquadrant.com>
Content-Type: multipart/mixed;
 boundary="------------C4872B86007236D68CC45575"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------C4872B86007236D68CC45575
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit

On 2019-02-19 16:57, Peter Eisentraut wrote:
> On 2019-02-18 04:58, Michael Paquier wrote:
>> On Fri, Feb 15, 2019 at 02:04:59PM +0100, Peter Eisentraut wrote:
>>> We could remove default privileges from pg_stat_get_activity().  Would
>>> that be a problem?
>>
>> I don't think so, still I am wondering about the impact that this
>> could have for monitoring tools calling it directly as we document
>> it.. 
> 
> Actually, this approach isn't going to work anyway, because function
> permissions in a view are checked as the current user, not the view owner.

So here is a patch doing it the "normal" way of nulling out all the rows
the user shouldn't see.

I haven't found any documentation of these access restrictions in the
context of pg_stat_activity.  Is there something that I'm not seeing or
something that should be added?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--------------C4872B86007236D68CC45575
Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0";
 name="v1-0001-Hide-other-user-s-pg_stat_ssl-rows.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="v1-0001-Hide-other-user-s-pg_stat_ssl-rows.patch"

From e164cc0d7fbd8d1e6de23219eed3dd038f99e557 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 20 Feb 2019 11:38:44 +0100
Subject: [PATCH v1] Hide other user's pg_stat_ssl rows

Change pg_stat_ssl so that an unprivileged user can only see their own
rows; other rows will be all null.  This makes the behavior consistent
with pg_stat_activity, where information about where the connection
came from is also restricted.
---
 src/backend/utils/adt/pgstatfuncs.c | 73 ++++++++++++++++-------------
 1 file changed, 41 insertions(+), 32 deletions(-)

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b6ba856ebe..69f7265779 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -645,38 +645,6 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		else
 			nulls[16] = true;
 
-		if (beentry->st_ssl)
-		{
-			values[18] = BoolGetDatum(true);	/* ssl */
-			values[19] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
-			values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
-			values[21] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
-			values[22] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
-
-			if (beentry->st_sslstatus->ssl_client_dn[0])
-				values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn);
-			else
-				nulls[23] = true;
-
-			if (beentry->st_sslstatus->ssl_client_serial[0])
-				values[24] = DirectFunctionCall3(numeric_in,
-												 CStringGetDatum(beentry->st_sslstatus->ssl_client_serial),
-												 ObjectIdGetDatum(InvalidOid),
-												 Int32GetDatum(-1));
-			else
-				nulls[24] = true;
-
-			if (beentry->st_sslstatus->ssl_issuer_dn[0])
-				values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
-			else
-				nulls[25] = true;
-		}
-		else
-		{
-			values[18] = BoolGetDatum(false);	/* ssl */
-			nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = true;
-		}
-
 		/* Values only available to role member or pg_read_all_stats */
 		if (has_privs_of_role(GetUserId(), beentry->st_userid) ||
 			is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS))
@@ -854,6 +822,39 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			else
 				values[17] =
 					CStringGetTextDatum(pgstat_get_backend_desc(beentry->st_backendType));
+
+			/* SSL information */
+			if (beentry->st_ssl)
+			{
+				values[18] = BoolGetDatum(true);	/* ssl */
+				values[19] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
+				values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
+				values[21] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
+				values[22] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
+
+				if (beentry->st_sslstatus->ssl_client_dn[0])
+					values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn);
+				else
+					nulls[23] = true;
+
+				if (beentry->st_sslstatus->ssl_client_serial[0])
+					values[24] = DirectFunctionCall3(numeric_in,
+													 CStringGetDatum(beentry->st_sslstatus->ssl_client_serial),
+													 ObjectIdGetDatum(InvalidOid),
+													 Int32GetDatum(-1));
+				else
+					nulls[24] = true;
+
+				if (beentry->st_sslstatus->ssl_issuer_dn[0])
+					values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
+				else
+					nulls[25] = true;
+			}
+			else
+			{
+				values[18] = BoolGetDatum(false);	/* ssl */
+				nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = true;
+			}
 		}
 		else
 		{
@@ -870,6 +871,14 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			nulls[13] = true;
 			nulls[14] = true;
 			nulls[17] = true;
+			nulls[18] = true;
+			nulls[19] = true;
+			nulls[20] = true;
+			nulls[21] = true;
+			nulls[22] = true;
+			nulls[23] = true;
+			nulls[24] = true;
+			nulls[25] = true;
 		}
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);

base-commit: 56fadbedbd2f697400b89e7b767cfa4ec67932d6
-- 
2.20.1


--------------C4872B86007236D68CC45575--