Postgres Pro
Downloads
Home   >   mailing lists

Thread: roles

roles

From
salah jubeh
Date:

 
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 synchronise the roles using these statments

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;
 


 

Re: roles

From
salah jubeh
Date:

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


 

Re: roles

From
"Rob Richardson"
Date:

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@postgresql.org [mailto:pgsql-general-owner@postgresql.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

 

 

Re: roles

From
Chris Travers
Date:
On Fri, Jul 1, 2011 at 5:06 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
>
> 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

I guess it kinda depends on what you are trying to do.

If these are db's on the same db cluster, then no action is necessary
since roles are shared among databases.  If they are on different
clusters and servers, I think your approach will fail to synchronize
passwords if those are required.  I would probably do a pg_dumpall -s
and use grep to pullout the create/alter role statements along with
grants.

Best wishes,
Chris Travers