Backup single database roles and ACLs

From: Rui Lopes <rgl(at)ruilopes(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Backup single database roles and ACLs
Date: 2007-10-03 17:44:39
Message-ID: 4703D507.50803@ruilopes.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

How do I backup all the roles and ACLs that have permissions to a single
database?

pg_dumpall -g does not do the trick because it dumps all the roles from
all the databases.

I've read the system catalogs documentation [1], but I didn't figure how
to relate the database name with the table names...

So far I can get all the database names, owners oid and ACLs using:

select oid,datname,datdba,datacl from pg_database;

But how do I relate pg_database.oid with pg_class (to get table ACLs)?
or maybe its related with other table that I'm missing?

TIA!

Best regards,
Rui Lopes

[1] http://www.postgresql.org/docs/8.2/static/catalogs.html
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHA9UHg6ss8INEYd4RAhGJAJ9gcuPY/VoIPUGnkB6QLEWaf6tsYACeOxul
qqUTkoo1WewaLKDxJXSZfHE=
=GLyA
-----END PGP SIGNATURE-----

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Lanzarotta 2007-10-03 17:58:08 Importing MySQL dump into PostgreSQL 8.2
Previous Message hubert depesz lubaczewski 2007-10-03 17:29:04 Re: Easier string concat in PL funcs?