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.

UUIDs are fine too. What matters is how they're generated.

If you're generating v4 UUIDs server side using the "uuid" NPM module then you're fine as internally it's using crypto.randomBytes(...)[1] with an almost 16-byte random string (UUIDs are 16-bytes but a proper v4 UUID has to override some of the bits to conform to the spec[2]).

If you're rolling your own UUID function or generating them client side then they may not be as random as you think. For example the same uuid NPM module silently uses Math.random()[3] on the client side if it can't find a better alternative. It's fine for something purely local to the one browser but I wouldn't rely on it being unique globally.

[1]: https://github.com/kelektiv/node-uuid/blob/17d443f7d8cfb65a7...

[2]: https://github.com/kelektiv/node-uuid/blob/17d443f7d8cfb65a7...

[3]: https://github.com/kelektiv/node-uuid/blob/17d443f7d8cfb65a7...

Hashing a value from Math.random() with a cryptographic hash (i.e. SHA256) doesn't make it cryptographically random[1].

If you want a random string get one directly via crypto.randomBytes(...)[2]:

    const id = crypto.randomBytes(32).toString('hex');

[1]: https://github.com/bluzi/jsonstore/blob/87af0d3ef6bf11222b98...

[2]: https://nodejs.org/api/crypto.html#crypto_crypto_randombytes...

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?

I played around with it quite a bit in early 2014 and even wrote a plugin for it. It gave me a better understanding of Docker and abstracting things into containers. The internals are all bash scripts so it's pretty easy to follow how it works and modify it.

[1]: https://github.com/sehrope/dokku-logging-supervisord

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

> ... and if streams become a better and better option as response sizes get larger and larger. The streams syntax is nicer looking in any case.

fs.readFile(...) reads the entire contents of the file into memory. For small files that may be acceptable but if you're dealing with anything that could be large it's not going to be very pleasant or even work properly.

I just tried it out on my laptop for a 100MB and a 1GB file. For a 100MB file using streams is about 25% slower. However the sync method failed for the 1GB file:

    Style            100MB       1GB
    =====            =====       ===
    fs.readFileSync  .176s    <failed>
    streams/pipe     .234s      1.25s

Any background on who's doing this or what the motivation is?

The linked site doesn't have much content besides a link to the GitHub repo.

EDIT: Even though it's not explicitly listed in the README or contributor list, the commit history does show a bit of detail: https://github.com/iojs/io.js/commits/v0.12

> If your app needs a relational database then use an in-memory db like hsql or cloud services like Heroku Postgres, RDS, Redis Labs, etc. However one risk with most in-memory databases is that they differ from what is used in production. JPA / Hibernate try to hide this but sometimes bugs crop up due to subtle differences. So it is best to mimic the production services for developers even down to the version of the database.

The idea that you can develop locally or test against a different type of database then what you are (or will be) using in production is silly. I don't just think it "sometimes" is a bad idea, I reject it outright. You should always be using the exact same database (type and version).

For cloud deployments most XaaS providers have cheap or free dev tiers. For local development it's easy to use VMs. For any project that requires external resources (Postgres, Redis, RabbitMQ, etc) I have a VM that gets spun up via Vagrant[1]. Just "vagrant up" and all external resources required by the app should be available.

> So first… Use a build tool. It doesn’t matter if you choose Ant + Ivy, Maven, Gradle, or sbt. Just pick one and use it to automatically pull your dependencies from Maven Central or your own Artifactory / Nexus server. With WebJars you can even manage your JavaScript and CSS library dependencies. Then get fancy by automatically denying SCM check-ins that include Jar files.

Though I agree that it's a good idea to have dependencies externalized, it's not always possible. Some libraries (ex: third party JARs) are not available in public Maven repos and not everyone has a private one setup. In those situations it's fine to check in the JAR into the project itself.

I like the approach outlined by Heroku for dealing with unmanaged dependencies[2]. You declare them normally in your pom.xml but also include the path to a local directory (checked into SCM) with the JAR files. Anybody that clones your repo should be able to build the project with no manual steps. Works great!

[1]: https://www.vagrantup.com/

[2]: https://devcenter.heroku.com/articles/local-maven-dependenci...

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read.

It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically:

    -- NOTE: WITH clause is just to fake a table with data:
    WITH foo AS (
      SELECT 'a' AS name
           , 2 AS quantity
      UNION ALL
      SELECT 'b' AS name
           , 4 AS quantity)
    SELECT t.*
         , x
    FROM foo t
      -- No need to say "LATERAL" here as it's added automatically
      , generate_series(1,quantity) x;
    
    name | quantity | x
    ------+----------+---
    a    |        2 | 1
    a    |        2 | 2
    b    |        4 | 1
    b    |        4 | 2
    b    |        4 | 3
    b    |        4 | 4
    (6 rows)

Most Postgres clients have issues connecting to Amazon Redshift. The wire protocol is the same so basic interactions (ex: connect via psql and run a SELECT) usually works but things get hairy when you start doing more complicated things or even just querying the data dictionary (the basic information_schema is there but Redshift has it's own tables for it's specific features).

My company[1] makes a database client that runs in your web browser that has explicit support for Redshift (as well as Postgres, MySQL, and more). I encourage you to check it out.

[1]: https://www.jackdb.com/

> In this trial, XFINITY Internet Economy Plus customers can choose to enroll in the Flexible-Data Option to receive a $5.00 credit on their monthly bill and reduce their data usage plan from 300 GB to 5 GB. If customers choose this option and use more than 5 GB of data in any given month, they will not receive the $5.00 credit and will be charged an additional $1.00 for each gigabyte of data used over the 5 GB included in the Flexible-Data Option.

What informed consumer would agree to this?!

Streaming Netflix uses about 1GB/hour for "good" quality streams and 2+GB/hour for HD streams. That would make watching an hour a night come out to $30-60/month of bandwidth charges. Multiply accordingly if you watch more or have multiple people streaming simultaneously. Oh and the way it's worded it sounds like you lose the $5 discount if you go over 5GB.

I can see this leading to some serious bill shock for anybody that signs up.

Oracle supported both full refresh and incremental refresh as of at least 10 years ago (maybe longer).

Fast refresh requires creating "MATERIALIZED VIEW LOGS" on the source table(s) and covers most (but not all) aggregations/groupings in the mview SQL. Once it's setup, DML to the source tables gets logged to the mview logs and a "fast" refresh of the mview uses it to incrementally update the mview's data.

>> Seemed like a good idea until it dawned on me that this means the passwords are stored as plaintext.

>There are several ways this can be done without that.

>Easiest is if they store the date of the last password change or otherwise know you haven't changed it. If it's old enough, double the plaintext before handing it to the hashing function.

Not quite. What you'd need to do is halve the user's entered password if it's older than the cutoff date. If the user enters "foobarfoobar" then you'd halve it to "foobar" before hashing and comparing it to what's stored.

If you only have the old hashed password stored then you don't have the hash of the doubled password, nor can you can infer it.

This whole approach is silly of course. They should just force everybody to reset their passwords.

> The old prototype machine had our AWS API access key and secret key. Once the hacker gained access to the keys, he created an IAM user, and generated a key-pair.

Ouch! This is why you must practice the principle of least privilege when provisioning access keys, especially ones that can control your entire infrastructure.

> He was then able to run an instance inside our AWS account using these credentials, and mount one of our backup disks.

Good security practices are like an onion ... it's got many layers and will most likely make you cry.

This is why you should be encrypting your backups. The backup program doesn't even need to be able to read the backups it creates, it only needs to to be able to write them (i.e. the public half of the key).

If you're thinking, "Why does that matter? It can already read the plain-text data it's backing up!" then you're not thinking about history or multiple servers. Each may be able to read the current state of its own file system, but if they only have the public half, they can't read the historical state or each other's backups. The damage would be limited to a single server and only what's live on it at that moment.

> We were able to verify the actions of the hacker using AWS CloudTrail, which confirmed that no other services were compromised, no other machines were booted, and our AMIs and other data stores were not copied.

CloudTrail is awesome and if you're on AWS you really should enable it. It costs peanuts compared to what it provides. In cases like this, where the attacker takes over your entire infrastructure, it may be the only log you have of what happened.

Now this is cool! (and I'm not saying that just because I was thinking about it yesterday[1]).

It's amazing to see how with the multicorn[2] FDW it takes less than 40 lines of python to put this together.

[1]: https://news.ycombinator.com/item?id=8529044

[2]: http://multicorn.org/

This is pretty neat. I'm a big fan of SQL in general and being able to query system stats like this feels pretty natural to me.

A long time back I created something similar to this atop Oracle[1]. It used a Java function calling out to system functions to get similar data sets (I/O usage, memory usage, etc). It was definitely a hack, but a really pleasant one to use.

Be cool to see an foreign data wrapper for PostgreSQL[2] that exposes similar functionality. I'm guessing it'd be pretty easy to put together as you'd only need to expose the data sets themselves as set returning functions. PostgreSQL would handle the rest. Though I guess that would limit it's usefulness to servers that have PG already installed. Having it be separate like this let's you drop it on any server (looks like it's cross platform too!).

[1]: I don't remember exactly when but I think 10g had just been released.

[2]: http://www.postgresql.org/docs/9.3/static/postgres-fdw.html

An interesting solution I've heard[1] to reform both copyright (and possibly patent) law is to require compounding annual fees to maintain them. For example if someone writes a new song or book, we could have a short "free" copyright period. After that, creator (or more accurately, the rights holder) would need to pay an annual fee to maintain their copyright. The fee would increase by some percentage each year.

If the value of the work is greater than the fee, then it'd be in the interests of the rights holder to pay the fee and maintain the copyright, for example to be able to license it out to others. If it's not, then the copyright would expire and the work would enter the public domain.

A high enough interest rate for the compounding would ensure that everything is eventually in the public domain. For works that are repeatedly extended, say Mickey Mouse, the public will receive the fees collected for each of the extensions until it enters the public domain.

This also has the interesting effect of incentivizing the creation of new works as there wouldn't be any fees associated with them for the initial term.

There's obviously a lot more details to handle such as having a central registry, inflation adjustments, differentiating between new works and derived works, and how to handle works created prior to the introduction of a system like this. Still I think it's an interesting proposal.

[1]: I don't remember where exactly but probably here on HN.