From d913928c9c5e905d0062d1e7237b7fb5fbde61ed Mon Sep 17 00:00:00 2001 From: Dean Rasheed Date: Sat, 7 Jan 2023 11:09:26 +0000 Subject: [PATCH] psql: Add support for \dpS and \zS. This allows an optional "S" modifier to be added to \dp and \z, to have them include system objects in the list. Note that this also changes the behaviour of a bare \dp or \z without the "S" modifier to include temp objects in the list, and exclude information_schema objects, making them consistent with other psql meta-commands. Nathan Bossart, reviewed by Maxim Orlov. Discussion: https://postgr.es/m/20221206193606.GB3078082@nathanxps13 --- doc/src/sgml/ref/psql-ref.sgml | 12 ++++++++---- src/bin/psql/command.c | 23 +++++++++++++++-------- src/bin/psql/describe.c | 14 ++++++-------- src/bin/psql/describe.h | 2 +- 4 files changed, 30 insertions(+), 21 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 8a5285da9aa..3f994a35921 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 - \dp [ pattern ] + \dp[S] [ pattern ] Lists tables, views and sequences with their associated access privileges. If pattern 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 S modifier to include + system objects. @@ -3575,14 +3577,16 @@ testdb=> \setenv LESS -imx4F - \z [ pattern ] + \z[S] [ pattern ] Lists tables, views and sequences with their associated access privileges. If a pattern 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 S modifier to include + system objects. diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 00b89d941b8..b5201edf555 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -140,7 +140,8 @@ static backslashResult exec_command_write(PsqlScanState scan_state, bool active_ static backslashResult exec_command_watch(PsqlScanState scan_state, bool active_branch, PQExpBuffer query_buf, PQExpBuffer previous_buf); static backslashResult exec_command_x(PsqlScanState scan_state, bool active_branch); -static backslashResult exec_command_z(PsqlScanState scan_state, bool active_branch); +static backslashResult exec_command_z(PsqlScanState scan_state, bool active_branch, + const char *cmd); static backslashResult exec_command_shell_escape(PsqlScanState scan_state, bool active_branch); static backslashResult exec_command_slash_command_help(PsqlScanState scan_state, bool active_branch); static char *read_connect_arg(PsqlScanState scan_state); @@ -413,8 +414,8 @@ exec_command(const char *cmd, query_buf, previous_buf); else if (strcmp(cmd, "x") == 0) status = exec_command_x(scan_state, active_branch); - else if (strcmp(cmd, "z") == 0) - status = exec_command_z(scan_state, active_branch); + else if (strcmp(cmd, "z") == 0 || strcmp(cmd, "zS") == 0) + status = exec_command_z(scan_state, active_branch, cmd); else if (strcmp(cmd, "!") == 0) status = exec_command_shell_escape(scan_state, active_branch); else if (strcmp(cmd, "?") == 0) @@ -875,7 +876,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': { @@ -2822,16 +2823,22 @@ exec_command_x(PsqlScanState scan_state, bool active_branch) * \z -- list table privileges (equivalent to \dp) */ static backslashResult -exec_command_z(PsqlScanState scan_state, bool active_branch) +exec_command_z(PsqlScanState scan_state, bool active_branch, const char *cmd) { bool success = true; if (active_branch) { - char *pattern = psql_scan_slash_option(scan_state, - OT_NORMAL, NULL, true); + char *pattern; + bool show_system; + + pattern = psql_scan_slash_option(scan_state, + OT_NORMAL, NULL, true); + + show_system = strchr(cmd, 'S') ? true : false; + + success = permissionsList(pattern, show_system); - success = permissionsList(pattern); free(pattern); } else diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 523fab61b90..e280b6f76c2 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,13 @@ 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_catalog'\n" + " AND n.nspname <> 'information_schema'\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 15f62b91d1a..554fe867255 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); -- 2.30.2