Thread: pg_dump and roles
Group, I have a postgres implementation with approximately 150 databases. This was not my choice, but what I was given. What I need to do is backup individual databases and be able to restore any database along with it's roles. I understand the pg_dump and restore commands, they are quite clear and easy to use. The problem is the roles; other than postgres, I have to users, let's call them "update" and "read". I did the grants for these 2 roles on each table for each database. The documentation states that pg_dump doesn't dump roles. Is there a method to dump just the roles from a database, or otherwise select them, so I can build a script to reload the roles? Otherwise, I guess I'll just read the tables for each database and build the security script from that. I need to be able to restore individual databases, so pg_dumpall doesn't look like the route for me. I've looked at the internal tables and couldn't find anything with has my roles in it. TIA, Julie
Julie Warden wrote: > Is there a method to dump just the roles from a database, or otherwise > select them, so I can build a script to reload the roles? Otherwise, I > guess I'll just read the tables for each database and build the > security script from that. Roles are not per-database; they are global objects. You can get a dump of all the roles with pg_dumpall -g. If you really need to filter out roles per database, my best suggestion is to filter a global role dump them with the pg_shdepend catalog for each database. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
use pg_dumpall -g
--
Peter Steinheuser
EnterpriseDB Corporation
499 Thornall Street
2nd Floor
Edison, NJ 08837
+1.732.331.1378 Office
+1.908.419.4220 Cell
+1.732.331.1301 Fax
peter.steinheuser@enterprisedb.com
http://www.enterprisedb.com
Built on open source. Compatible with Oracle®.
On Fri, Oct 3, 2008 at 1:35 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Julie Warden wrote:Roles are not per-database; they are global objects. You can get a dump
> Is there a method to dump just the roles from a database, or otherwise
> select them, so I can build a script to reload the roles? Otherwise, I
> guess I'll just read the tables for each database and build the
> security script from that.
of all the roles with pg_dumpall -g.
If you really need to filter out roles per database, my best suggestion
is to filter a global role dump them with the pg_shdepend catalog for
each database.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Peter Steinheuser
EnterpriseDB Corporation
499 Thornall Street
2nd Floor
Edison, NJ 08837
+1.732.331.1378 Office
+1.908.419.4220 Cell
+1.732.331.1301 Fax
peter.steinheuser@enterprisedb.com
http://www.enterprisedb.com
Built on open source. Compatible with Oracle®.
> Is there a method to dump just the roles from a database, or otherwise > select them, so I can build a script to reload the roles? Otherwise, I > guess I'll just read the tables for each database and build the > security script from that. from man pg_dumpall: -r --roles-only Dump only roles, no databases or tablespaces. Eugene
On Fri, 03 Oct 2008 13:21:46 GMT, Julie Warden <Julie_Warden@nospam.hotmail.com> wrote: >Group, > >I have a postgres implementation with approximately 150 databases. >This was not my choice, but what I was given. > >What I need to do is backup individual databases and be able to >restore any database along with it's roles. I understand the pg_dump >and restore commands, they are quite clear and easy to use. > >The problem is the roles; other than postgres, I have 2 users, let's >call them "update" and "read". I did the grants for these 2 roles on >each table for each database. The documentation states that pg_dump >doesn't dump roles. > >Is there a method to dump just the roles from a database, or otherwise >select them, so I can build a script to reload the roles? Otherwise, I >guess I'll just read the tables for each database and build the >security script from that. > >I need to be able to restore individual databases, so pg_dumpall >doesn't look like the route for me. I've looked at the internal tables >and couldn't find anything with has my roles in it. > >TIA, >Julie Group, Thanks for the help, but I don't think I expressed myself clearly enough. What the pg_dumpall gives me is the CREATE ROLE commands only (with -g I also get CREATE TABLESPACE). This is not what I want. I'm dumping individual databases, so I want the GRANT statements for each table in the database schemas. For example, given a database with 2 user roles, read and update I want to generate whatever GRANT statements are associated with that database - like this: GRANT SELECT ON TABLE_1 TO read; GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE_1 TO update; GRANT SELECT ON TABLE_2 TO read; GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE_2 TO update; These statements are specfic to the table objects in each database. I've looked everywhere I could find in the postgres 8.2 manual and cannot find anything about this. Funny, it explains it doesn't dump these grants or any security, which is very important, then it doesn't tell you how to dump it. Thanks, Julie