Re: 8.2.0 upgrade issue: loss of CONNECT rights

L Bayuk <lbayuk(at)pobox(dot)com> writes:
> Another thing to watch out for when upgrading to 8.2.0:
> I had some 8.1.x databases with restricted rights, like REVOKE CREATE ON
> DATABASE. After pg_dumpall and reload into 8.2.0, I lost CONNECT rights on
> those databases. So only the superuser account can connect. The reason
> seems to be that my dump file had REVOKE ALL ON DATABASE, then added the
> specific GRANT rights. At 8.2.0, REVOKE ALL includes revoking CONNECT
> rights even though they were always granted at 8.1.5.

Hmmm ... I think we could hack 8.2's pg_dumpall to compensate for that,
ie throw in a GRANT CONNECT ... TO PUBLIC when dumping from a pre-8.2
server. But of course this wouldn't do anything for databases dumped
with older versions of pg_dumpall.

regards, tom lane