From: Julie Warden <julie_warden@spamonlyhotmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: pg_dump and roles
Date: Mon, 06 Oct 2008 19:28:44 GMT
Message-ID: <43pke45rb4hpifl6qd1dmkcmg658fil7p0@4ax.com> (raw)
References: <4k6ce45tvg8aqhnol6s8n6s3sn41bdc7q1@4ax.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: julie_warden@spamonlyhotmail.com
Subject: Re: pg_dump and roles
In-Reply-To: <43pke45rb4hpifl6qd1dmkcmg658fil7p0@4ax.com>
References: <4k6ce45tvg8aqhnol6s8n6s3sn41bdc7q1@4ax.com> <43pke45rb4hpifl6qd1dmkcmg658fil7p0@4ax.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox