Re: W3C Specs: Web SQL

Hey All,

I just noticed that an editor at the w3c seems to have hit a wall with
browser implementers,
as they are all using Sqlite as their backend database, and so there is
no second, independent implementation. (required for the standards process).

At some point, these specs will be used more on the server-side.
Currently, server-side packages are likely to use Sqlite as well.

So, at some point, when the stars align, someone may drudge through
a few w3c specs and create a contrib for "web sql".

An "enterprise" distribution of Chromium or Firefox would
be better off using PostgreSQL as its back-end, over Sqlite.

So, at some point, when the sales align, someone will have
financial incentive to deploy a "web sql" contrib module.

Here's a set of relevant w3c specs:

Simple async sql sub-set (the spec in trouble):
http://dev.w3.org/html5/webdatabase/

Typed arrays and casting (standard names)
https://cvs.khronos.org/svn/repos/registry/trunk/public/webgl/doc/spec/TypedArray-spec.html

It's fairly close to what's available now in postgres c libs;

COPY style / blob support (just solidified in the latest browser releases):
http://www.w3.org/TR/FileAPI/ <http://www.w3.org/TR/IndexedDB/>
http://www.w3.org/TR/2010/WD-file-writer-api-20101026/

For C++ fans, Object Oriented Sugar (not implemented / used yet) :
http://www.w3.org/TR/IndexedDB/

Excerpts from Charles Pritchard's message of sáb nov 06 23:20:13 -0300 2010:

> Simple async sql sub-set (the spec in trouble):
> http://dev.w3.org/html5/webdatabase/

This is insane. This spec allows the server to run arbitrary SQL
commands on the client, AFAICT. That seems like infinite joy for
malicious people running webservers. The more powerful the dialect of
SQL the client implements, the more dangerous it is.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 11/8/2010 7:55 AM, Alvaro Herrera wrote:
> Excerpts from Charles Pritchard's message of sáb nov 06 23:20:13 -0300 2010:
>
>> Simple async sql sub-set (the spec in trouble):
>> http://dev.w3.org/html5/webdatabase/
> This is insane. This spec allows the server to run arbitrary SQL
> commands on the client, AFAICT. That seems like infinite joy for
> malicious people running webservers. The more powerful the dialect of
> SQL the client implements, the more dangerous it is.

Because of a lack of "interested implementers", the spec does not put
forward a standard dialect/subset. It simply uses Sqlite.

Obviously, access should be restricted per the security section: a given
domain may only run commands that modify its own database.

Remember, this is client-side, in respect to "implementations". Each
domain (origin) would behave as its own unique user with its own unique
database (or namespace).
That said, there are a few Server side JS apps around, and they're
certainly more agile than browser vendors: the "openDatabase" command
does not encompass
credentials for multi-user situations in SSJS [again, because it's glued
to the origin, on client-side].

With postgres current security options, I don't see that being a
difficult issue.

On Nov 8, 2010, at 10:36 AM, Charles Pritchard wrote:

> Because of a lack of "interested implementers", the spec does not put forward a standard dialect/subset. It simply uses Sqlite

As de-facto standards go, you could do *much* worse.

David

On 11/08/2010 04:06 PM, David E. Wheeler wrote:
> On Nov 8, 2010, at 10:36 AM, Charles Pritchard wrote:
>
>> Because of a lack of "interested implementers", the spec does not put forward a standard dialect/subset. It simply uses Sqlite
> As de-facto standards go, you could do *much* worse.

I have no idea what's in the standard, but SQLite should be fairly
PostgreSQL compatible. Its author has told me he used PostgreSQL as a
template when implementing his SQL dialect.

cheers

andrew

Excerpts from Andrew Dunstan's message of lun nov 08 18:17:50 -0300 2010:

> I have no idea what's in the standard, but SQLite should be fairly
> PostgreSQL compatible. Its author has told me he used PostgreSQL as a
> template when implementing his SQL dialect.

Starting from the fact that it does not lowercase (or uppercase)
unquoted identifiers, it's not all that compatible.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Nov 08, 2010 at 10:36:16AM -0800, Charles Pritchard wrote:
> On 11/8/2010 7:55 AM, Alvaro Herrera wrote:
> >Excerpts from Charles Pritchard's message of sáb nov 06 23:20:13 -0300 2010:
> >
> >>Simple async sql sub-set (the spec in trouble):
> >>http://dev.w3.org/html5/webdatabase/
> >This is insane. This spec allows the server to run arbitrary SQL
> >commands on the client, AFAICT. That seems like infinite joy for
> >malicious people running webservers. The more powerful the dialect
> >of SQL the client implements, the more dangerous it is.
>
> Because of a lack of "interested implementers", the spec does not
> put forward a standard dialect/subset. It simply uses Sqlite.
>
> Obviously, access should be restricted per the security section: a
> given domain may only run commands that modify its own database.

