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

From: Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jim Nasby <jim(dot)nasby(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Things I don't like about \du's "Attributes" column
Date: 2024-07-17 21:09:09
Message-ID: 2570803e-e68f-4830-a7ff-6ad89662c55f@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 16.07.2024 18:00, Robert Haas wrote:

> On the question of display width, my personal opinion is that the
> current patch is worse than what we have now.

Robert, David, thanks for the detailed explanation.

I tried to remember all the thoughts that led to this version of the patch.

So the main issue that Robert points out is that the output of the command
takes up more space compared to the current version.
(But I'm ready to debate that too :-), see below.)

In the proposed version, columns for rolconnlimit and rolvaliduntil occupy
a significant place. It really is. We can hide them in extended mode, but
they still take up a lot of space. In the current command, these attributes
are very compactly arranged in the "Attributes" column on separate lines.

However, the current placement of rolconnlimit and rolvaliduntil on separate
lines is very bad, which Tom noted in the first letter and I completely
agree with this. Also, I don't like that the values appear only if they
differ from the default values. It's more compact, but less intuitive.
It seems to me that this approach is not used anywhere else in other
\d* commands (but I may be wrong, I did not check).

Let me explain why I think rolconnlimit and rolvaliduntil are worthy
of being placed as separate columns.

1. Logical attributes (rolsuper, rolinherit, rolcreaterole, rolcreatedb,
rolcanlogin, rolreplication, rolbypassrls) are uniform in nature and
presenting them as a list in one column looks logical.
But rolconnlimit and rolvaliduntil do not fit into this company in any way.
They are strangers here in terms of data type and meaning.

2. Logical attributes give the role additional capabilities,
while rolconnlimit and rolvaliduntil rather limit the use of the role.

3. After switching to a role with the SET ROLE command, you can use
the capabilities of logical attributes, but the restrictions of rolconnlimit
and rolvaliduntil do not apply to SET ROLE:

postgres(at)demo(17.0)=# grant bob to alice;
grant bob to alice;
GRANT ROLE
postgres(at)demo(17.0)=# alter role bob connection limit 0;
alter role bob connection limit 0;
ALTER ROLE
postgres(at)demo(17.0)=# \c - bob
connection to server on socket "/tmp/.s.PGSQL.5401" failed: FATAL: too many connections for role "bob"
Previous connection kept
postgres(at)demo(17.0)=# \c - alice
You are now connected to database "demo" as user "alice".
alice(at)demo(17.0)=> set role bob;
set role bob;
SET

This makes it reasonable to consider rolconnlimit and rolvaliduntil
as separate properties of a role, rather than together with logical
attributes.

Now the hard part. What to do with the width of the command output?
I also think that it is desirable to fit the output of any command in 80
characters. And I was calm when I saw the 78-character output in my test
system:

postgres(at)demo(17.0)=# \du
List of roles
Role name | Login | Attributes | Valid until | Connection limit
-----------+-------+-------------+------------------------+------------------
alice | yes | Inherit | 2024-06-30 00:00:00+03 |
bob | yes | Inherit | infinity |
charlie | yes | Inherit | | 1
postgres | yes | Superuser +| |
| | Create DB +| |
| | Create role+| |
| | Inherit +| |
| | Replication+| |
| | Bypass RLS | |
(4 rows)

But, really, the width can exceed 80 with longer role names, as well as with
a wider default date output. Compare with the date output in the patch regression
tests:

2024-06-30 00:00:00+03
Tue Jun 04 00:00:00 2024 PDT

To be fair, I must say that among the \d* commands there are many commands
whose output width exceeds 80 characters.
Namely: \da, \dAc, \dAf, \dAo, \dAp, \dC, \df, \di, \do, \dO, \dRp, \dT, \l

But let's go back to the current version. I consider this patch as
a continuation of the work on the \drg command that appeared in version 16.
As part of that work, we removed the "Member of" column from the \du command
and introduced a new \drg command to show membership in roles.
From my point of view, the \du command is currently in an intermediate and
unfinished state. Therefore, it is more correct to compare the proposed patch
with psql 15, rather than 16.
(I know there is nothing more permanent than a temporary solution,
but give me hope :-).)

In version 15, the output of the \du command is wider than the proposed version!

15=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

And this is with only one role. I can assume that there are usually several
roles in systems and role membership is actively used to organize roles within
groups. Therefore, in real systems, the output of the \du command in version 15
is probably much wider. For example, output together with system objects:

15=# \duS
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------------------------------------
pg_execute_server_program | Cannot login | {}
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
pg_read_all_settings | Cannot login | {}
pg_read_all_stats | Cannot login | {}
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

All this allows me to believe that the proposed version has advantages over
the current version of the \du command:
- Solutions have been proposed for 3 of the 4 Tom's complaints.
- The new "Login" column separates users from group roles, which is very useful (imho).
- Tabular output is convenient to view both in normal mode and in expanded mode (\x).
The last line contains information about the number of roles.
- Refactoring: code has become much simpler and clearer.

But if we don't find a compromise and just leave it as it is, then that's fine.
So the time for change has not come yet. In any case, this discussion may be useful
in the future.Butwhoknows,maybenowwecancometosomekindof agreement.

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2024-07-17 21:16:59 Re: optimizing pg_upgrade's once-in-each-database steps
Previous Message Noah Misch 2024-07-17 20:41:47 Re: Use read streams in CREATE DATABASE command when the strategy is wal_log