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

On 23.06.2023 03:50, Tom Lane wrote:

Nearby I dissed psql's \du command for its incoherent "Attributes"
column [1]. It's too late to think about changing that for v16,
but here's some things I think we should consider for v17:

If there are no others willing, I am ready to take up this topic. There
is definitely room for improvement here.
But first I want to finish with the \du command.

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

On 23.06.2023 03:50, Tom Lane wrote:

* On a purely presentation level, how did we possibly arrive
at the idea that the connection-limit and valid-until properties
deserve their own lines in the Attributes column while the other
properties are comma-separated? That makes no sense whatsoever,
nor does it look nice in \x display format.

I think this a reason why footer property explicitly disabled in the output.
As part of reworking footer should be enabled, as it worked for other
meta-commands.

Just to don't forget.

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

Now I'm ready for discussion.

On 23.06.2023 03:50, Tom Lane wrote:

Nearby I dissed psql's \du command for its incoherent "Attributes"
column [1]. It's too late to think about changing that for v16,
but here's some things I think we should consider for v17:

* It seems weird that some attributes are described in the negative
("Cannot login", "No inheritance"). I realize that this corresponds
to the defaults, so that a user created by CREATE USER with no options
shows nothing in the Attributes column; but I wonder how much that's
worth. As far as "Cannot login" goes, you could argue that the silent
default ought to be for the properties assigned by CREATE ROLE, since
the table describes itself as "List of roles". I'm not dead set on
changing this, but it seems like a topic that deserves a fresh look.

Agree. The negative form looks strange.

Fresh look suggests to move login attribute to own column.
The attribute separates users and group roles, this is very important
information,
it deserves to be placed in a separate column. Of course, it can be
returned back to "Attributes" if such change is very radical.

On the other hand, rolinherit attribute lost its importance in v16.
I don't see serious reasons in changing the default value, so we can
leave it
in the "Attributes" column. In most cases it will be hidden.

* I do not like the display of rolconnlimit, ie "No connections" or
"%d connection(s)". A person not paying close attention might think
that that means the number of *current* connections the user has.
A minimal fix could be to word it like "No connections allowed" or
"%d connection(s) allowed". But see below.

connlimit attribute moved from "Attributes" column to separate column
"Max connections" in extended mode. But without any modifications to
it's values.
For me it looks normal.

* I do not like the display of rolvaliduntil, either.

Moved from "Attributes" column to separate column  "Password expire time"
in extended mode (+).

I suggest that maybe it'd
be okay to change the pg_roles view along the lines of

-       '********'::text as rolpassword,
+       case when rolpassword is not null then '********'::text end as rolpassword,

Done.
The same changes to pg_user.passwd for consistency.

Then we could fix \du to say nothing if rolpassword is null,
and when it isn't, print "Password valid until infinity" whenever
that is the case (ie, rolvaliduntil is null or infinity).

I think that writing the value "infinity" in places where there is no
value is
not a good thing. This hides the real value of the column. In addition,
there is no reason to set "infinity" when the password is always valid with
default NULL.

My suggestion to add new column "Has password?" in extended mode with
yes/no values and leave rolvaliduntil values as is.

* On a purely presentation level, how did we possibly arrive
at the idea that the connection-limit and valid-until properties
deserve their own lines in the Attributes column while the other
properties are comma-separated? That makes no sense whatsoever,
nor does it look nice in \x display format.
(b) move these two things into separate columns.

Implemented this approach.

In a result describeRoles function significantly simplified and
rewritten for the convenience
of printing the whole query result. All the magic of building
"Attributes" column
moved to SELECT statement for easy viewing by users via ECHO_HIDDEN
variable.

Here is an example output.

--DROP ROLE alice, bob, charlie, admin;