That's not proof against a DoS of the form:

SELECT * FROM generate_series(1,1000000),generate_series(1,1000000),...;

... and that was *before* CTEs made SQL Turing-complete.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> wrote:

> That's not proof against a DoS

What client API is?

-Kevin

Excerpts from Kevin Grittner's message of lun nov 08 19:30:54 -0300 2010:
> David Fetter <david(at)fetter(dot)org> wrote:
>
> > That's not proof against a DoS
>
> What client API is?

This spec gives free rein into every web user's system to webmasters.
If this isn't terminally dangerous, I don't know what is.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Excerpts from Charles Pritchard's message of lun nov 08 20:25:21 -0300 2010:
> On 11/8/2010 3:03 PM, Alvaro Herrera wrote:
> > Excerpts from Kevin Grittner's message of lun nov 08 19:30:54 -0300 2010:
> >> David Fetter<david(at)fetter(dot)org> wrote:
> >>
> >>> That's not proof against a DoS
> >>
> >> What client API is?
> > This spec gives free rein into every web user's system to webmasters.
> > If this isn't terminally dangerous, I don't know what is.
>
> DoS is more-or-less the responsibility of the host to send up alerts like:
> "This page is hanging, do you want to continue..." or otherwise
> automatically close hanging queries.

I classify that kind of approach to security as "terminally dangerous", yes.

> I don't believe the webmaster is granted free rein:
> Disk quotas are enforced, data is separated per origin,
> hanging processes are up to the implementer, and postgres has plenty of
> settings for that.

The day a privilege escalation is found and some webserver runs
"pg_read_file()" on your browser, will be a sad one indeed.

> The default disk quota per origin is generally 5megs; beyond that,
> additional user interaction is requested.

So 5 megs to a.example.com, 5 megs to b.example.com, and so on? Sounds,
eh, great.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Nov 08, 2010 at 12:55:22PM -0300, Alvaro Herrera wrote:
> Excerpts from Charles Pritchard's message of sáb nov 06 23:20:13 -0300 2010:
>
> > Simple async sql sub-set (the spec in trouble):
> > http://dev.w3.org/html5/webdatabase/
>
> This is insane. This spec allows the server to run arbitrary SQL
> commands on the client, AFAICT. That seems like infinite joy for
> malicious people running webservers. The more powerful the dialect of
> SQL the client implements, the more dangerous it is.

How is this different from the server asking the client to run an
infinite loop in javascript?

--
Sam http://samason.me.uk/

Excerpts from Sam Mason's message of mar nov 09 08:06:12 -0300 2010:
> On Mon, Nov 08, 2010 at 12:55:22PM -0300, Alvaro Herrera wrote:
> > Excerpts from Charles Pritchard's message of sáb nov 06 23:20:13 -0300 2010:
> >
> > > Simple async sql sub-set (the spec in trouble):
> > > http://dev.w3.org/html5/webdatabase/
> >
> > This is insane. This spec allows the server to run arbitrary SQL
> > commands on the client, AFAICT. That seems like infinite joy for
> > malicious people running webservers. The more powerful the dialect of
> > SQL the client implements, the more dangerous it is.
>
> How is this different from the server asking the client to run an
> infinite loop in javascript?

So we already failed :-) It seems that being able to kill processes is
seen as "good enough" ... well, I guess I just don't visit many
malicious sites.

And this makes me think that SQLite is indeed the right tool for the job
here, and not PostgreSQL. If someone intrudes, it's going to be in the
same process running the web browser, not in some server running under
another user identity in the machine. That seems like a feature to me,
not a bug.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 11/09/2010 09:59 AM, Alvaro Herrera wrote:
> And this makes me think that SQLite is indeed the right tool for the job
> here, and not PostgreSQL. If someone intrudes, it's going to be in the
> same process running the web browser, not in some server running under
> another user identity in the machine. That seems like a feature to me,
> not a bug.
>

Right. Then it has some chance to be run in a sandbox. This doesn't
strike me at all as a good fit for Postgres.

cheers

andrew

