Skip site navigation (1) Skip section navigation (2)

Re: Setting privilegies from one DB to another

From: Brent Dombrowski <brent(dot)dombrowski(at)gmail(dot)com>
To: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Setting privilegies from one DB to another
Date: 2011-08-17 15:28:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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 ( 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.


In response to

pgsql-novice by date

Next:From: Robert FrantzDate: 2011-08-17 18:59:59
Subject: How to restore an entire server from a .sql file created with PGAdmin Backup Server?
Previous:From: JORGE MALDONADODate: 2011-08-17 13:43:23
Subject: Setting privilegies from one DB to another

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group