pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump and roles
5+ messages / 5 participants
[nested] [flat]
* pg_dump and roles
@ 2008-10-03 13:21 Julie Warden <Julie_Warden@nospam.hotmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Julie Warden @ 2008-10-03 13:21 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump and roles
@ 2008-10-03 17:35 Alvaro Herrera <alvherre@commandprompt.com>
parent: Julie Warden <Julie_Warden@nospam.hotmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2008-10-03 17:35 UTC (permalink / raw)
To: Julie Warden <Julie_Warden@nospam.hotmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump and roles
@ 2008-10-03 17:44 Peter Steinheuser <peter.steinheuser@enterprisedb.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 5+ messages in thread
From: Peter Steinheuser @ 2008-10-03 17:44 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin
use pg_dumpall -g
On Fri, Oct 3, 2008 at 1:35 PM, Alvaro Herrera
<alvherre@commandprompt.com>wrote:
> 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
>
> --
> 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(R).
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump and roles
@ 2008-10-03 20:12 Yauheni Labko <yyl@chappy.com>
parent: Julie Warden <Julie_Warden@nospam.hotmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Yauheni Labko @ 2008-10-03 20:12 UTC (permalink / raw)
To: Julie Warden <Julie_Warden@nospam.hotmail.com>; +Cc: pgsql-admin
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump and roles
@ 2008-10-06 19:28 Julie Warden <julie_warden@spamonlyhotmail.com>
parent: Julie Warden <Julie_Warden@nospam.hotmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Julie Warden @ 2008-10-06 19:28 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-10-06 19:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-03 13:21 pg_dump and roles Julie Warden <Julie_Warden@nospam.hotmail.com>
2008-10-03 17:35 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-10-03 17:44 ` Peter Steinheuser <peter.steinheuser@enterprisedb.com>
2008-10-03 20:12 ` Yauheni Labko <yyl@chappy.com>
2008-10-06 19:28 ` Julie Warden <julie_warden@spamonlyhotmail.com>
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