Re: Things I don't like about \du's "Attributes" column

On 23.01.2024 04:18, Tom Lane wrote:

I think expecting the pg_roles view to change for this is problematic.
You can't have that in the back branches, so with this patch psql
will show something different against a pre-17 server than later
versions.  At best, that's going to be confusing.

I've been thinking about it. Therefore, the column "Password?" is shown
only for version 17 and older.

  Can you get the same result without changing pg_roles?

Hm. I'm not sure if this is possible.

Actually, even more to the point: while this doesn't expose the
contents of a role's password, it does expose whether the role
*has* a password to every user in the installation.  I doubt
that that's okay from a security standpoint.  It'd need debate
at the least.

Yes, I remember your caution about security from the original post.
I'll try to explain why changing pg_roles is acceptable.
Now \du shows column "Valid until". We know that you can set
the password expiration date without having a password, but this is
more likely a mistake in role maintenance. In most cases, a non-null
value indicates that the password has been set. Therefore, security
should not suffer much, but it will help the administrator to see
incorrect values.

On 23.01.2024 05:22, David G. Johnston wrote:
> At present it seems like a createrole permissioned user is unable 
> to determine whether a given role has a password or not even in the case
> when that role would be allowed to alter a role they've created to set or
> remove said password.  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> (alt. N/A).

Good point.
But what about "Valid until". Can roles without superuser or createrole
attributes see it? The same about "Connection limit"?

I'll think about it and try to implement in the next patch version within a few days.
Thank you for review.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com