[ckan-dev] Started work on the 'webstore' (a datastore with web API)

[David Raznick]

On Sat, Jul 16, 2011 at 8:50 PM, Friedrich Lindenberg <
friedrich.lindenberg at okfn.org> wrote:

> Hi all,
>
> I did some hacking on this last night:
>
> Its hard to do this with SQLite and the normal Python drivers,
> apparently. While there is a binding that supports the RO flag, this
> in turn would not be compatible with SQLAlchemy which helps us keeping
> the code clean.
>
> What I would propose is using PostgreSQL as a backend and having all
> stores share a single database. Isolation will be done on a schema
> level which is optionally not complete (i.e. users could do SELECT *
> FROM cra.cra2010 cra JOIN "openspending.cofog".cofog cofog1 ON
> cra.cofog1_id = cofog1.id). To do this, we would create schemas on
> demand and a read-only user along with them. Since in PG < 9.0 you
> can't assign SELECT privileges on a per-schema basis, this requires
> some DDL hookery, but see for yourselves: [1]
>

This seems fine and how I would have done it. So hopefully we can fix the
sqlalchemy problems.  I am willing lend a hand if you get stuck on this.

Also please add the following.

alter role <username> set statement_timeout = 10000;

The 10000 is milliseconds, so is 10 second timeout for that user (probably
could set this to even less)   To test this out just try running  "select
10000!".

The way I would like to do this (oneday) would be to do an explain on the
query first.  This gives you the query plan (you can specify it to be in
json or yaml in >=9.0).  From this you get the estimated query cost.  This
way you could stop expensive queries from even starting.


> All of this works when executed on the PSQL prompt but not currently
> via SQLA. Also I'm not entirely sure its a good idea (read: not sure
> how bad it is as an idea). One thing that David Raznik mentioned is
> file system commands like SELECT INTO ..., another concern is simply
> having one giant database that could potentially go AWOL
>

I think I was wrong here, I think the select privileges are pretty strict
and I don't think there should be a way to access the filesytem.  Things
like 'copy to' require a superuser.

Postgres should be able to deal with all the data we throw at it.  It may
get more painful if we let people make their own indexes though.


> Happy for any hints, comments, etc.
>
> I think posgres is a really good choice for this, however monolithic and
scary it is.

- Friedrich
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20110717/7e458f4b/attachment-0001.html>