Re: Install PostgreSQL as part of a desktop application, but how to coop with existing installations?

Hi,

On 17/01/2011 14:49, Craig Ringer wrote:
> On 01/17/2011 06:46 PM, Jensen Somers wrote:
>
>> Once a recording session is completed the data should be locked and not
>> modified anymore. And everything should be local on the user's PC, I
>> can't use an external database server.
>
> Those two requirements are mutually exclusive, unless you're willing
> to qualify and limit one or both of them with something like "the user
> must not have local admin rights". As stated, you've set yourself
> requirements that are impossible to satisfy with ANY database system.
>
> If the user has local admin, then everything is modifiable with
> varying degrees of effort. You can potentially both your requirements
> if the local user can be guanteed NOT have admin rights, by
> configuring privs in the database to permit INSERT but not
> UPDATE/DELETE on the tables of interest, or by using SECURITY DEFINER
> functions to perform limited priveleged tasks (like "archive this
> batch into the read-only-to-normal-users history tables).
>
> No matter what data storage system you use, if your data or the
> credentials that grant unlimited read/write access to that data on a
> remote server are on the local machine and the user has local admin
> rights, your data isn't really read-only. It might be "trickier to
> modify", but that's about it. That might be good enough, depending on
> how strong your requirement for read-only archival is. If it's a
> legal/compliance requirement, you're stuffed, you need non-admin users
> or a remote server + limited database access credentials. If it's just
> to make playing with the history by accident unlikely and make casual
> tampering hard, then maybe it'll be OK to keep the data local even
> with a local admin user. They'll at least have to jump through a few
> hoops to bypass the app and start modifying the data directly.
>
> This applies to Pg, MS SQL Express, MySQL, SQLite, etc. If the user
> has local admin rights, you have no security against them.
>
>> My first idea was to use SQL
>> Compact Edition. It can easily create a file which I can password
>> protect so a user cannot open the file and modify the data.
>
> ... except by brute-force cracking the password, examining your
> program binary to extract and read the password, capturing the
> password using a debugger as your program passes it to the MS SQL
> Compact Edition library calls, using their local admin rights to load
> a malicious DLL that intercepts the calls and logs the password, ...
> etc etc etc.
That's something I know I can't do anything against. But that's the same
problem I face now with my binary data format and so far I have not seen
any user able to modify it. I know from the user forums that a lot of
people tried, but they failed. But I can achieve the same thing by
password protecting the SQL CE or SQLite database file. And that's good
enough for me, I know I cannot get a 100% secure solution, but the one I
can get is good enough for me. Since my application is used by a very
small number of users world wide ( < 10.000) I don't really need to
worry about it being cracked in the same way Adobe or Microsoft needs to
worry.
>
> How secure does it have to be? Against whom? What must it prevent, and
> what things may it let past? Define your requirements in detail, THEN
> start thinking about how to satisfy them.
>
>> I have no problem with a user knowing I use PostgreSQL, but if there's
>> no way I can block him out of the database and prevent him from
>> manipulating the data in any way PostgreSQL is, unfortunately, no longer
>> an option.
>
> There is no way you can lock the user out of ****ANY**** database,
> data file, etc if they have local administrator rights on the
> computer, the data is on that computer, and the credentials used to
> access that data (embedded in your program or otherwise) are also on
> the computer. None. Zero. Nil. All you can do is make it harder for
> them to varying degrees.
I can hide the credentials in my application, that's what I currently
also need to do in order to access the binary data file.

The data that is logged can be uploaded, only if a user desires, to a
web service which process the data and compares it against other user.
What we saw from previous application versions which used an XML based
file format is that a lot of users manipulated their data to get better
results. That was "fixed" by introducing our binary format, but that has
proven to be hard to maintain and work with in general which is my I am
currently looking into database solutions. And I want to provide some
protection, I don't want the data to be wide open to everyone.
>
> Even if they don't have local admin, if they have physical access to
> the computer they can get local admin if they particularly want it.
> Boot an ubuntu CD / USB key, reset the NT password for the local
> admin, and you're done. Easy. Or do the same thing over PXE (network
> boot) by plugging in a laptop and spoofing the DHCP server. Numerous
> other tools can be used instead of a linux boot CD, and there are may
> other ways to backdoor a system than changing the admin password. The
> only way you can stop them is by denying physical access and denying
> local admin rights.
>
> So: How secure does it have to be?
The password protection I can get using SQLite or SQL CE is more than
enough. If I can do the same thing with PostgreSQL it's OK. Creating a
database user for my application is what I planned to do. I am also in
no way interested in the user's computer user or admin rights, I am just
talking about a database user.

But, from your initial reply I understood that a user can simply browse
to my database installation folder (e.g.: C:/ProgramData/MyApp/data),
read out and/or modify a configuration file and he can access the entire
database and modify the data. And that's what I want to prevent.
> (You **might** be able to keep it secure against a non-admin user with
> physical access if you had a system with a "trusted boot" chain using
> the TPM, etc. I doubt you'll be able to require that, though!)
>
> --
> Craig Ringer

- Jensen