On 28.01.2024 22:51, Pavel Luzanov wrote:
I'll think about it and try to implement in the next patch version
within a few days.
Sorry for delay.
Please look at v4.
I tried to implement all of David's suggestions.
The only addition - "Login" column. I still thinks this is important
information to be highlighted.
Especially considering that the Attributes column small enough with a newline
separator.
The changes are split into two patches.
0001 - pg_roles view. I plan to organize a new thread for discussion.
0002 - \du command. It depends on 0001 for "Password?" and "Valid until"
columns.
Output for superuser:
postgres@postgres(17.0)=# \du+
List of
roles
Role name | Login | Attributes | Password? | Valid until
| Connection limit | Description
------------------+-------+-------------+-----------+---------------------------------+------------------+--------------------------------------------------
postgres | yes | Superuser +| no |
| |
| | Create DB +| |
| |
| | Create role+| |
| |
| | Inherit +| |
| |
| | Replication+| |
| |
| | Bypass RLS | |
| |
regress_du_admin | yes | Create role+| yes | infinity
| | User createrole attribute
| | Inherit | |
| |
regress_du_role0 | yes | Create DB +| yes | 2024-12-31 00:00:00+03
| |
| | Inherit +| |
| |
| | Replication+| |
| |
| | Bypass RLS | |
| |
regress_du_role1 | no | Inherit | no | 2024-12-31
00:00:00+03(invalid) | 50 | Group role without password but with
valid until
regress_du_role2 | yes | Inherit | yes |
| Not allowed | No connections allowed
regress_du_role3 | yes | | yes |
| 10 | User without attributes
regress_du_su | yes | Superuser +| yes |
| 3(ignored) | Superuser but with connection limit
| | Create DB +| |
| |
| | Create role+| |
| |
| | Inherit +| |
| |
| | Replication+| |
| |
| | Bypass RLS | |
| |
(7 rows)
Output for regress_du_admin (can see password for regress_du_role[0,1,2]
but not for regress_du_role3):
regress_du_admin@postgres(17.0)=> \du regress_du_role*
List of roles
Role name | Login | Attributes | Password? | Valid until
| Connection limit
------------------+-------+-------------+-----------+---------------------------------+------------------
regress_du_role0 | yes | Create DB +| yes | 2024-12-31 00:00:00+03
|
| | Inherit +| |
|
| | Replication+| |
|
| | Bypass RLS | |
|
regress_du_role1 | no | Inherit | no | 2024-12-31
00:00:00+03(invalid) | 50
regress_du_role2 | yes | Inherit | yes |
| Not allowed
regress_du_role3 | yes | | |
| 10
(4 rows)
Output for regress_du_role3 (no password information):
regress_du_role3@postgres(17.0)=> \du regress_du_role*
List of roles
Role name | Login | Attributes | Password? | Valid until |
Connection limit
------------------+-------+-------------+-----------+------------------------+------------------
regress_du_role0 | yes | Create DB +| | 2024-12-31 00:00:00+03 |
| | Inherit +| | |
| | Replication+| | |
| | Bypass RLS | | |
regress_du_role1 | no | Inherit | | 2024-12-31 00:00:00+03 |
50
regress_du_role2 | yes | Inherit | | |
Not allowed
regress_du_role3 | yes | | | |
10
(4 rows)
Any comments. What did I miss?
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
From 78a1b38386634becc6c82749c1e7e19c4f1cc94f Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Mon, 12 Feb 2024 23:46:15 +0300
Subject: [PATCH v4 1/2] Show password presence in pg_roles
Keeping with the changes made in v16 it does seem worthwhile modifying
pg_roles to be sensitive to the role querying the view having both
createrole and admin membership on the role being displayed. With now
three possible outcomes: NULL if no password is in use, ********* if a
password is in use and the user has the ability to alter role, or
<insufficient privileges>.
---
src/backend/catalog/system_views.sql | 45 ++++++++++++++++++----------
src/test/regress/expected/rules.out | 37 ++++++++++++++---------
2 files changed, 52 insertions(+), 30 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 6791bff9dd..2de6802cf7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -16,21 +16,34 @@
CREATE VIEW pg_roles AS
SELECT
- rolname,
- rolsuper,
- rolinherit,
- rolcreaterole,
- rolcreatedb,
- rolcanlogin,
- rolreplication,
- rolconnlimit,
- '********'::text as rolpassword,
- rolvaliduntil,
- rolbypassrls,
- setconfig as rolconfig,
- pg_authid.oid
- FROM pg_authid LEFT JOIN pg_db_role_setting s
- ON (pg_authid.oid = setrole AND setdatabase = 0);
+ r.rolname,
+ r.rolsuper,
+ r.rolinherit,
+ r.rolcreaterole,
+ r.rolcreatedb,
+ r.rolcanlogin,
+ r.rolreplication,
+ r.rolconnlimit,
+ CASE WHEN curr_user.rolsuper OR
+ (curr_user.rolcreaterole AND m.admin_option)
+ THEN
+ CASE WHEN r.rolpassword IS NULL
+ THEN NULL::pg_catalog.text
+ ELSE '********'::pg_catalog.text
+ END
+ ELSE '<insufficient privileges>'::pg_catalog.text
+ END rolpassword,
+ r.rolvaliduntil,
+ r.rolbypassrls,
+ s.setconfig AS rolconfig,
+ r.oid
+ FROM pg_catalog.pg_authid r
+ JOIN pg_catalog.pg_authid curr_user
+ ON (curr_user.rolname = CURRENT_USER)
+ LEFT JOIN pg_catalog.pg_auth_members m
+ ON (curr_user.oid = m.member AND r.oid = m.roleid AND m.admin_option)
+ LEFT JOIN pg_catalog.pg_db_role_setting s
+ ON (r.oid = s.setrole AND s.setdatabase = 0);
CREATE VIEW pg_shadow AS
SELECT
@@ -65,7 +78,7 @@ CREATE VIEW pg_user AS
usesuper,
userepl,
usebypassrls,
- '********'::text as passwd,
+ '********'::text AS passwd,
valuntil,
useconfig
FROM pg_shadow;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index abc944e8b8..a704015de3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1477,21 +1477,30 @@ pg_replication_slots| SELECT l.slot_name,
l.failover
FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, temporary, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size, two_phase, conflict_reason, failover)
LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
-pg_roles| SELECT pg_authid.rolname,
- pg_authid.rolsuper,
- pg_authid.rolinherit,
- pg_authid.rolcreaterole,
- pg_authid.rolcreatedb,
- pg_authid.rolcanlogin,
- pg_authid.rolreplication,
- pg_authid.rolconnlimit,
- '********'::text AS rolpassword,
- pg_authid.rolvaliduntil,
- pg_authid.rolbypassrls,
+pg_roles| SELECT r.rolname,
+ r.rolsuper,
+ r.rolinherit,
+ r.rolcreaterole,
+ r.rolcreatedb,
+ r.rolcanlogin,
+ r.rolreplication,
+ r.rolconnlimit,
+ CASE
+ WHEN (curr_user.rolsuper OR (curr_user.rolcreaterole AND m.admin_option)) THEN
+ CASE
+ WHEN (r.rolpassword IS NULL) THEN NULL::text
+ ELSE '********'::text
+ END
+ ELSE '<insufficient privileges>'::text
+ END AS rolpassword,
+ r.rolvaliduntil,
+ r.rolbypassrls,
s.setconfig AS rolconfig,
- pg_authid.oid
- FROM (pg_authid
- LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
+ r.oid
+ FROM (((pg_authid r
+ JOIN pg_authid curr_user ON ((curr_user.rolname = CURRENT_USER)))
+ LEFT JOIN pg_auth_members m ON (((curr_user.oid = m.member) AND (r.oid = m.roleid) AND m.admin_option)))
+ LEFT JOIN pg_db_role_setting s ON (((r.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules| SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
--
2.34.1
From 9bc12ecb2e1bf792bd536d45bd7f9cff689f931d Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Mon, 12 Feb 2024 23:49:05 +0300
Subject: [PATCH v4 2/2] psql: Rethinking of \du command
The Attributes column includes only the enabled logical attributes.
The attribute names correspond to the keywords of the CREATE ROLE
command. The attributes are listed in the same order as in
the documentation. "Login", "Connection limit" and "Valid until" attributes
are placed in separate columns. The "Password?" column has been added.
---
src/bin/psql/describe.c | 164 ++++++++++-------------------
src/test/regress/expected/psql.out | 106 +++++++++++++++----
src/test/regress/sql/psql.sql | 37 +++++--
3 files changed, 170 insertions(+), 137 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b6a4eb1d56..ed84630f39 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -36,7 +36,6 @@ static bool describeOneTableDetails(const char *schemaname,
bool verbose);
static void add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline);
-static void add_role_attribute(PQExpBuffer buf, const char *const str);
static bool listTSParsersVerbose(const char *pattern);
static bool describeOneTSParser(const char *oid, const char *nspname,
const char *prsname);
@@ -3662,34 +3661,65 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
- printTableContent cont;
- printTableOpt myopt = pset.popt.topt;
- int ncols = 2;
- int nrows = 0;
- int i;
- int conns;
- const char align = 'l';
- char **attr;
-
- myopt.default_footer = false;
+ printQueryOpt myopt = pset.popt;
initPQExpBuffer(&buf);
-
printfPQExpBuffer(&buf,
- "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
- " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
- " r.rolconnlimit, r.rolvaliduntil");
-
- if (verbose)
- {
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
- }
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ "SELECT r.rolname AS \"%s\",\n"
+ " CASE WHEN r.rolcanlogin THEN '%s' ELSE '%s' END \"%s\",\n"
+ " pg_catalog.concat_ws(E'\\n',\n"
+ " CASE WHEN r.rolsuper THEN '%s' END,\n"
+ " CASE WHEN r.rolcreatedb THEN '%s' END,\n"
+ " CASE WHEN r.rolcreaterole THEN '%s' END,\n"
+ " CASE WHEN r.rolinherit THEN '%s' END,\n"
+ " CASE WHEN r.rolreplication THEN '%s' END",
+ gettext_noop("Role name"),
+ gettext_noop("yes"), gettext_noop("no"),
+ gettext_noop("Login"),
+ gettext_noop("Superuser"),
+ gettext_noop("Create DB"),
+ gettext_noop("Create role"),
+ gettext_noop("Inherit"),
+ gettext_noop("Replication"));
if (pset.sversion >= 90500)
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolbypassrls THEN '%s' END",
+ gettext_noop("Bypass RLS"));
+
+ appendPQExpBuffer(&buf, "\n ) AS \"%s\"", gettext_noop("Attributes"));
+
+ if (pset.sversion >= 170000)
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolpassword IS NULL THEN '%s'\n"
+ " WHEN r.rolpassword = '********' THEN '%s'\n"
+ " END AS \"%s\"",
+ gettext_noop("no"), gettext_noop("yes"),
+ gettext_noop("Password?"));
+
+ appendPQExpBuffer(&buf,
+ ",\n CASE WHEN r.rolvaliduntil IS NOT NULL and r.rolpassword IS NULL\n"
+ " THEN r.rolvaliduntil::pg_catalog.text || '%s'\n"
+ " ELSE r.rolvaliduntil::pg_catalog.text\n"
+ " END \"%s\",\n"
+ " CASE\n"
+ " WHEN r.rolconnlimit >= 0 AND r.rolsuper\n"
+ " THEN r.rolconnlimit::pg_catalog.text || '%s'\n"
+ " WHEN r.rolconnlimit = 0 THEN '%s'\n"
+ " WHEN r.rolconnlimit = -1 THEN ''::pg_catalog.text\n"
+ " ELSE r.rolconnlimit::pg_catalog.text\n"
+ " END \"%s\"",
+ gettext_noop("(invalid)"),
+ gettext_noop("Valid until"),
+ gettext_noop("(ignored)"),
+ gettext_noop("Not allowed"),
+ gettext_noop("Connection limit"));
+
+ if (verbose)
{
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.shobj_description(r.oid, 'pg_authid') AS \"%s\"",
+ gettext_noop("Description"));
}
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3708,99 +3738,19 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
if (!res)
return false;
- nrows = PQntuples(res);
- attr = pg_malloc0((nrows + 1) * sizeof(*attr));
-
- printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
-
- printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
- printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
-
- if (verbose)
- printTableAddHeader(&cont, gettext_noop("Description"), true, align);
-
- for (i = 0; i < nrows; i++)
- {
- printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
-
- resetPQExpBuffer(&buf);
- if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
- add_role_attribute(&buf, _("Superuser"));
-
- if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
- add_role_attribute(&buf, _("No inheritance"));
-
- if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
- add_role_attribute(&buf, _("Create role"));
-
- if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
- add_role_attribute(&buf, _("Create DB"));
-
- if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
- add_role_attribute(&buf, _("Cannot login"));
-
- if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), "t") == 0)
- add_role_attribute(&buf, _("Replication"));
-
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
-
- conns = atoi(PQgetvalue(res, i, 6));
- if (conns >= 0)
- {
- if (buf.len > 0)
- appendPQExpBufferChar(&buf, '\n');
-
- if (conns == 0)
- appendPQExpBufferStr(&buf, _("No connections"));
- else
- appendPQExpBuffer(&buf, ngettext("%d connection",
- "%d connections",
- conns),
- conns);
- }
-
- if (strcmp(PQgetvalue(res, i, 7), "") != 0)
- {
- if (buf.len > 0)
- appendPQExpBufferChar(&buf, '\n');
- appendPQExpBufferStr(&buf, _("Password valid until "));
- appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
- }
-
- attr[i] = pg_strdup(buf.data);
-
- printTableAddCell(&cont, attr[i], false, false);
-
- if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
- }
- termPQExpBuffer(&buf);
-
- printTable(&cont, pset.queryFout, false, pset.logfile);
- printTableCleanup(&cont);
+ myopt.title = _("List of roles");
+ myopt.translate_header = true;
- for (i = 0; i < nrows; i++)
- free(attr[i]);
- free(attr);
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
PQclear(res);
return true;
}
-static void
-add_role_attribute(PQExpBuffer buf, const char *const str)
-{
- if (buf->len > 0)
- appendPQExpBufferStr(buf, ", ");
-
- appendPQExpBufferStr(buf, str);
-}
-
/*
* \drds
*/
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index ad02772562..b2f123903f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6198,9 +6198,10 @@ List of text search templates
(0 rows)
\dg "no.such.role"
- List of roles
- Role name | Attributes
------------+------------
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit
+-----------+-------+------------+-----------+-------------+------------------
+(0 rows)
\dL "no.such.language"
List of languages
@@ -6630,16 +6631,23 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
-- check \drg and \du
-CREATE ROLE regress_du_role0;
-CREATE ROLE regress_du_role1;
-CREATE ROLE regress_du_role2;
-CREATE ROLE regress_du_admin;
+CREATE ROLE regress_du_su LOGIN SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS PASSWORD '123' CONNECTION LIMIT 3;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE PASSWORD '123' VALID UNTIL 'infinity';
+CREATE ROLE regress_du_role0 LOGIN REPLICATION BYPASSRLS CREATEDB password '123' VALID UNTIL '2024-12-31';
+CREATE ROLE regress_du_role1 VALID UNTIL '2024-12-31' CONNECTION LIMIT 50;
+CREATE ROLE regress_du_role2 LOGIN PASSWORD '123' CONNECTION LIMIT 0;
+CREATE ROLE regress_du_role3 LOGIN NOINHERIT PASSWORD '123' CONNECTION LIMIT 10;
+COMMENT ON ROLE regress_du_su IS 'Superuser but with connection limit';
+COMMENT ON ROLE regress_du_admin IS 'User createrole attribute';
+COMMENT ON ROLE regress_du_role1 IS 'Group role without password but with valid until';
+COMMENT ON ROLE regress_du_role2 IS 'No connections allowed';
+COMMENT ON ROLE regress_du_role3 IS 'User without attributes';
GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
-GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
@@ -6657,18 +6665,76 @@ GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET
regress_du_role2 | regress_du_role1 | ADMIN, SET | regress_du_admin
(7 rows)
-\du regress_du_role*
- List of roles
- Role name | Attributes
-------------------+--------------
- regress_du_role0 | Cannot login
- regress_du_role1 | Cannot login
- regress_du_role2 | Cannot login
-
-DROP ROLE regress_du_role0;
-DROP ROLE regress_du_role1;
-DROP ROLE regress_du_role2;
-DROP ROLE regress_du_admin;
+-- run as superuser
+\du+ regress_du_*
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit | Description
+------------------+-------+-------------+-----------+---------------------------------------+------------------+--------------------------------------------------
+ regress_du_admin | yes | Create role+| yes | infinity | | User createrole attribute
+ | | Inherit | | | |
+ regress_du_role0 | yes | Create DB +| yes | Tue Dec 31 00:00:00 2024 PST | |
+ | | Inherit +| | | |
+ | | Replication+| | | |
+ | | Bypass RLS | | | |
+ regress_du_role1 | no | Inherit | no | Tue Dec 31 00:00:00 2024 PST(invalid) | 50 | Group role without password but with valid until
+ regress_du_role2 | yes | Inherit | yes | | Not allowed | No connections allowed
+ regress_du_role3 | yes | | yes | | 10 | User without attributes
+ regress_du_su | yes | Superuser +| yes | | 3(ignored) | Superuser but with connection limit
+ | | Create DB +| | | |
+ | | Create role+| | | |
+ | | Inherit +| | | |
+ | | Replication+| | | |
+ | | Bypass RLS | | | |
+(6 rows)
+
+-- run as user with createrole attribute
+SET ROLE regress_du_admin;
+\du regress_du_*
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit
+------------------+-------+-------------+-----------+---------------------------------------+------------------
+ regress_du_admin | yes | Create role+| | infinity |
+ | | Inherit | | |
+ regress_du_role0 | yes | Create DB +| yes | Tue Dec 31 00:00:00 2024 PST |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+ regress_du_role1 | no | Inherit | no | Tue Dec 31 00:00:00 2024 PST(invalid) | 50
+ regress_du_role2 | yes | Inherit | yes | | Not allowed
+ regress_du_role3 | yes | | | | 10
+ regress_du_su | yes | Superuser +| | | 3(ignored)
+ | | Create DB +| | |
+ | | Create role+| | |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+(6 rows)
+
+-- run as unprivileged user
+SET ROLE regress_du_role0;
+\du regress_du_*
+ List of roles
+ Role name | Login | Attributes | Password? | Valid until | Connection limit
+------------------+-------+-------------+-----------+------------------------------+------------------
+ regress_du_admin | yes | Create role+| | infinity |
+ | | Inherit | | |
+ regress_du_role0 | yes | Create DB +| | Tue Dec 31 00:00:00 2024 PST |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+ regress_du_role1 | no | Inherit | | Tue Dec 31 00:00:00 2024 PST | 50
+ regress_du_role2 | yes | Inherit | | | Not allowed
+ regress_du_role3 | yes | | | | 10
+ regress_du_su | yes | Superuser +| | | 3(ignored)
+ | | Create DB +| | |
+ | | Create role+| | |
+ | | Inherit +| | |
+ | | Replication+| | |
+ | | Bypass RLS | | |
+(6 rows)
+
+RESET ROLE;
+DROP ROLE regress_du_role0, regress_du_role1, regress_du_role2, regress_du_role3, regress_du_admin;
-- Test display of empty privileges.
BEGIN;
-- Create an owner for tested objects because output contains owner name.
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 129f853353..0e9442e8da 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1831,10 +1831,18 @@ DROP FUNCTION psql_error;
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
-- check \drg and \du
-CREATE ROLE regress_du_role0;
-CREATE ROLE regress_du_role1;
-CREATE ROLE regress_du_role2;
-CREATE ROLE regress_du_admin;
+CREATE ROLE regress_du_su LOGIN SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS PASSWORD '123' CONNECTION LIMIT 3;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE PASSWORD '123' VALID UNTIL 'infinity';
+CREATE ROLE regress_du_role0 LOGIN REPLICATION BYPASSRLS CREATEDB password '123' VALID UNTIL '2024-12-31';
+CREATE ROLE regress_du_role1 VALID UNTIL '2024-12-31' CONNECTION LIMIT 50;
+CREATE ROLE regress_du_role2 LOGIN PASSWORD '123' CONNECTION LIMIT 0;
+CREATE ROLE regress_du_role3 LOGIN NOINHERIT PASSWORD '123' CONNECTION LIMIT 10;
+
+COMMENT ON ROLE regress_du_su IS 'Superuser but with connection limit';
+COMMENT ON ROLE regress_du_admin IS 'User createrole attribute';
+COMMENT ON ROLE regress_du_role1 IS 'Group role without password but with valid until';
+COMMENT ON ROLE regress_du_role2 IS 'No connections allowed';
+COMMENT ON ROLE regress_du_role3 IS 'User without attributes';
GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
@@ -1842,19 +1850,28 @@ GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
-GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
\drg regress_du_role*
-\du regress_du_role*
-DROP ROLE regress_du_role0;
-DROP ROLE regress_du_role1;
-DROP ROLE regress_du_role2;
-DROP ROLE regress_du_admin;
+-- run as superuser
+\du+ regress_du_*
+
+-- run as user with createrole attribute
+SET ROLE regress_du_admin;
+\du regress_du_*
+
+-- run as unprivileged user
+SET ROLE regress_du_role0;
+\du regress_du_*
+
+RESET ROLE;
+
+DROP ROLE regress_du_role0, regress_du_role1, regress_du_role2, regress_du_role3, regress_du_admin;
-- Test display of empty privileges.
BEGIN;
--
2.34.1
