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

Robert,

Iam pleasedthatyouare payingso muchattentionto thispatch.

On 19.07.2024 16:26, Robert Haas wrote:
> Second, I think that the threshold question for this patch is: will
> users, on average, be happier if this patch gets committed? If the
> answer is yes, then the patch should be committed, and if the answer
> is no, the patch should not be committed. But I actually don't really
> have any clear idea of what users in general are likely to think. My
> own reaction is essentially ... meh. I do not think that the proposed
> new output is massively worse than what we have now, but I also don't
> think it's a whole lot better. Now, if a bunch of other people show up
> and vote, well then we'll have a much better view of what the typical
> user is likely to think.

Ishareyouropinionthatthe needfor a patchshouldbe decidedby the
votes(orlackof votes)of practicingexperts. Iam mainly
involvedineducationalprojects,soinmostcasesI
workwithdemosystems.Therefore, I'm notsurethatthe patch I'm offeringwill makeusershappy. Perhaps it should be withdrawn.

> Third, if I can back away from this particular patch for a moment, I
> feel like roles and permissions are one of the weaker areas in psql.

> So, personally, if I were going to work on a redesign in this area, I
> would look into making \du <username> work like \d <tablename>. That
> is, it would tell you every single thing there is to know about a
> user. Role attributes. Roles in which this role has membership. Roles
> that are a member of this row. Objects of all sorts this object owns.
> Permissions this role has on objects of all sorts. Role settings. All
> of it in SQL-ish format like we do with the footer when you run \d.

Oh, that's very interesting. I will think about this approach,
but I do not know when and what result can be obtained...

But let me share my thoughts on roles, privileges and system catalogs
from a different angle. This has nothing to do with the current patch,
I just want to share my thoughts.

I came to PostgreSQL from Oracle and it was unexpected for me that users
had almost complete access to the contents of the system сatalogs.
With rare exceptions (pg_authid, pg_statistic), any unprivileged user sees
the full contents of any system сatalog. (I'm not saying that access to system
catalogs needs to be reworked, it's probably impossible or very difficult.)

Visible but inaccessible objects in system catalogs increase the volume
of command output unnecessarily. Why do I need to know the list of all
schemas in the database if I only have access to the public schema?
The same applies to inaccessible tables, views, functions, etc.

Not for safety, but for convenience, it might be worth having a set of views
that show only those rows of the system catalog (with *acl column) that
the user has access to. Either as the object owner, or through the privileges.
Directly or indirectly through role membership.

By the way, this is exactly the approach implemented for the information
schema. Here is a code fragment of the information_schema.schemata view:

SELECT ...
FROM pg_namespace n,
pg_authid u
WHERE n.nspowner = u.oid AND
(pg_has_role(n.nspowner, 'USAGE'::text) OR
has_schema_privilege(n.oid, 'CREATE, USAGE'::text))

Then the commands like \dt, \df, \dn, \l, etc might use these views and show
only the objects accessible to the user. To do this, a new modifier to
the commands can be implemented, similar to the S modifier for system objects.

For example:
\dn - list of all schemas
\dnA - list of accessible schemas

In some way this approach can resolve your issue about roles and privileges.
Familiar psql commands will be able to display only the objects accessible
for current role,withoutpushingthe whole output into \du.

Such a set of views can be useful not only in psql, but also for third-party
applications.

I think I'm not the first one trying to bikeshedding in this area.
It's probably been discussed many times whythisshouldnotbe done.
But such thoughts do come, and I don't know the answer yet.

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