Thread: W3C Specs: Web SQL
Hey All,<br /><br /> I just noticed that an editor at the w3c seems to have hit a wall with browser implementers,<br />as they are all using Sqlite as their backend database, and so there is<br /> no second, independent implementation. (requiredfor the standards process).<br /><br /> At some point, these specs will be used more on the server-side.<br /> Currently,server-side packages are likely to use Sqlite as well.<br /><br /> So, at some point, when the stars align, someonemay drudge through<br /> a few w3c specs and create a contrib for "web sql".<br /><br /> An "enterprise" distributionof Chromium or Firefox would<br /> be better off using PostgreSQL as its back-end, over Sqlite.<br /><br /> So,at some point, when the sales align, someone will have<br /> financial incentive to deploy a "web sql" contrib module.<br/><br /><br /><br /> Here's a set of relevant w3c specs:<br /><br /> Simple async sql sub-set (the spec in trouble):<br/><a href="http://dev.w3.org/html5/webdatabase/">http://dev.w3.org/html5/webdatabase/</a><br /><br /> Typed arraysand casting (standard names)<br /><a href="https://cvs.khronos.org/svn/repos/registry/trunk/public/webgl/doc/spec/TypedArray-spec.html">https://cvs.khronos.org/svn/repos/registry/trunk/public/webgl/doc/spec/TypedArray-spec.html</a><br /><br/> It's fairly close to what's available now in postgres c libs;<br /><br /> COPY style / blob support (just solidifiedin the latest browser releases):<br /><a href="http://www.w3.org/TR/IndexedDB/">http://www.w3.org/TR/FileAPI/</a><br/><a href="http://www.w3.org/TR/2010/WD-file-writer-api-20101026/">http://www.w3.org/TR/2010/WD-file-writer-api-20101026/</a><br /><br/><br /> For C++ fans, Object Oriented Sugar (not implemented / used yet) :<br /><a href="http://www.w3.org/TR/IndexedDB/">http://www.w3.org/TR/IndexedDB/</a><br/><br />
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@commandprompt.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 usesSqlite 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 usesSqlite > 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@commandprompt.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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@fetter.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@fetter.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@commandprompt.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@fetter.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@commandprompt.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/
Alvaro Herrera wrote: > Excerpts from Charles Pritchard's message: >> 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. Personally, I feel somewhat more safe about trusting PostgreSQL on this than JavaScript, Java applets, a Flash plug-in, and cookies -- all of which are enabled in my browser. Sure, I occasionally hit an ill-behaved page and need to xkill my browser. I don't visit that site again. And it really doesn't happen to me very often. Can you can make a case that this proposal is more dangerous than having all the above enabled? -Kevin
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@commandprompt.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@fetter.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