Обсуждение: 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

> 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