Date: Mon, 5 Dec 2022 16:04:14 -0800
From: Nathan Bossart <nathandbossart@gmail.com>
To: Pavel Luzanov <p.luzanov@postgrespro.ru>
Cc: Andrew Dunstan <andrew@dunslane.net>,
	Corey Huinker <corey.huinker@gmail.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, Stephen Frost <sfrost@snowman.net>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	Robert Haas <robertmhaas@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: predefined role(s) for VACUUM and ANALYZE
Message-ID: <20221206000414.GA2813564@nathanxps13>
References: <20221115050813.GA1953731@nathanxps13>
 <287b17b8-92f3-2bc2-6bcf-31dc1305b65a@dunslane.net>
 <20221117043952.GA116054@nathanxps13>
 <20221118170504.GA401589@nathanxps13>
 <20221119185004.GA539143@nathanxps13>
 <20221120165713.GA597801@nathanxps13>
 <0b00a6ff-1475-c0ba-15ec-5b5e381c6359@dunslane.net>
 <20221123235444.GA479104@nathanxps13>
 <8609e4f7-5ffd-9fef-a5e0-78edb8818f3c@dunslane.net>
 <a2382acd-e465-85b2-9d8e-f9ed1a5a66e9@postgrespro.ru>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="OXfL5xGRrasGEqWY"
Content-Disposition: inline
In-Reply-To: <a2382acd-e465-85b2-9d8e-f9ed1a5a66e9@postgrespro.ru>
Archived-At: 
 <https://www.postgresql.org/message-id/20221206000414.GA2813564%40nathanxps13>
Precedence: bulk


--OXfL5xGRrasGEqWY
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Dec 05, 2022 at 11:21:08PM +0300, Pavel Luzanov wrote:
> But perhaps this behavior should be reviewed or at least documented?

I wonder why \dpS wasn't added.  I wrote up a patch to add it and the
corresponding documentation that other meta-commands already have.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

--OXfL5xGRrasGEqWY
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="add_s_modifier_to_dp.patch"

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d3dd638b14..406936dd1c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1825,14 +1825,16 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
 
 
       <varlistentry>
-        <term><literal>\dp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dp[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <listitem>
         <para>
         Lists tables, views and sequences with their
         associated access privileges.
         If <replaceable class="parameter">pattern</replaceable> is
         specified, only tables, views and sequences whose names match the
-        pattern are listed.
+        pattern are listed.  By default only user-created objects are shown;
+        supply a pattern or the <literal>S</literal> modifier to include system
+        objects.
         </para>
 
         <para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index de6a3a71f8..3520655dc0 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -875,7 +875,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listCollations(pattern, show_verbose, show_system);
 				break;
 			case 'p':
-				success = permissionsList(pattern);
+				success = permissionsList(pattern, show_system);
 				break;
 			case 'P':
 				{
@@ -2831,7 +2831,7 @@ exec_command_z(PsqlScanState scan_state, bool active_branch)
 		char	   *pattern = psql_scan_slash_option(scan_state,
 													 OT_NORMAL, NULL, true);
 
-		success = permissionsList(pattern);
+		success = permissionsList(pattern, false);
 		free(pattern);
 	}
 	else
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 2eae519b1d..eb98797d67 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1002,7 +1002,7 @@ listAllDbs(const char *pattern, bool verbose)
  * \z (now also \dp -- perhaps more mnemonic)
  */
 bool
-permissionsList(const char *pattern)
+permissionsList(const char *pattern, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -1121,15 +1121,12 @@ permissionsList(const char *pattern)
 						 CppAsString2(RELKIND_FOREIGN_TABLE) ","
 						 CppAsString2(RELKIND_PARTITIONED_TABLE) ")\n");
 
-	/*
-	 * Unless a schema pattern is specified, we suppress system and temp
-	 * tables, since they normally aren't very interesting from a permissions
-	 * point of view.  You can see 'em by explicit request though, eg with \z
-	 * pg_catalog.*
-	 */
+	if (!showSystem && !pattern)
+		appendPQExpBufferStr(&buf, "AND n.nspname !~ '^pg_'\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
-								"n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)",
+								"pg_catalog.pg_table_is_visible(c.oid)",
 								NULL, 3))
 		goto error_return;
 
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index bd051e09cb..58d0cf032b 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -38,7 +38,7 @@ extern bool describeRoles(const char *pattern, bool verbose, bool showSystem);
 extern bool listDbRoleSettings(const char *pattern, const char *pattern2);
 
 /* \z (or \dp) */
-extern bool permissionsList(const char *pattern);
+extern bool permissionsList(const char *pattern, bool showSystem);
 
 /* \ddp */
 extern bool listDefaultACLs(const char *pattern);

--OXfL5xGRrasGEqWY--