Обсуждение: roles
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;
SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user;However, How can I synchronize the privileges that are assigned to the database entities i.e. schemas , tables, and views.
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;
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
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
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