CREATE ROLE alice LOGIN SUPERUSER NOINHERIT PASSWORD 'alice' VALID UNTIL 'infinity' CONNECTION LIMIT 5;
CREATE ROLE bob LOGIN REPLICATION BYPASSRLS CREATEDB VALID UNTIL '2022-01-01';
CREATE ROLE charlie LOGIN CREATEROLE PASSWORD 'charlie' CONNECTION LIMIT 0;
CREATE ROLE admin;

COMMENT ON ROLE alice IS 'Superuser but with connection limit and with no inheritance';
COMMENT ON ROLE bob IS 'No password but with expire time';
COMMENT ON ROLE charlie IS 'No connections allowed';
COMMENT ON ROLE admin IS 'Group role without login';

Master.
=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
admin | Cannot login
alice | Superuser, No inheritance +
| 5 connections +
| Password valid until infinity
bob | Create DB, Replication, Bypass RLS +
| Password valid until 2022-01-01 00:00:00+03
charlie | Create role +
| No connections
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

=# \du+
List of roles
Role name | Attributes | Description
-----------+------------------------------------------------------------+-------------------------------------------------------------
admin | Cannot login | Group role without login
alice | Superuser, No inheritance +| Superuser but with connection limit and with no inheritance
| 5 connections +|
| Password valid until infinity |
bob | Create DB, Replication, Bypass RLS +| No password but with expire time
| Password valid until 2022-01-01 00:00:00+03 |
charlie | Create role +| No connections allowed
| No connections |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |

Patched.
=# \du
List of roles
Role name | Can login? | Attributes
-----------+------------+------------------------------------------------------------
admin | no |
alice | yes | Superuser, No inheritance
bob | yes | Create DB, Replication, Bypass RLS
charlie | yes | Create role
postgres | yes | Superuser, Create role, Create DB, Replication, Bypass RLS
(5 rows)

=# \du+
List of roles
Role name | Can login? | Attributes | Has password? | Password expire time | Max connections | Description
-----------+------------+------------------------------------------------------------+---------------+------------------------+-----------------+-------------------------------------------------------------
admin | no | | no | | -1 | Group role without login
alice | yes | Superuser, No inheritance | yes | infinity | 5 | Superuser but with connection limit and with no inheritance
bob | yes | Create DB, Replication, Bypass RLS | no | 2022-01-01 00:00:00+03 | -1 | No password but with expire time
charlie | yes | Create role | yes | | 0 | No connections allowed
postgres | yes | Superuser, Create role, Create DB, Replication, Bypass RLS | yes | | -1 |
(5 rows)

v1 of the patch attached. There are no tests and documentation yet.
make check fall in 2 existing tests due changes in pg_roles and \du.
Will be corrected.

Any opinions?

I plan to add a patch to the January commitfest.

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

On Sat, 30 Dec 2023 at 09:23, Pavel Luzanov <p.luzanov@postgrespro.ru>
wrote:

I think that writing the value "infinity" in places where there is no
value is
not a good thing. This hides the real value of the column. In addition,
there is no reason to set "infinity" when the password is always valid with
default NULL.

Would it make sense to make the column non-nullable and always set it to
infinity when there is no expiry?

In this case, I think NULL simply means infinity, so why not write that? If
the timestamp type didn't have infinity, then NULL would be a natural way
of saying that there is no expiry, but with infinity as a possible value, I
don't see any reason to think of no expiry as being the absence of an
expiry time rather than an infinite expiry time.

On 30.12.2023 17:33, Isaac Morland wrote:

Would it make sense to make the column non-nullable and always set it
to infinity when there is no expiry?

A password is not required for roles. In many cases, external
authentication is used in ph_hba.conf.
I think it would be strange to have 'infinity' for roles without a password.

Tom suggested to have 'infinity' in the \du output for roles with a
password.
My doubt is that this will hide the real values (absence of values). So
I suggested a separate column
'Has password?' to show roles with password and unmodified column
'Password expire time'.

