Hello,
Currently, a role with the createrole attribute can create roles, set and
change their password,
but can't see the password. Can't even see if the password is set or not.
In this case, you can mistakenly set the Valid until attribute to roles without
a password.
And there is no way to detect such a situation.
In the patch for changing the \du command, I want to give the opportunity to
show
incorrect values of the Valid until attribute. [1]
I suggest changing the pg_roles view to allow a role with the createrole
attribute to see
information about the password of the roles that this role manages
(has membership with admin option).
There are several ways to implement it.
1.
Change the values of the rolpassword column. Now it always shows '********'.
The values should depend on the role executing the query.
If the query is executed by a superuser or a role with create role and admin
membership,
then show '********' instead of password or NULL (no password).
For other roles, show '<insufficient privileges>'.
This is implemented in the attached patch.
2.
Change the values of the rolpassword column.
If the query is executed by a superuser or a role with create role and admin
membership,
then show real password or NULL (no password).
For other roles, show '********'.
3.
Leave the rolpassword column as it is for backward compatibility, but add
a new logical rolhaspassword column.
If the query is executed by a superuser or a role with create role and admin
membership,
then show true/false depending on the password existence.
For other roles, show NULL.
Although it is possible that for security reasons such changes should not be
made.
1.https://www.postgresql.org/message-id/ef4d000f-6766-4ae1-9f69-0d0caa8130d6%40postgrespro.ru
--
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
