Re: Setting privilegies from one DB to another

On Aug 17, 2011, at 6:43 AM, JORGE MALDONADO wrote:

> I am using a database in a development PC and in a test server, it is the
> same in both. Changes are first made in the development PC and applied to
> the test server later. Actually, the modifications made to the DB in my
> development PC are related to privilegies; I set them for the 6 group roles
> in every object (database, schema, tablespace, tables, sequences). Now, it
> is time to get them to the test server but it would be time consuming if I
> do it one at a time and also error prone. Is there a way to "backup" only
> privilegies from one DB and "restore" them to another one?
>
> With respect,
> Jorge Maldonado

pg_dumpall has options for globals or roles only (depending on the version). Take a look at the Postgres Manual for your version (http://www.postgresql.org/docs/manuals/). It can be found under Server Administration -> Backup and Restore. The output will be a plain text file that you can feed into your favorite client. This will get all the roles and passwords.

As far as the privileges on each table, etc., that may take some work. I know a dump of the schema only will include that info. You may have to do that and then chop out all the other statements. I recently migrated a db and in 9.0.4 the dump utilities put all the privileges in one section. A bit of copy and paste could get what you need. I've seen options for no privileges, but have not seen an option for privileges only. Look into the options for pg_dump for just one database.

Brent.