Re: Things I don't like about \du's "Attributes" column - Mailing list pgsql-hackers

Robert,

I am pleased that you are paying so much attention to this patch.

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.

I share your opinion that the need for a patch should be decided
by the votes (or lack of votes) of practicing experts. I am mainly
involved in educational projects, so in most cases I work with
demo systems. Therefore, I'm not sure that the patch I'm offering
will make users happy. 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, without pushing the 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 why this should not be done.
But such thoughts do come, and I don't know the answer yet.

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