Yes, it's easy to replace NULL with "infinity" for roles with a
password, but why?
What is the reason for this action? Absence of value for 'expire time'
clear indicates that there is no time limit.
Also I don't see a practical reasons to execute next command, since it
do nothing:

ALTER ROLE .. PASSWORD 'infinity';

So I think that in most cases there is no "infinity" in the
rolvaliduntil column.

But of course, I can be wrong.

Thank you for giving your opinion.

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

On Thu, Jun 22, 2023 at 8:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

* It seems weird that some attributes are described in the negative
("Cannot login", "No inheritance"). I realize that this corresponds
to the defaults, so that a user created by CREATE USER with no options
shows nothing in the Attributes column; but I wonder how much that's
worth. As far as "Cannot login" goes, you could argue that the silent
default ought to be for the properties assigned by CREATE ROLE, since
the table describes itself as "List of roles". I'm not dead set on
changing this, but it seems like a topic that deserves a fresh look.

I wonder if we shouldn't try to display the roles's properties using
SQL keywords rather than narrating. Someone can be confused by "No
connections" but "CONNECTION LIMIT 0" is pretty hard to mistake;
likewise "LOGIN" or "NOLOGIN" seems clear enough. If we took this
approach, there would still be a question in my mind about whether to
show values where the configured value of the property matches the
default, and maybe we would want to do that in some cases and skip it
in others, or maybe we would end up with a uniform rule, but that
issue could be considered somewhat separately from how to print the
properties we choose to display.

--
Robert Haas
EDB: http://www.enterprisedb.com

Robert Haas <robertmhaas@gmail.com> writes:

I wonder if we shouldn't try to display the roles's properties using
SQL keywords rather than narrating. Someone can be confused by "No
connections" but "CONNECTION LIMIT 0" is pretty hard to mistake;
likewise "LOGIN" or "NOLOGIN" seems clear enough.

Mmm ... maybe. I think those of us who are native English speakers
may overrate the intelligibility of SQL keywords to those who aren't.
So I'm inclined to feel that preserving translatability of the
role property descriptions is a good thing. But it'd be good to
hear comments on that point from people who actually use it.

regards, tom lane

On Tue, Jan 2, 2024 at 1:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Mmm ... maybe. I think those of us who are native English speakers
may overrate the intelligibility of SQL keywords to those who aren't.
So I'm inclined to feel that preserving translatability of the
role property descriptions is a good thing. But it'd be good to
hear comments on that point from people who actually use it.

+1 for comments from people who use it.

My thought was that such people probably need to interpret LOGIN and
NOLOGIN into their preferred language either way, but if \du displays
something else, then they also need to mentally construct a reverse
mapping, from whatever string is showing up there to the corresponding
SQL syntax. The current display has that problem even for English
speakers -- you have to know that "Cannot login" corresponds to
"NOLOGIN" and that "No connections" corresponds to "CONNECTION LIMIT
0" and so forth. No matter what we put there, if it's English or
Turkish or Hindi rather than SQL, you have to try to figure out what
the displayed text corresponds to at the SQL level in order to fix
anything that isn't the way you want it to be, or to recreate the
configuration on another instance.

--
Robert Haas
EDB: http://www.enterprisedb.com

Robert Haas <robertmhaas@gmail.com> writes:

My thought was that such people probably need to interpret LOGIN and
NOLOGIN into their preferred language either way, but if \du displays
something else, then they also need to mentally construct a reverse
mapping, from whatever string is showing up there to the corresponding
SQL syntax. The current display has that problem even for English
speakers -- you have to know that "Cannot login" corresponds to
"NOLOGIN" and that "No connections" corresponds to "CONNECTION LIMIT
0" and so forth.

True, although if you aren't happy with the current state then what
you actually need to construct is a SQL command to set a *different*
state from what \du is saying. Going from LOGIN to NOLOGIN or vice
versa can also be non-obvious. So you're likely to end up consulting
"\h alter user" no matter what, if you don't have it memorized.

