sehrope

The intended usage is that the client tells the server, "I want to load data from a file /path/to/data.txt on my local filesystem" in a SQL command. As part of the protocol for executing the query the server sends a message to the client to request the contents of /path/to/data.txt. Unfortunately client's don't validate the file request and will send any file (ex: /path/to/secrets.txt) even if there was no legit data request in their command.

This has been an issue with MySQL client drivers for years. I found and fixed the same issue in MariaDB Connector/J (JDBC driver (wire compatible with MySQL databases) in 2015. It rejects LOCAL DATA requests from the server unless the client app preregistered an InputStream (Java interface for generic stream of bytes) as data for the command being executing.

This is one of the many many reasons I love open source database drivers. I was able to find and fix this issue only because I could see the source code. Similar "features" in proprietary databases could go unnoticed for years and even when discovered may not have feature flags to disable them.

> PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command.

I'm pretty sure this is going to be my favorite feature in v11 as it will facilitate conditional DDL for the few remaining things at aren't already allowed in transactions, in particular add values to enums.

Prior to v11 you could create new enums in a transaction but could not add values to an existing one. IIUC how this v11 feature works, you could now have a stored procedure check the current value list of the enum and decide to issue an ALTER TYPE ... ADD VALUE ... at runtime, optionally discarding any concurrency error. All of that could run from a SQL command, no external program needed.

> Prior to PostgreSQL 11, one such feature was using the ALTER TABLE .. ADD COLUMN command where the newly created column had a DEFAULT value that was not NULL. Prior to PostgreSQL 11, when executing aforementioned statement, PostgreSQL would rewrite the whole table, which on larger tables in active systems could cause a cascade of problems. PostgreSQL 11 removes the need to rewrite the table in most cases, and as such running ALTER TABLE .. ADD COLUMN .. DEFAULT .. will execute extremely quickly.

... and this is a close second favorite. Having NOT NULL constraints on columns makes schemas much more pleasant to work with, but for schema migrations that means requiring DEFAULT clause to populate existing data. While there are ways to slowly migrate to a NOT NULL / DEFAULT config for a new column (e.g. add column without constraint, migrate data piecemeal, likely in batches of N records, then enable constraint), having it for free in core without rewriting the table at all is simply awesome.

What's the min version of the Postgres server this can be used with?

I've used WAL-E (the predecessor of this) for backing up Postgres's DB for years and it's been a very pleasant experience. From what I've read so far this looks like it's superior in every way. Lower resource usage, faster operation, and the switch to Go for WAL-G (v.s. Python for WAL-E) means no more mucking with Python versions either.

Great job to everybody that's working on this. I'm looking forward to trying it out.

Finally!

Having dealt with many database drivers over the years, I can say first hand that closed source drivers are the bane of my existence. The only thing worse than running into a deep-in-the-stack bug in a database driver is one that you can't correct, let alone debug properly.

Regardless of your views on closed source software on the server side of things, it's a disservice to your customers to not have open source client side drivers. The secret sauce is always on the server side so it doesn't buy you anything. At best there's (very poor) argument for security through obscurity or you're doing something silly like hiding server side features behind client side feature flags.

Plus the more tech savvy of your users will directly contribute back to improve the driver. This is both for fixing existing bugs and adding features to support existing database server features. In this particular case, I've got a laundry list of things I'd like corrected / added / improved in the SQL Server JDBC driver. Had it been open sourced a few years back, I would have already done them myself.

Unless there's a legal restriction for not doing so, like say not owning the original source, there's no good reason not to open source the client side of things.

Does this allow users to execute custom SQL, in addition to ORM methods, and have it be part of the same transaction?

For me 600 iterations takes about 3ms (I guess my laptop is a bit faster). A decent range to shoot for is .5-1 sec.

Test program:

    crypto = require 'crypto'

    password = 'testing'
    len = 128
    salt = crypto.randomBytes(len)

    iters = Number(process.argv[2] || 600)

    console.log 'Testing iters=%s', iters
    for i in [1..10]
      start = Date.now()
      crypto.pbkdf2Sync password, salt, iters, len
      elapsed = Date.now() - start
      console.log '   Test #%s - %s ms', i, elapsed

Output:

      $ coffee pbkdf2-test.coffee 100000
      Testing iters=100000
         Test #1 - 497 ms
         Test #2 - 510 ms
         Test #3 - 496 ms
         Test #4 - 525 ms
         Test #5 - 510 ms
         Test #6 - 493 ms
         Test #7 - 521 ms
         Test #8 - 518 ms
         Test #9 - 510 ms
         Test #10 - 498 ms

      $ coffee pbkdf2-test.coffee 10000
      Testing iters=10000
         Test #1 - 54 ms
         Test #2 - 50 ms
         Test #3 - 50 ms
         Test #4 - 55 ms
         Test #5 - 51 ms
         Test #6 - 52 ms
         Test #7 - 50 ms
         Test #8 - 49 ms
         Test #9 - 51 ms
         Test #10 - 50 ms

      $ coffee pbkdf2-test.coffee 600
      Testing iters=600
         Test #1 - 3 ms
         Test #2 - 3 ms
         Test #3 - 3 ms
         Test #4 - 3 ms
         Test #5 - 3 ms
         Test #6 - 4 ms
         Test #7 - 3 ms
         Test #8 - 4 ms
         Test #9 - 3 ms
         Test #10 - 3 ms

From the link:

    ITERATIONS = 600
    ...
    crypto.pbkdf2 pwd, salt, ITERATIONS, LEN, (err, hash) ->

That's way too small for the number of iterations. Something like 100K would be a better choice.

Alternatively here's a version that uses bcrypt:

    bcrypt = require 'bcrypt'
    rounds = Number(process.env.BCRYPT_ROUNDS || 12)

    module.exports =
      hash: (password, cb) ->
        bcrypt.hash password, rounds, cb

      compare: (password, hashedPassword, cb) ->
        bcrypt.compare password, hashedPassword, cb