Re: PostgreSQL 8.4 - dumping database connection privileges

Russell Smith <mr-russ(at)pws(dot)com(dot)au> writes:
> On 02/05/10 01:36, Tom Lane wrote:
>> No, that's the intended place for them given the current division of
>> labor between pg_dump and pg_dumpall. There have been complaints before
>> about this, but no one has proposed a better approach (where better
>> means "fixes this without breaking use-cases that work now").

> I have just spent an hour searching the archives and have been unable to
> find any discussions on this issue. I must be searching the wrong
> terms. Any ideas? I would like to discuss this further, but it's a
> little futile without reading the background material.

If you were looking specifically for mention of CONNECT privileges, you
likely wouldn't have found much, because that's a pretty new feature.
Most of the previous discussions have related to other database-level
attributes, such as ALTER DATABASE SET properties --- but it's basically
the same problem. A few minutes of searching turned up several threads:

http://archives.postgresql.org/pgsql-hackers/2009-09/msg01944.php
http://archives.postgresql.org/pgsql-hackers/2006-10/msg00432.php
http://archives.postgresql.org/pgsql-hackers/2008-06/msg01031.php

(the last of these is actually a TODO entry)

To my mind there are several issues associated with pushing any of that
functionality into pg_dump:

* Currently it's possible to restore pg_dump output into a database
having a different name than the one that was dumped. This becomes
problematic if the dump contains commands that try to set database-level
attributes, since those commands will refer to the DB by name. The
TODO list suggests inventing a "CURRENT DATABASE" syntax for such
commands, but that seems like a lot of work for a rather marginal issue.
We might also consider making the dumping of these properties an
optional behavior (then it's on the user's head to not do it if he
wants to restore to a different DB name). The name of the database's
owner might be a risk factor too, not sure.

* In many cases (especially with something like CONNECT privilege),
correctly restoring this state would require that the destination
installation have the same set of users/groups as the source did.
For pg_dumpall that's not a problem because it dumps the users first,
but if you're going to put this in pg_dump you need a strategy for
coping. Now pg_dump already has the issue with respect to ownership
and privileges on individual objects, and it has a couple of coping
strategies: there's --no-owner, and the dump output is also designed
so that the ALTER OWNER and GRANT commands can fail without taking
out the whole object. How would you map these strategies, or invent
new ones, for database-level attributes?

* As of 9.0 we have ALTER ROLE IN DATABASE SET, ie configuration
settings that are specific to a user+database combination. In the
current dispensation where only pg_dumpall is responsible for dumping
these things, that's no great problem --- it just dumps them along
with the plain ALTER ROLE SET and ALTER DATABASE SET commands. If some
of this functionality is to be moved into pg_dump, how are you going
to divide the responsibility? And again, what if the role's not there?

* The user might not even want this info in pg_dump output, for instance
for backward compatibility with some established procedure or other.
So you probably need a switch to turn it off even if you haven't decided
you need one because of one of the above points.

What it comes down to is that pg_dump output has to be considerably
more flexible than pg_dumpall output, and nobody's done the work to
run down all the cases and show how we can move this info into pg_dump
without creating problems.

regards, tom lane