SEO

-----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-----

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

Rui Lopes wrote:
> 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.

roles are part of the catalog/cluster not a database, thus -g is your
option.

It has been discussed in the past to have per database roles, but that
has not beed coded.

Joshua D. Drake


>
> 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

- ---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly



- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHA9i9ATb/zqfZUUQRAqorAKCslzpZqeAyj79nYY5amFFWTuJ7dwCgkowS
8vza+ntivUnancUTZa6eaJQ=
=MUB8
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly