Re: pg_dump and roles

On Fri, 03 Oct 2008 13:21:46 GMT, Julie Warden
<Julie_Warden(at)nospam(dot)hotmail(dot)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