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