Re: Fix output of zero privileges in psql - Mailing list pgsql-hackers

On 2023-10-06 22:32 +0200, Laurenz Albe write:
> On Sun, 2023-09-17 at 21:31 +0200, Erik Wienhold wrote:
> > I wrote a patch to change psql's display of zero privileges after a user's
> > reported confusion with the psql output for zero vs. default privileges [1].
> > Admittedly, zero privileges is a rare use case [2] but I think psql should not
> > confuse the user in the off chance that this happens.
> > 
> > With this change psql now prints "(none)" for zero privileges instead of
> > nothing.  This affects the following meta commands:
> > 
> >     \db+ \dD+ \df+ \dL+ \dl+ \dn+ \dp \dT+ \l
> > 
> > Default privileges start as NULL::aclitem[] in various catalog columns but
> > revoking the default privileges leaves an empty aclitem array.  Using
> > \pset null '(null)' as a workaround to spot default privileges does not work
> > because the meta commands ignore this setting.
> > 
> > The privileges shown by \dconfig+ and \ddp as well as the column privileges
> > shown by \dp are not affected by this change because those privileges are reset
> > to NULL instead of leaving empty arrays.
> > 
> > Commands \des+ and \dew+ are not covered in src/test/regress because no foreign
> > data wrapper is available at this point to create a foreign server.
> > 
> > [1] https://www.postgresql.org/message-id/efdd465d-a795-6188-7f71-7cdb4b2be031%40mtneva.com
> > [2] https://www.postgresql.org/message-id/31246.1693337238%40sss.pgh.pa.us
> 
> Reading that thread, I had the impression that there was more support for
> honoring "\pset null" rather than unconditionally displaying "(none)".

I took Tom's response in the -general thread to mean that we could fix
\pset null also as a "nice to have" but not as a solution to the display
of zero privileges.

Only fixing \pset null has one drawback IMO because it only affects how
default privileges (more common) are printed.  The edge case of zero
privileges (less common) gets lost in a bunch of NULL output.  And I
assume most users change the default \pset null to some non-empty string
in their psqlrc (I do).

For example with your patch applied:

    create table t1 (a int);
    create table t2 (a int);
    create table t3 (a int);

    revoke all on t2 from :USER;

    \pset null <NULL>
    \dp t1|t2|t3
                                Access privileges
     Schema | Name | Type  | Access privileges | Column privileges | Policies
    --------+------+-------+-------------------+-------------------+----------
     public | t1   | table | <NULL>            |                   |
     public | t2   | table |                   |                   |
     public | t3   | table | <NULL>            |                   |
    (3 rows)

Instead of only displaying the zero privileges with my patch and default
\pset null:

    \pset null ''
    \dp t1|t2|t3
                                Access privileges
     Schema | Name | Type  | Access privileges | Column privileges | Policies
    --------+------+-------+-------------------+-------------------+----------
     public | t1   | table |                   |                   |
     public | t2   | table | (none)            |                   |
     public | t3   | table |                   |                   |
    (3 rows)

I guess if most tables have any non-default privileges then both
solutions are equally good.

> The simple attached patch does it like that.  What do you think?

LGTM.

-- 
Erik