Thread: Extracting \ Generate DDL for existing object permissions

Hello all,

How do I capture the EXISTING permissions for a database?  I know you can
see them via \z or \dp however I need ALL permissions to be captured so I
can reapply after performing a database restore.

ex:
1) dump database A -
2) Extract permissions from database B - (HOW????)
3) load database B from A -
4) execute sql to put permissions back to database B from step 2

Thanks...Michelle
--
View this message in context:
http://www.nabble.com/Extracting-%5C-Generate-DDL-for-existing-object-permissions-tp17413948p17413948.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 22/05/2008 22:33, smiley2211 wrote:

> 1) dump database A -
> 2) Extract permissions from database B - (HOW????)

How about dumping B using the plain-text format, then running the output
through grep, looking for lines starting with "GRANT..."?

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Raymond O'Donnell wrote:
> On 22/05/2008 22:33, smiley2211 wrote:
>
>> 1) dump database A - 2) Extract permissions from database B - (HOW????)
>
> How about dumping B using the plain-text format, then running the output
> through grep, looking for lines starting with "GRANT..."?

Hint: sometimes easier is to run pg_dump -fc and then pg_restore -l to
create an object list and operate your filters on this list, then use
the resulting list with pg_restore -L

With GRANTS, which are one-liners, it works either way. But the general
approach works very well with any statement, think of multiline create
table, create function ...

Cheers
Tino