Re: roles

["Rob Richardson" <Rob.Richardson@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of salah jubeh
Sent: Friday, July 01, 2011 8:07 AM
To: pgsql
Subject: Re: 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