Re: Postgresql replication - Mailing list pgsql-general

William Yu wrote:

> Chris Travers wrote:
>
>> 1)  Efficiency of network throughput
>> 2)  Tolerance to attempts at repeat transactions before replication
>> (emptying an account multiple times)
>> 3)  Availability of a transaction.
>
>
> We ended up having to give up #1. It's possible to have our
> transactions routed to multiple servers before it becomes a final
> transaction. User1 might request a payment on ServerA. User2 then
> marks the payment as approved on ServerB. ServerC is authoritative and
> checks the bank/budget balances before posting as final. After each of
> these steps requires replication of the latest changes to all other
> servers. (In theory, the first 2 steps only require replication to the
> authoritative server but we do so anyways so all servers can act as
> backups for each other -- pending transactions still need to be
> restored in case of total DB failure.)

Ok.  I see where you are going with this.

It is an interesting problem.  Multimaster Async Replication will give
you a problem in that it allows the attack you are describing due to the
asynchronous nature of the replication.  If I were trying to prevent
this sort of attack, I would try to build into this some sort of
"account authority" which can manage these transactions.  Origionally I
was thinking of the home server as the obvious place to start if it is
available.  But if it is not, then you would need some infrastructure to
track attempted withdrawals and handle them appropriately.  Such servers
could cache requests and if they see duplicates or many requests coming
from many servers on the same account could flag that.  One option might
be to have a rotational authority (i.e. home-server, then next, then
next) in a ring so that an unavailable server still allows reasonable
precautions to be held against emptying the account many times.

Basically, if the servers aren't talking to eachother at the time of the
transaction, then they are not going to know about duplicates.  You have
to have some setup beyond your replication to handle this.  Otherwise
you will have some issues with delays causing the security risks you
deem unacceptable.

Your question seems to be "How do I set up multimaster async replication
such that a person cannot empty his account on each server" and the
answer is that this is an inherent limitation of multimaster async
replication.   This also means that you will have to have some sort of
other verification process for such transactions beyond what is locally
available on the replicants.

> There's definitely a delay in terms of getting from point A to point
> Z; duplicate servers. But there's guaranteed financial integrity,
> users can connect to any server the load balancer picks and no server
> requires any other server/connection to be up for individual user
> tranactions to occur.

The delay will by definition defeat any guarantee of financial integrity
if you are allowing read-write operations to the replica without
checking with some sort of central authority.  At very least, the
central authority should look for suspicious patterns.  Again, it may be
possible to do some failover here, but I don't think you can do without
*some* sort of centralized control.

(Note, here load balancing is handled by the distribution of accounts.
A down server simply means that the next server in the ring will take
over its remote verification role).

This doesn;t make the security issue go away, but it may reduce it to an
acceptable level. I.e. it is still possible for duplicates to be
submitted just before and after a home server goes down, but this is a
lot better than being able to have one transaction repeated on each
server and then dealing with the massively overdrawn account.

Best Wishes,
Chris Travers
Metatron Technology Consulting