Thread: 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
yourversion (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
andpasswords. 

As far as the privileges on each table, etc., that may take some work. I know a dump of the schema only will include
thatinfo. 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
dumputilities put all the privileges in one section. A bit of copy and paste could get what you need.  I've seen
optionsfor no privileges, but have not seen an option for privileges only. Look into the options for pg_dump for just
onedatabase. 

Brent.