On 11/8/2010 4:47 PM, Alvaro Herrera wrote:
> Excerpts from Charles Pritchard's message of lun nov 08 20:25:21 -0300 2010:
>> On 11/8/2010 3:03 PM, Alvaro Herrera wrote:
>>> Excerpts from Kevin Grittner's message of lun nov 08 19:30:54 -0300 2010:
>>>> David Fetter<david(at)fetter(dot)org> wrote:
>>>>> That's not proof against a DoS
>>>> What client API is?
>>> This spec gives free rein into every web user's system to webmasters.
>>> If this isn't terminally dangerous, I don't know what is.
>> DoS is more-or-less the responsibility of the host to send up alerts like:
>> "This page is hanging, do you want to continue..." or otherwise
>> automatically close hanging queries.
> I classify that kind of approach to security as "terminally dangerous", yes.
>
>> I don't believe the webmaster is granted free rein:
>> Disk quotas are enforced, data is separated per origin,
>> hanging processes are up to the implementer, and postgres has plenty of
>> settings for that.
> The day a privilege escalation is found and some webserver runs
> "pg_read_file()" on your browser, will be a sad one indeed.
>
>> The default disk quota per origin is generally 5megs; beyond that,
>> additional user interaction is requested.
> So 5 megs to a.example.com, 5 megs to b.example.com, and so on? Sounds,
> eh, great.
>

I don't think it's fair to assume a privilege escalation will be found:
using that argument, no software should ever run on a client/server.

That said, NaCl and PNaCl are under active development and I've no doubt
that Postgres could be compiled by the tool set in the future.

http://code.google.com/p/nativeclient/

Still, that's a diversion from the topic: Postgres can run on workstations,
with an audience of browser-oriented implementations.

Postgres is more stable than Sqlite for "enterprise-level" activity,
hardened/enterprise
browser distributions would choose Postgres over Sqlite for Web SQL
implementations.

I don't think it's fair to assume a privilege escalation will be found:
using that argument, no software should ever run on a client/server.

That said, NaCl and PNaCl are under active development and I've no doubt
that Postgres could be compiled by the tool set in the future.

http://code.google.com/p/nativeclient/

Still, that's a diversion from the topic: Postgres can run on workstations,
with an audience of browser-oriented implementations.

Postgres is more stable than Sqlite for "enterprise-level" activity,
hardened/enterprise
browser distributions would choose Postgres over Sqlite for Web SQL
implementations.

And as for the quota issues: that's really up to the browser vendor.
It's completely out of spec here.
And it's how the web currently works for hundreds of millions of users:
it's not introducing a security issue,
as it reflects the current state of security.

On Tue, Nov 9, 2010 at 9:14 PM, Charles Pritchard wrote:
> Postgres is more stable than Sqlite for "enterprise-level" activity,
> hardened/enterprise
> browser distributions would choose Postgres over Sqlite for Web SQL
> implementations.

I find that very unlikely. Web SQL is to be an upgrade from cookies as
the client storage mechanism, it is not meant to be store a few TB in
GIS data. Implementors will choose based on much more practical
concerns such as portability (SQLite is not just available for Android
and iOS, it is included), filesystem layout (put the databases in one
folder for each domain just like Flash lays out its offline storage),
embeddability (5 processes just to start a DB), recovery speed (when a
mobile browser gets pushed from RAM and later fear back in, it has to
replay a 16 MB WAL file) and even just convenience (how many browsers
already use SQLite for bookmark storage?) will weight far heavier then
some perceived enterprise readiness,

Jochem

--
Jochem van Dieten
http://jochem.vandieten.net/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Nov 09, 2010 at 12:14:06PM -0800, Charles Pritchard wrote:

[...]

> as it reflects the current state of security.

Which is... well, I haven't a word for *that*.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFM2lkYBcgs9XrR2kYRAnt7AJ4jI8qIz6BLlKnMXnj7h1AeWfXBcACfTOWI
8aMdXz0Y2CSGeFJA6WBxPnA=
=MQ5R
-----END PGP SIGNATURE-----

Revisiting my original post:
http://archives.postgresql.org/pgsql-hackers/2010-11/msg00318.php

The "Web SQL" spec is still in its abandoned state.
Mozilla has stated that they do not want to support the API,
though they do allow extensions to send calls to sqlite directly.

Many posters responding to my OP, noted that exposing "SQL" directly,
even with permissions, is not something they're comfortable with.

IndexedDB has gained acceptance across Mozilla, WebKit and Microsoft.
SQL is not exposed directly. It's a simple system.

IndexedDB is currently implemented in WebKit and Mozilla browsers on
using the SQLite library. MS recently implemented a .Net prototype.

I'm going to compile libpq as a browser extension to prototype indexedb
with postgres, then work on patches to WebKit to develop a libpq flag
[default: false] for webkit build scripts.

I will post back when I've got something to demonstrate (hoping to get
to it in a few months).

-Charles