Re: Usability fail with psql's \dp command

Hello.

At Sun, 29 Jul 2018 21:34:29 -0400 (EDT), Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote in <alpine(dot)DEB(dot)2(dot)21(dot)1807291818460(dot)14827(at)lancre>
>
> >> My 0.02¤: this creates an exception for anyone trying to parse the
> >> output.
> >> I would have preferred empty logically meaning no rights, and the
> >> default
> >> being spelled out explicitely.
> >
> > Uh, who'd be trying to parse the output of \dp?
>
> Ok. Maybe humans?
>
> Note that 'No privileges' could be somehow interpreted as "default
> privileges" (no "special/given" privileges) or as "no permissions at
> all", so there is still some ambiguity, at least for me.

FWIW "No privileges" seems to me as "The user cannot access it at
all" with no ambiguity.

Currently the behavior is documented here. (This needs to be
edited.)

https://www.postgresql.org/docs/10/static/sql-grant.html

| If the “Access privileges” column is empty for a given object,
| it means the object has default privileges (that is, its
| privileges column is null). Default privileges always include all
| privileges for the owner, and can include some privileges for
| PUBLIC depending on the object type, as explained above. The
| first GRANT or REVOKE on an object will instantiate the default
| privileges (producing, for example, {miriam=arwdDxt/miriam}) and
| then modify them per the specified request.

So it changes the existing documented behavior.

What is most significant to me here is it's confusing that the
empty representation means rather opposite things for
pg_class.relacl and the correspondent in \dp's output.

relacl | Access privileges
--------+------------------
(null) | joe=arwdDxt/joe
{} | (null)

> > We could certainly consider the explicit-default approach (and it's
> > one
> > of the options I suggested), but to my mind we should evaluate the
> > options entirely on what humans find readable, with exactly zero
> > weight
> > to machine readability.
>
> Ok. So I agree with your suggestion, on the ground of avoiding a
> special output syntax in one particular case if possible.

\dp is a convenient shortcut for users so the output should be
intuitive or easy-to-grasp. If we wanted to use the output as a
input of other programs, we are to use bare tables.. maybe, or
should handle the special indications. But, if we were to change
the documented behavior, I'd propose the following.

relacl | Access privileges
--------+------------------
(null) | '(default)'
{} | '(no privilege)'

The parentheses ('()') can be '<>' as pg_stat_activity uses for a
content with a special meaning. (<insufficient privilege>). Also
I found that \dC shows '(binary coercible)' when the cast is a
relabel. So I'm not confident on whether to use but I'd like to
choose '()' for them. Both are not "parsable" for... a human?

> Attached is a quick and dirty attempt at regenerating default
> privileges from dp query, with an added join on roles & and test on
> kind.
>
> I'm not 100% sure of the list of privileges for all types, and I do
> not like much having them in a query like that because in the unlikely
> event that a new one is added, the query output suddenly becomes
> false.
>
> From a programming point of view there is another pain with that
> approach as "describe.c" uses "printACLColumn", but this version would
> need the kind and the owner role as well, and it seems that all 11
> instances of printACLColumn should be adapted as well.
>
> As for David point of breaking anything from a user perspective, as
> the current output is currently ambiguous thus unreliable/unusable, I
> think it is more a bug fix than anything else.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center