On 17.02.2024 00:44, Tom Lane wrote:
"David G. Johnston"<david.g.johns...@gmail.com> writes:
Per the recent bug report, we should probably add something like (ignored)
after the 50 connections for role1 since they are not allowed to login so
the value is indeed ignored. It is ignored to zero as opposed to unlimited
for the Superuser so maybe a different word (not allowed)?
Not sure it's worth worrying about, but if we do I'd not bother to
show the irrelevant value at all: it's just making the display wider
to little purpose. We could make the column read as "(irrelevant)",
or leave it blank. I'd argue the same for password expiration
time BTW.
Please look at v5.
Changes:
- 'XXX(ignored)' replaced by '(irrelevant)' for 'Connection limit'.
for superusers with Connection limit
for roles without login and Connection limit
- 'XXX(invalid)' replaced by '(irrelevant)' for 'Valid until'.
for roles without password and Valid until
- 'Not allowed' replaced by '(not allowed)' for consistency.
for roles with Connection limit = 0
postgres@postgres(17.0)=# \du regress*
List of roles
Role name | Login | Attributes | Password? | Valid until |
Connection limit
------------------+-------+-------------+-----------+------------------------+------------------
regress_du_admin | yes | Create role+| yes | infinity |
| | 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 | (irrelevant) |
(irrelevant)
regress_du_role2 | yes | Inherit | yes | |
(not allowed)
regress_du_role3 | yes | | yes | |
10
regress_du_su | yes | Superuser +| yes | |
(irrelevant)
| | Create DB +| | |
| | Create role+| | |
| | Inherit +| | |
| | Replication+| | |
| | Bypass RLS | | |
(6 rows)
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
From 80d7038e72d36659fc8453567ce38660fc3a6364 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Sat, 17 Feb 2024 20:46:32 +0300
Subject: [PATCH v5 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 | 43 ++++++++++++++++++----------
src/test/regress/expected/rules.out | 37 +++++++++++++++---------
2 files changed, 51 insertions(+), 29 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 04227a72d1..acb6668e58 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
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b7488d760e..9cce3818ce 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1478,21 +1478,30 @@ pg_replication_slots| SELECT l.slot_name,
l.synced
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, synced)
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 6301b9f5df0a71e8cf054567cbb7b55c66c8463a Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <p.luza...@postgrespro.ru>
Date: Sat, 17 Feb 2024 20:47:35 +0300
Subject: [PATCH v5 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 | 165 ++++++++++-------------------
src/test/regress/expected/psql.out | 106 ++++++++++++++----
src/test/regress/sql/psql.sql | 37 +++++--
3 files changed, 171 insertions(+), 137 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b6a4eb1d56..6e0f895475 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,66 @@ 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 ) \"%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 \"%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 '%s'\n"
+ " ELSE r.rolvaliduntil::pg_catalog.text\n"
+ " END \"%s\",\n"
+ " CASE\n"
+ " WHEN r.rolconnlimit >= 0 AND r.rolsuper THEN '%s'\n"
+ " WHEN r.rolconnlimit >= 0 AND NOT r.rolcanlogin THEN '%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("(irrelevant)"),
+ gettext_noop("Valid until"),
+ gettext_noop("(irrelevant)"),
+ gettext_noop("(irrelevant)"),
+ 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 +3739,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..822e3e09db 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 and conn limit';
+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 | (irrelevant) | (irrelevant) | Group role without password but with valid until and conn limit
+ 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 | | (irrelevant) | 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 | (irrelevant) | (irrelevant)
+ regress_du_role2 | yes | Inherit | yes | | (not allowed)
+ regress_du_role3 | yes | | | | 10
+ regress_du_su | yes | Superuser +| | | (irrelevant)
+ | | 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 | (irrelevant)
+ regress_du_role2 | yes | Inherit | | | (not allowed)
+ regress_du_role3 | yes | | | | 10
+ regress_du_su | yes | Superuser +| | | (irrelevant)
+ | | 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..5853717aec 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 and conn limit';
+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