I think your argument does have relevance for the other issue about
whether it's good to be silent about the defaults. If \du says
nothing at all about a particular property, that certainly isn't
helping you to decide whether you want to change it and if so to what.
I'm not convinced that point is dispositive, but it's something
to consider.

regards, tom lane

On Tue, Jan 2, 2024 at 1:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

True, although if you aren't happy with the current state then what
you actually need to construct is a SQL command to set a *different*
state from what \du is saying. Going from LOGIN to NOLOGIN or vice
versa can also be non-obvious. So you're likely to end up consulting
"\h alter user" no matter what, if you don't have it memorized.

That could be true in some cases, but I don't think it's true in all
cases. If you're casually familiar with ALTER USER you probably
remember that many of the properties are negated by sticking NO on the
front; you're less likely to forget that than you are to forget the
name of some specific property. And certainly if you see CONNECTION
LIMIT 24 and you want to change it to CONNECTION LIMIT 42 it's going
to be pretty clear what to adjust.

I think your argument does have relevance for the other issue about
whether it's good to be silent about the defaults. If \du says
nothing at all about a particular property, that certainly isn't
helping you to decide whether you want to change it and if so to what.
I'm not convinced that point is dispositive, but it's something
to consider.

I agree with 100% of what you say here.

To add to that a bit, I would probably never ask a user to give me the
output of \du to troubleshoot some issue. I would either ask them for
pg_dumpall -g output, or I'd ask them to give me the raw contents of
pg_authid. That's because I know that either of those things are going
to tell me about ALL the properties of the role, or at least all of
the properties of the role that are stored in pg_authid, without
omitting anything that some hacker thought was uninteresting. I don't
know that \du is going to do that, and I'm not going to want to read
the code to figure out which cases it thinks are uninteresting,
*especially* if it behaves differently by version.

The counterargument is that if you don't omit anything, the output
gets very long, which is a problem, because either you go wide, and
then you get wrapping, or you use multiple-lines, and then if there
are 500 users the output goes on forever.

I think a key consideration here is how easy it will be for somebody
to guess the value of a property that is not mentioned. Personally,
I'd assume that if CONNECTION LIMIT isn't mentioned, it's unlimited.
But a lot of the other options are less clear. Probably NOSUPERUSER is
the default and SUPERUSER is the exception, but it's very unclear
whether LOGIN or NOLOGIN is should be treated as the "normal" case,
given that the feature encompasses users and groups and non-login
roles that people access via SET ROLE and things that look like groups
but are also used as login roles.

And with some of the other options, it's just harder to remember
whether there's a default and what it is exactly than for other object
types. With something like a table column, it feels intuitive that if
you just ask for a table column, you get a "normal" table column ...
and then if you add a fillfactor or a CHECK constraint it will show up
in the \d output, and otherwise not. But to try to apply that concept
here means that we suppose the user knows whether the default is
INHERIT or NOINHERIT, whether the default is BYPASSRLS or NOBYPASSRLS,
etc. And I'm just a little bit skeptical of that assumption. Perhaps
it's just that I've spent less time doing user management than table
administration and so I'm the only one who finds this fuzzier than
some other kinds of SQL objects, but I'm not sure it's just that.
Roles are pretty weird.

