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.

sehrope

8 anni fadiscuss

> 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.

sehrope

8 anni fadiscuss

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...

sehrope

8 anni fadiscuss

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...

sehrope

9 anni fadiscuss

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

sehrope

9 anni fadiscuss

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.

sehrope

10 anni fadiscuss

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.

sehrope

11 anni fadiscuss

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

sehrope

11 anni fadiscuss

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

sehrope

12 anni fadiscuss

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

sehrope

12 anni fadiscuss

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

sehrope

12 anni fadiscuss

> ... 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

sehrope

12 anni fadiscuss

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

sehrope

12 anni fadiscuss

> 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...

sehrope

12 anni fadiscuss

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)

sehrope

12 anni fadiscuss

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/

sehrope

12 anni fadiscuss

> 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.

sehrope

12 anni fadiscuss

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.

sehrope

12 anni fadiscuss

>> 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.

sehrope

12 anni fadiscuss

> 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.

Submissions

Postgres password encryption without leaking credentials

How I Write SQL, Part 1: Naming Conventions (2014)

Remote code execution vulnerability in node-postgres

Amazon RDS: Action Required for SSL Users by 23 Mar 2015

Misfortune Cookie crumbles router security: '12 MILLION+' in hijack risk

AT&T demands clarity: Are warrants needed for customer cell-site data?

Verizon: ISPs will sue unless government adopts weaker net neutrality rules

TV monitoring service is fair use, judge rules

A Mobius Strip Track Makes Magnet Hovercrafts Even Cooler

It's official: You can now legally carrier-unlock your mobile in the US

How I Write SQL, Part 1: Naming Conventions

Netflix slow on Verizon or Comcast? A VPN might speed up that video

The Art of Peering: The Peering Playbook

Payments experts assure Senate that swipe-and-sign cards will disappear in 2015

AWS Tips, Tricks, and Techniques

My blog's tech stack: Pelican powered, Dokku deployed

Encrypting Docker containers on a Virtual Server

Publish your Pelican blog on Github pages via Travis-CI

How to set up a private PaaS with Dokku on DigitalOcean

JackDB – Query and analyze any database in the cloud, right in your web browser

comments