On 1/2/11 12:35 AM, Heikki Linnakangas wrote: > Very likely. A synchronous standby can bring the master to a halt, while > an asynchronous one is rather harmless. If I were a DBA, and the data > wasn't very sensitive, I would liberally hand out async privileges to my > colleagues to set up reporting standbys, test servers etc. But I would > *not* give them synchronous privileges, because sooner or later one > would go "hmm, I wonder what happens if I make this synchronous", or > haphazardly copy the config file from a synchronous standby. That would > either bring down the master, or act as a "fake" standby, acknowledging > commits before they're flushed to the real synchronous standby. Either > one would be bad.
That's not "very likely". Shops with enough DBAs to actually have a heirarchy of database access are rare indeed; the average business has trouble employing *one* fulltime DBA. Also, you're mistaken if you think that DBA's wouldn't restrict the ability of development users to set up asynch connections; those have a significant impact on the production server as well. However, the possibility of *accidental* DOS is a bit more likely, per accidentally using the wrong config file. I don't think you can guard against that with permissions, though; if a DBA is copying the config file from a synchronous server accidentally, presumably he's copying the .pgpass file as well, and then the new server is identical to the legit synch rep servers as far as the master is concerned. So, again, I don't agree that a separate synchrep permission is useful, or warranted. However, your arguments *do* make me backpedal on the issue of having a list of synch rep roles on the master. I can easily imagine a DBA needing to rapidly disable synch rep if replication is failing, without taking the time to log in to several separate standbys, one or more of which might be improperly configured and inaccessible. I can't think of a simpler way to do that than having a synch_rep_roles configuration on the master. That would also handle control issues for the senior DBA, since you'd need superuser access to the master to modify it. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
