From: | "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com> |
---|---|
To: | "salah jubeh" <s_jubeh(at)yahoo(dot)com>, "pgsql" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: roles |
Date: | 2011-07-01 12:39:13 |
Message-ID: | 04A6DB42D2BA534FAC77B90562A6A03D018A67EE@server.rad-con.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
As I understand things, roles are not specific to databases. In
PGAdmin, when I connect to a server, I see five collections of objects:
databases, tablespaces, jobs, group roles, and login roles. Roles are
separate from databases. So, for a given server, login and group roles
apply to all databases on that server.
Roles can be exported using pg_dump's "global" option, which I think is
"-g". Check the help for pg_dump.
Good luck!
RobR, whose advice may well be worth what you have paid for it.
________________________________
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of salah jubeh
Sent: Friday, July 01, 2011 8:07 AM
To: pgsql
Subject: Re: [GENERAL] roles
Hello,
I have two databases, I need to insure that both databases has the same
roles. tables, schemas, views must have the same permissions and
privileges. you can say and Identical clones. I can synchronize the
roles using these statements
SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user;
SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members
a JOIN pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid =
g.usesysid;
SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE
usesuper;
However, How can I synchronize the privileges that are assigned to the
database entities i.e. schemas , tables, and views.
If that solution is not possible, how can I create an identical clone of
my database including roles. when I use pg_restore normally the owner of
the tables are changed and you need to recreate the roles in advance
Regards
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Travers | 2011-07-01 12:49:35 | Re: roles |
Previous Message | salah jubeh | 2011-07-01 12:06:49 | Re: roles |