role replication and/or synchronization between clusters

From: F(dot)Sluiter <fsluiter(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: role replication and/or synchronization between clusters
Date: 2007-04-25 00:05:06
Message-ID: 90bb279f0704241705h3af81876se5b322d17178a008@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Dear all,

I have to replicate/synchornize user accounts between clusters.
Any change in roles (goups and logins and membership in groups) have
to be replicated to all clusters. Highly preferable it would be a
multimaster solution, allthough I can have one supermaster (so changes
in the client can pass through that master to other clients).
Ordinary data does not have to be replicated, only the accounts and
group memership, therefore a fullblown replication solution might be
over the top, and slony does not support multimaster yet i think.

I first considered LDAP/PAM, but it turns out roles have to exist in
the clusters before one can use that method (or am I wrong here?).

I am considering triggers on the catalog tables pgauthid and
pg_auth_members to store changes in these tables and synchronize the
"supermaster" based on those.
Maybe a better solution would be to refer all changes first to the
supermaster and replicate them back to the clients. I would have to
track the changes in the whole system either through timestamps added
to each record, or bookkeeping the change on each client on the
supermaster.

Because the systems are physically distributed it would be a pain to
have admin_users -who are allowed admin rights on group roles- to
login to a separate cluster each time they want to grant rights to
that role. So i'd like them to be able to change locally and replicate
that change through the whole system (currently 20 databases with
close to 2000 (100 group admins) users of the system).

Any thoughts/suggestions/solutions are highly appreciated.

Regards,

Floris Sluiter
University of Amsterdam

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Eisentraut 2007-04-25 06:45:06 Re: Help me to decrypt password
Previous Message Alexander B. 2007-04-24 20:12:53 Tool to analyze statement sql