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

Hi,

On 15/01/2011 14:21, Craig Ringer wrote:
> On 01/14/2011 03:45 PM, Jensen Somers wrote:
>> Bundling it as part of my application is even better. I didn't knew if
>> that would be possible, but it would solve some of the issues. Mainly
>> data protection. The data that needs to be stored should not be altered
>> by users. If they have access to the database via a root password, which
>> would be the case when using the installer or an existing server they
>> can manipulate the data. Implementing your suggested solution would
>> prevent all that, which makes it a perfect solution.
>
> Not so much.
>
> If the user has the access rights to install your app, they also have
> access rights to modify it or components of it later. Bundling Pg with
> the app gains nothing in security terms, as the user can just modify
> the Pg service or user account, alter pg_hba.conf, directly copy the
> Pg data files, etc etc etc.
>
> If the user requires intervention from a system/network administrator
> to install the app (via a priv elevation prompt, "run as
> administrator" etc) then the admin is not forced to disclose any Pg
> admin access details to the final user, who also lacks the rights to
> override them. In this case bundling Pg with the app gains nothing in
> security terms, as a regular Pg install is already secured against the
> local unpriveleged user.
>
> Bundling Pg will gain you convenience, easier app updates/maintenance
> (if done right), and a lower impact on the user, but it won't help you
> isolate Pg data from the user.
>
> A few other considerations, since you're interested in the security
> side of things:
>
> Even if the user lacks admin rights on the machine, if they have
> physical access to the machine they can get admin rights. A BIOS
> password and locked boot order help a little, but won't stop someone
> who's happy to open the case. Full HDD encryption and "trusted boot"
> _might_ stop them. Even if you store the database its self on a
> different host, the user will be able to break their own machine
> enough to extract the authentication credentials and access the
> database directly.
>
> If preventing the user from accessing/manipulating data directly is a
> really strict requirement you'll need the database to run on a
> separate, physically secure machine, and your app will need to run as
> a database user with only the access rights the user should have. If
> the user shouldn't be able to do it directly, the app shouldn't be
> able to do it via the database. If you can't implement your
> requirements using database-level security you'll need a "middleware"
> tool running on a physically secure host, and you'll need to do all
> database access via that app. It can enforce your business rules about
> who is allowed to see/modify what, when.
>
> --
> Craig Ringer

That's a real bummer.

I am migrating from a binary data format. But it's hard to maintain and
add new features. Most of the operations I need to perform on the data
is list, sort and analyze, which is why I'm interested into using a
database as a back-end solution.
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. 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. But SQL CE
has some limitations which is why I am very interested into using
PostgreSQL as a database service.

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.

- Jensen