Fran Fabrizio wrote:
>
> Scenario 2: Same hardware setup as Scenario 1 but instead of
> replication we have a cron'ed perl script or psql script or something
> similar select from one db and insert into the other, and vice versa.
>
> Cons: still have two seperate databases, not real time, seems like a
> hack to me
I pretty much agree with everything Paul Ramsey said in his reply, use a
modified version of Scenario 2 but have a separate app instead of a
database outside the firewall. You can keep the clients real simple then.
> - all 10,000 clients can get a separate Pg user account. performance
> issues? can we then restrict to a certain user/IP combo? can we
> restrict what actions they can take, what tables they can see, or just
> whether or not they have access to the db? does this even help?
Do this in your app. Obviously you can have 10,000 entries in a
user_access table without any difficulty.
> - SSL? is this even possible? The db client on those 10,000 machines
> is going to be a very lightweight C program out of necessity (perl and
> other languages is not supported, these machines are old and often we
> don't have permission to install new languages on them anyway)
Install openSSH and forward a port from the local machine to your
external server. Keeps the client app nice and simple, provides security
(and compression if they're on a phone-line).
I'd probably just use scp to move text-files to/from the server as
required. Leverage standard *nix user permissions to restrict what
files/folders they have access to. You say somewhere that clients only
connect every 15 mins so you'll be batching log messages anyway I presume.
> - the sensitive data fields can be encrypted in some reversible but
> secure fashion when we store them in the database
Not necessary if you are confident enough about your communication app.
> - we can use things like tripwire, etc... to detect any unauthorized
> access to the db server machine
Good idea anyway, but no use for people hacking the database itself.
HTH
- Richard Huxton