--
Robert Haas
EDB: http://www.enterprisedb.com

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 1/2/24 1:38 PM, Robert Haas wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CA+Tgmoa6VFBze4Lecws9C1VBGn1VGZ5jozDEPU93Kb_dJ+h-sw@mail.gmail.com">
<pre>But to try to apply that concept
here means that we suppose the user knows whether the default is
INHERIT or NOINHERIT, whether the default is BYPASSRLS or NOBYPASSRLS,
etc. And I'm just a little bit skeptical of that assumption. Perhaps
it's just that I've spent less time doing user management than table
administration and so I'm the only one who finds this fuzzier than
some other kinds of SQL objects, but I'm not sure it's just that.
Roles are pretty weird.</pre>
</blockquote>
<p>In my consulting experience, it's extremely rare for users to do
anything remotely sophisticated with roles (I was always happy
just to see apps weren't connecting as a superuser...).</p>
<p>Like you, I view \du and friends as more of a "helping hand" to
seeing the state of things, without the expectation that every
tiny nuance will always be visible, because I don't think it's
practical to do that in psql. While that behavior might surprise
some users, the good news is once they start exploring non-default
options the behavior becomes self-evident.</p>
<p>Some attributes are arguably important enough to warrant their
own column. The most obvious is NOLOGIN, since those roles are
generally used for a very different purpose than LOGIN roles.
SUPERUSER might be another candidate (though, I much prefer a
dedicated "sudo role" than explicit SU on roles).</p>
<p>I'm on the fence when it comes to SQL syntax vs what we have now.
What we currenly have is more readable, but off-hand I think the
other places we list attributes we do it in SQL syntax. It might
be worth changing just for consistency sake.</p>
<p><font size="2">--<br>
Jim Nasby, Data Architect, Austin TX</font><br>
</p>
</body>
</html>

On 02.01.2024 22:38, Robert Haas wrote:

To add to that a bit, I would probably never ask a user to give me the
output of \du to troubleshoot some issue. I would either ask them for
pg_dumpall -g output, or I'd ask them to give me the raw contents of
pg_authid. That's because I know that either of those things are going
to tell me about ALL the properties of the role, or at least all of
the properties of the role that are stored in pg_authid, without
omitting anything that some hacker thought was uninteresting. I don't
know that \du is going to do that, and I'm not going to want to read
the code to figure out which cases it thinks are uninteresting,
*especially* if it behaves differently by version.

\d commands are a convenient way to see the contents of the system
catalogs. Short commands, instead of long SQL queries. Imo, this is their
main purpose.

Interpreting values ('No connection' instead of 0 and so on)
can be useful if the actual values are easy to identify. If there is
doubt whether it will be clear, then it is better to show it as is.
The documentation contains a description of the system catalogs.
It tells you how to interpret the values correctly.

The counterargument is that if you don't omit anything, the output
gets very long, which is a problem, because either you go wide, and
then you get wrapping, or you use multiple-lines, and then if there
are 500 users the output goes on forever.

This can be mostly solved by using extended mode. Key properties for \du,
all others for \du+. Also \du+ can used with \x.
Of course, the question arises as to which properties are key and
which are not. Here we need to reach a compromise.

Personally,
I'd assume that if CONNECTION LIMIT isn't mentioned, it's unlimited.
But a lot of the other options are less clear. Probably NOSUPERUSER is
the default and SUPERUSER is the exception, but it's very unclear
whether LOGIN or NOLOGIN is should be treated as the "normal" case,
given that the feature encompasses users and groups and non-login
roles that people access via SET ROLE and things that look like groups
but are also used as login roles.

And with some of the other options, it's just harder to remember
whether there's a default and what it is exactly than for other object
types.

psql provides a handy tool for solving such questions - ECHO_HIDDEN variable.
But it is very important that the query text is easily transformed into
the command output.

Proposed patch tries to implement this approach.

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

On 03.01.2024 02:37, Jim Nasby wrote:

Some attributes are arguably important enough to warrant their own
column. The most obvious is NOLOGIN, since those roles are generally
used for a very different purpose than LOGIN roles. SUPERUSER might be
another candidate (though, I much prefer a dedicated "sudo role" than
explicit SU on roles).

I like this idea.
But what if all the attributes are moved to separate columns?
This solves all issues except the wide output. Less significant attributes
can be moved to extended mode. Here's what it might look like:

postgres@postgres(17.0)=# \du
List of roles
Role name | Login | Superuser | Create role | Create DB | Replication
-----------+-------+-----------+-------------+-----------+-------------
admin | no | no | no | no | no
alice | yes | yes | no | no | no
bob | yes | no | no | yes | yes
charlie | yes | no | yes | no | no
postgres | yes | yes | yes | yes | yes
(5 rows)

postgres@postgres(17.0)=# \du+
List of roles
Role name | Login | Superuser | Create role | Create DB | Replication | Bypass RLS | Inheritance | Password | Valid until | Connection limit | Description
-----------+-------+-----------+-------------+-----------+-------------+------------+-------------+----------+------------------------+------------------+-------------------------------------------------------------
admin | no | no | no | no | no | no | yes | no | | -1 | Group role without login
alice | yes | yes | no | no | no | no | no | yes | infinity | 5 | Superuser but with connection limit and with no inheritance
bob | yes | no | no | yes | yes | yes | yes | no | 2022-01-01 00:00:00+03 | -1 | No password but with expire time
charlie | yes | no | yes | no | no | no | yes | yes | | 0 | No connections allowed
postgres | yes | yes | yes | yes | yes | yes | yes | yes | | -1 |
(5 rows)

postgres@postgres(17.0)=# \x \du+ bob
Expanded display is on.
List of roles
-[ RECORD 1 ]----+---------------------------------
Role name | bob
Login | yes
Superuser | no
Create role | no
Create DB | yes
Replication | yes
Bypass RLS | yes
Inheritance | yes
Password | no
Valid until | 2022-01-01 00:00:00+03
Connection limit | -1
Description | No password but with expire time

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

Another approach based on early suggestions.

The Attributes column includes only the enabled logical attributes.
Regardless of whether the attribute is enabled by default or not.
This changes the current behavior, but makes it clearer: everything
that is enabled is displayed. This principle is easy to maintain in
subsequent versions, even if there is a desire to change the default
value for any attribute. In addition, the issue with the 'LOGIN' attribute
is being resolved, the default value of which depends on the command
(CREATE ROLE or CREATE USER).

The attribute names correspond to the keywords of the CREATE ROLE command.
The attributes are listed in the same order as in the documentation.
(I think that the LOGIN attribute should be moved to the first place,
both in the documentation and in the command.)

The "Connection limit" and "Valid until" attributes are placed in separate columns.
The "Password?" column has been added.

Sample output.

Patch v3:
=# \du
List of roles
Role name | Attributes | Password? | Valid until | Connection limit
-----------+-------------------------------------------------------------------+-----------+------------------------+------------------
admin | INHERIT | no | | -1
alice | SUPERUSER LOGIN | yes | infinity | 5
bob | CREATEDB INHERIT LOGIN REPLICATION BYPASSRLS | no | 2022-01-01 00:00:00+03 | -1
charlie | CREATEROLE INHERIT LOGIN | yes | | 0
postgres | SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS | no | | -1
(5 rows)

The output of the command is long. But there are other commands of
comparable length: \dApS, \dfS, \doS.

Small modification with newline separator for Attributes column:

Patch v3 with newlines:
=# \du
List of roles
Role name | Attributes | Password? | Valid until | Connection limit
-----------+-------------+-----------+------------------------+------------------
admin | INHERIT | no | | -1
alice | SUPERUSER +| yes | infinity | 5
| LOGIN | | |
bob | CREATEDB +| no | 2022-01-01 00:00:00+03 | -1
| INHERIT +| | |
| LOGIN +| | |
| REPLICATION+| | |
| BYPASSRLS | | |
charlie | CREATEROLE +| yes | | 0
| INHERIT +| | |
| LOGIN | | |
postgres | SUPERUSER +| no | | -1
| CREATEDB +| | |
| CREATEROLE +| | |
| INHERIT +| | |
| LOGIN +| | |
| REPLICATION+| | |
| BYPASSRLS | | |
(5 rows)

For comparison, here's what it looks like now:

master:
=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
admin | Cannot login
alice | Superuser, No inheritance +
| 5 connections +
| Password valid until infinity
bob | Create DB, Replication, Bypass RLS +
| Password valid until 2022-01-01 00:00:00+03
charlie | Create role +
| No connections
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

From my point of view, any of the proposed alternatives is better than what we have now.
But for moving forward we need to choose some approach.

I will be glad of any opinions.

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

2024-01 Commitfest.

Hi, This patch has a CF status of "Needs Review" [1]https://commitfest.postgresql.org/46/4738/, but it seems
there were CFbot test failures last time it was run [2]https://cirrus-ci.com/github/postgresql-cfbot/postgresql/commitfest/46/4738. Please have a
look and post an updated version if necessary.

======
[1]: https://commitfest.postgresql.org/46/4738/
[2]: https://cirrus-ci.com/github/postgresql-cfbot/postgresql/commitfest/46/4738

Kind Regards,
Peter Smith.

On Sun, Jan 21, 2024 at 2:35 PM Pavel Luzanov <p.luzanov@postgrespro.ru>
wrote:

Another approach based on early suggestions.

The Attributes column includes only the enabled logical attributes.
Regardless of whether the attribute is enabled by default or not.

The attribute names correspond to the keywords of the CREATE ROLE command.
The attributes are listed in the same order as in the documentation.
(I think that the LOGIN attribute should be moved to the first place,
both in the documentation and in the command.)

I'd just flip INHERIT and LOGIN

The "Connection limit" and "Valid until" attributes are placed in separate columns.
The "Password?" column has been added.

Sample output.

Patch v3:
=# \du
List of roles
Role name | Attributes | Password? | Valid until | Connection limit
-----------+-------------------------------------------------------------------+-----------+------------------------+------------------
admin | INHERIT | no | | -1
alice | SUPERUSER LOGIN | yes | infinity | 5
bob | CREATEDB INHERIT LOGIN REPLICATION BYPASSRLS | no | 2022-01-01 00:00:00+03 | -1
charlie | CREATEROLE INHERIT LOGIN | yes | | 0
postgres | SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS | no | | -1
(5 rows)

Small modification with newline separator for Attributes column:

Patch v3 with newlines:
=# \du
List of roles
Role name | Attributes | Password? | Valid until | Connection limit
-----------+-------------+-----------+------------------------+------------------
postgres | SUPERUSER +| no | | -1
| CREATEDB +| | |
| CREATEROLE +| | |
| INHERIT +| | |
| LOGIN +| | |
| REPLICATION+| | |
| BYPASSRLS | | |
(5 rows)

I'm strongly in favor of using mixed-case for the attributes. The SQL

Command itself doesn't care about capitalization and it is much easier on
the eyes. I'm also strongly in favor of newlines, as can be seen by the
default bootstrap superuser entry putting everything on one line eats up 65
characters.

List of roles
Role name | Attributes | Password? | Valid until | Connection limit |
Description
-----------+-------------+-----------+-------------+------------------+-------------
davidj | Superuser +| no | | -1 |
| CreateDB +| | | |
| CreateRole +| | | |
| Inherit +| | | |
| Login +| | | |
| Replication+| | | |
| BypassRLS | | | |
(1 row)

As noted by Peter this patch didn't update the two affected expected output
files. psql.out and, due to the system view change, rules.out. That
particular change requires a documentation update to the pg_roles system
view page. I'd suggest pulling out this system view change into its own
patch.

I will take another pass later when I get some more time. I want to
re-review some of the older messages. But the tweaks I show and breaking
out the view changes in to a separate patch both appeal to me right now.

David J.

Pavel Luzanov <p.luzanov@postgrespro.ru> writes:

Another approach based on early suggestions.

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. Can you get the
same result without changing pg_roles?

regards, tom lane

I 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.

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.

regards, tom lane

On Mon, Jan 22, 2024 at 6:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I 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.

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.

Makes sense, more reason to put it within its own patch. 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).

David J.