Re: PostgreSQL as a triple store

On Tue, Aug 12, 2014 at 8:33 AM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Tue, 12 Aug 2014 16:57:32 +0200
> Jimmy Thrasibule <thrasibule(dot)jimmy(at)gmail(dot)com> wrote:
>
> > > Is there a reason why hstore or json is not an option? That may work
> a lot
> > > better than this approach.
> >
> > I don't want to move away from SQL common features so I can have a
> > test environment using SQLite and deploy on PostgreSQL. This approach
> > looks elegant and simple to me. Using a new table per attribute type
> > or even per attribute for the values, I can easily index them for
> > quick lookups.
>
> Couple of things to keep in mind:
> * SQLLite and PostgreSQL are not 100% compatable. It's cheap and easy to
> set up PostgreSQL for testing/development, so it seems silly (to me) to
> test
> on something that might behave differently than the production environment.
>
> * Any setup where the application can execute DDL is a potential security
> concern. If the code can manipulate tables then a bug in the code can
> allow an accidental or malicious user to quickly and easily destroy data.
>

This is a good point I probably should have mentioned specifically. Making
my solution secure means restricting access to the catalog and catalog to
DDL features to a very small subset of trusted users (ideally not through a
standard application connection). In this case, one might as well restrict
it to those who are already db superusers unless there is a clear need to
broaden it.

BTW, a specific case in point... DDL can't be parameterized since it
doesn't have a query plan. This means you are doing string concatenation
to create your ddl queries. If you aren't careful someone can add an
attribute like:

'; DROP TABLE things; --

In practice this means a great deal of manual review and restriction on
which users can access this feature.

Best Wishes,
Chris Travers

>
> --
> Bill Moran
> I need your help to succeed:
> http://gamesbybill.com
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more