pg_dump and roles

From: Julie Warden <Julie_Warden(at)nospam(dot)hotmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_dump and roles
Date: 2008-10-03 13:21:46
Message-ID: 4k6ce45tvg8aqhnol6s8n6s3sn41bdc7q1@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2008-10-03 17:35:50 Re: pg_dump and roles
Previous Message mm2010 2008-10-03 12:38:13 Re: [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit