Re: apparent loss of database access permissions

eponymous alias <eponymousalias@yahoo.com> writes:
> I have been testing the use of pg_upgrade, and in so doing I notice
> that it loses most database access privileges which are listed under
> the "Access privileges" column in output from "psql --list".

Actually not; or at least, your test case proves no such thing.
That's because these two situations are completely equivalent:

>       Name       |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
> -----------------+-------------+----------+-------------+-------------+-----------------------------
>  report_db       | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

>       Name       |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
> -----------------+-------------+----------+-------------+-------------+-----------------------------
>  report_db       | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/report_user            +
>                  |             |          |             |             | report_user=CTc/report_user

If the access privileges are shown as empty (NULL), that implies the
object-type-specific default privileges are in effect; and for a
database that means the owner has all privileges (CTc) while
PUBLIC has "T" and "c" (temp and connect) privileges.

The reason it gets to be like that is that normal grant and revoke
operations don't make any effort to reset the ACL field to null if
the end result of a series of operations chances to be equivalent
to the default.  However, for reasons of its own pg_dump has to
compute the set of GRANT/REVOKE commands to issue to get from the
default state to the object's current state --- and in this situation
that list is empty.  So no commands are issued, the ACL stays null,
and after the restore you again see

>  report_db       | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

That's not a bug, just an implementation artifact.

We like the fact that the underlying behavior is like this,
because leaving default ACLs as null saves a whole lot of
storage in some catalogs, notably pg_proc and pg_attribute.
There's been occasional discussions of having psql's display
commands print the actual default ACL instead of null,
but so far the consensus has been that that'd bulk up the
listings without really adding much.  Admittedly this
consensus comes more from seasoned users than confused newbies,
but nonetheless there's little appetite to change it.

            regards, tom lane