FAQ
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;

Search Discussions

  • Salah jubeh at Jul 1, 2011 at 12:06 pm
    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
  • Rob Richardson at Jul 1, 2011 at 12:39 pm
    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
    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
  • Chris Travers at Jul 1, 2011 at 12:49 pm

    On Fri, Jul 1, 2011 at 5:06 AM, salah jubeh 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 1, '11 at 12:00p
activeJul 1, '11 at 12:49p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase