Crush Tools dbstream with PostgreSQL

There's a command line library from Google called Crush Tools. It hasn't been updated for a while, but a few commands look useful, like pivot and aggregate.

There's also one called dbstream, which is a perl script to stream to/from a database.

Here's how to run it (on OS X anyways):

Install it (crush-tools, Perl package manager, Perl PostgreSQL driver):

$ brew install crush-tools
$ curl -L https://cpanmin.us | perl - --sudo App::cpanminus
$ sudo cpanm DBD::Pg

And run it:

$ dbstream -de , -ds dbi:Pg:dbname=foo -u alice -p secret -s "select 1,2;"

or insert each record from a file:

$ cut -d, -f1-2 input.csv | dbstream ... -s "insert into tbl values (?,?);"

or look up each record from a file:

$ cut -d, -f1 input.csv | dbstream ... -s "select name from tbl where code=?;"

or update up each record from a file:

$ awk -F, '{print $2","$1}' input.csv | dbstream ... -s "update tbl set name=? where id=?;"

where

-de is the input & output field separator
-ds is a Perl DBI datasource
-u username
-p password
-s SQL

It's here if it's not in your package manager: https://github.com/google/crush-tools

Compiling Multicorn on OpenBSD

Install the following:

python 2.7

gmake

git

cd /tmp

git clone git://github.com/Kozea/Multicorn.git

cd Multicorn

Change the first line of preflight-check.sh so can run it with ksh

sed -i 1s/bash/sh/ preflight-check.sh

(I've created a pull request to change this)

Then

gmake && gmake install

PostgreSQL 9.6 Wish List

First, a huge congratulations and thank you to the PostgreSQL Global Development Group for shipping such great features in 9.5!

I'm teaching myself C (I'm a Java guy) so that I can contribute down the road. 

Here's what I'd love to see in 9.6:

• Okapi BM-25 as the default relevance measure for text search. SQLite has it, so it should be doable for Postgres. 
• Overall, make text search a little more Solr-like (n-grams and such)
• Allow custom, non-C functions (like PLV8) for text search parsing, so I can write my own parser
• Allow parameters for SET ROLE ? and LISTEN ? so it's a bit safer to call them from a web application
• Security cookie for SET ROLE, so that user cannot switch roles without permission. See this post for more info.
• Async LISTEN/NOTIFY in the default JDBC driver.  pgjdbc-ng has it, so it should be doable.

Nice To Have:

• Kill MD5 forever and use bcrypt or scrypt for password hashing
• Raise an exception if an identifier is longer than 63 characters, instead of silently truncating it. This was an actual issue with Drupal Commerce
• Begin SQL:2011 Temporal feature set

UNIX-like row permissions in PostgreSQL

create table foo (
  foo_id int primary key,
  content text,
  owner name,      --username of the owner (or could be a key to another table)
  grp name,        --name of the group
  mode varchar(3)  --unix-style file permission mode (ex 777)
);

create extension pg_trgm;

--lets you use indexes with regex:
create index on foo using gin ( mode gin_trgm_ops );
create index on foo (owner);
create index on foo (grp);

insert into foo values
(1, 'bar', 'neil', 'managers', '764'); --764 being owner read/write/delete, group read/write, world read


create function world_read() returns text language sql strict immutable as $$
  select '[4567]$'::text
$$;

/* and similar functions world_write(), group_delete() etc */

select
*
from foo
where
    mode ~ world_read()
    or group in (?) and mode ~ group_read()
    or owner = ? and mode ~ owner_read();

delete
from foo
where 
    mode ~ world_delete()
    or group in (?) and mode ~ group_delete()
    or owner = ? and mode ~ owner_delete();

Duplicity Backup on OpenBSD 5.8

The duplicity 0.6.26 package seems to be broken for OpenBSD 5.8 (i386). Here's how to make it work.

Get the ports tree and unpack it:

pkg_add wget
cd /tmp
wget http://ftp.openbsd.org/pub/OpenBSD/5.8/ports.tar.gz
cd /usr
tar -zxvf /tmp/ports.tar.gz

(this takes a few minutes)

Compile duplicity:

cd /usr/ports/sysutils/duplicity
make install

(go get some coffee)

Install gpg and generate a key:

pkg_add gnupg
gpg --gen-key

(accept the defaults)


Full backup:

duplicity full $sourceDir file:///$targetDir 

Incremental backup:

duplicity incremental $sourceDir file:///$targetDir 

Restore:

duplicity restore file:///$sourceDir $targetDir 

If you're using S3:

• use s3+http://$bucketName as the target
• Your bucket should be in US Standard Region 
• export AWS_ACCESS_KEY_ID=$yourAWSAccessKeyId
• export AWS_SECRET_ACCESS_KEY=$yourAWSSecretKey

Simple Inbound XSS Filter for Spring Security

This is a simple filter to look for XSS in requests. It throws an exception if it finds one.

It looks at all GET and POST parameter names and values, as well as all header names and values.

Examples:

GET /foo?name=<script>alert('');</script>
(exception)

POST /bar
<script>alert('');</script>=someValue
(exception)

PUT /baz
Accept-Language=<script>alert('')</script>
(exception)

Add a Maven dependency for JSoup:

<dependency>
    <groupId>org.jsoup</groupId>
    <artifactId>jsoup</artifactId>
    <version>1.8.3</version>
</dependency>

SimpleInboundXssFilter.java:

public class SimpleInboundXssFilter extends GenericFilterBean {

    private Cleaner cleaner = new Cleaner(Whitelist.none());

    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {

        HttpServletRequest request = (HttpServletRequest) servletRequest;

        Parser parser = Parser.xmlParser();

        /* GET and POST parameters: */
        Map params = servletRequest.getParameterMap();

        for(Map.Entry entry : params.entrySet()) {
            String key = entry.getKey();

            if(!cleaner.isValid(getFragmentAsDocument(key, parser))) {
                throw new InboundXssException();
            }

            String[] values = entry.getValue();
            for(String value : values) {
                if(!cleaner.isValid(getFragmentAsDocument(value, parser))) {
                    throw new InboundXssException();
                }
            }
        }

        Enumeration headerNames = request.getHeaderNames();
        while(headerNames.hasMoreElements()){
            String key = headerNames.nextElement();
            if(!cleaner.isValid(getFragmentAsDocument(key, parser))) {
                throw new InboundXssException();
            }

            Enumeration values = request.getHeaders(key);
            while(values.hasMoreElements()){
                String value = values.nextElement();
                if(!cleaner.isValid(getFragmentAsDocument(value, parser))) {
                    throw new InboundXssException();
                }
            }

        }

        filterChain.doFilter(servletRequest, servletResponse);
    }

    private Document getFragmentAsDocument(CharSequence value, Parser parser) {
        Document fragment = Jsoup.parse(value.toString(), "", parser);
        Document document = Document.createShell("");
        Iterator nodes = fragment.children().iterator();

        while(nodes.hasNext()) {
            document.body().appendChild((Node)nodes.next());
        }

        return document;
    }

    public class InboundXssException extends RuntimeException{}
}

In applicationContext-security.xml add:

<http>
...
<custom-filter ref="xssFilter" before="FIRST"/>
...

</http>

<beans:bean class="com.example.SimpleInboundXssFilter" id="xssFilter"/>

PG Admin copy & paste to Excel

PG Admin copy & paste to Excel doesn't work well by default, at least on Windows.

Here's how to fix it:

In PG Admin, on the main menu, go to

File > Options... > Query tool > Results grid , and set:

Result copy field separator to Tab

and (optionally) check Copy column names.

In Query window, in Output pane, CTRL+A to select all and CTRL+C to copy

Sample:

PG Admin:

Excel: