Thread: PostgreSQL 8.4 - dumping database connection privileges
Hi, I've recently upgraded to PostgreSQL 8.4 as Redhat had begun supporting it. I have tried to dump database grants, but have only found an obscure way to do it. I would expect; postgres$ pg_dump -Fc database_name > backup.pgdump would include all of the GRANT CONNECT on database_name TO "Role"; commands. It does not. Second I tried; postgres$ pg_dumpall -g > globals.sql This also did not produce any GRANT CONNECT statements. The only method I found that works is; postgres$ pg_dumpall -s | grep 'ON DATABASE' This method is not exactly fool proof and isn't what I expected to have to do. Is this considered a bug that the only way to do a dump/restore with database privileges is to use pg_dumpall? I expect that pg_dump of a database would include all of that information. I would argue it is at least a misfeature and difficult for even an experienced user to understand. Regards Russell
Russell Smith <mr-russ@pws.com.au> writes:
> Is this considered a bug that the only way to do a dump/restore with
> database privileges is to use pg_dumpall?
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").
regards, tom lane
On 02/05/10 01:36, Tom Lane wrote: > Russell Smith <mr-russ@pws.com.au> writes: > >> Is this considered a bug that the only way to do a dump/restore with >> database privileges is to use pg_dumpall? >> > 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"). > > regards, tom lane > 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. Also are use-cases that work now covered in the previous discussions, I'd like to know what they are. Regards Russell
Russell Smith <mr-russ@pws.com.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
On 03/05/10 01:30, Tom Lane wrote: > Russell Smith <mr-russ@pws.com.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) > > The TODO item covers my expected behaviour. If you create the database from scratch, you expect it to have all the alter commands, if you are restoring into in, you get what is already there. Also you can pg_restore to a text file if you want those global parameters out and work with that. So the information is not lost if it's stored in the dump with the customer format. At the moment the information is lost at dump time. I think Richard in correct in the discussion that even though database information is stored globally, it's not global. If it was, pg_dumpall -g would dump it. It's certainly overly complex to get pg_dumpall to give you those database parameters. I will leave it there on the basis of the TODO item and previous discussions. Regards Russell