
PostgreSQL 9.5 New Features with Examples [pdf] - snaga
http://h30507.www3.hp.com/hpblogs/attachments/hpblogs/Japan-Enterprise-Topics/124/1/PostgreSQL%209.5%20New%20Features%20English%2020150807-1.pdf
======
y0ghur7_xxx
INSERT ON CONFLICT and Row Level Security are awesome new additions.

If you don't know about RLS I recommend you read up on them[1], as they would
allow you to handle select/update/delete rights on rows directly on the data
itself.

[1][http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-...](http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-
highlight-row-level-security/)

~~~
porker
> Row Level Security

Awesome! Though having read the explanation I can't think of a secure way to
use this in a multi-tenanted app, as once the DB user has permission to
execute "SET ROLE", it's got the same security issues with accessing other
user data as the current "WHERE company_id=3" style of separation? Would it
help this use-case, and does it add security?

~~~
masklinn
> as once the DB user has permission to execute "SET ROLE", it's got the same
> security issues with accessing other user data as the current "WHERE
> company_id=3" style of separation?

DB users can only SET ROLE to roles they belong to. If each tenant has its own
role it isn't possible to switch between them.

    
    
        # CREATE ROLE bob;
        CREATE ROLE
        # CREATE ROLE alice;
        CREATE ROLE
        # SET SESSION AUTHORIZATION bob;
        SET
        > SET ROLE alice;
        ERROR:  permission denied to set role "alice"
    

"SET SESSION AUTHORIZATION <role>" changes the possible SET ROLE targets to
those allowed for <role> (so only the roles it's a member of), whereas SET
ROLE keeps the original possible roleset (which is every role if you SET ROLE
from an admin account), so SET SESSION AUTHORIZATION is a better proxy for
"logged as <role>".

The biggest annoyance is with connection pooling and the inability (to my
knowledge) to prevent RESET SESSION AUTHORIZATION at the DB-level: when
pooling connections you'll want to have a "base" role which is a NOINHERIT
member of all "effective" (tenant) roles, so it can only SET ROLE/SET SESSION
AUTHORIZATION but can't do anything (so it can't be exploited), the first
thing you do when you get a connection from the pool is SSA to the effective
user you want, and the last thing you do when you put the connection back in
the pool is a RSA.

    
    
        # CREATE TABLE foo ( id serial, c1 varchar, c2 varchar );
        # GRANT SELECT (id, c1, c2) ON foo TO public;
        # GRANT UPDATE (c1) ON foo TO bob;
        # GRANT UPDATE (c2) ON foo TO alice;
        # INSERT INTO foo (c1, c2) VALUES ('a', 'b');
        # SELECT * FROM foo;
         id | c1 | c2 
        ----+----+----
          1 | a  | b
        (3 rows)
        # SET SESSION AUTHORIZATION base;
        > UPDATE foo SET c1 = 'd' WHERE id = 1;
        ERROR:  permission denied for relation foo
        > SET SESSION AUTHORIZATION bob;
        > UPDATE foo SET c1 = 'd' WHERE id = 1;
        UPDATE 1
        > SELECT * FROM foo;
         id | c1 | c2 
        ----+----+----
          1 | d  | b
        (3 rows)
    

That'll prevent honest mistakes, though an attacker could still RESET SESSION
AUTHORIZATION then switch the connection to a different role and go to town.
It might be possible to mitigate that at the application level, but that's
less convenient.

~~~
elchief
MS does this with a magic cookie value. [https://msdn.microsoft.com/en-
us/library/ms181362.aspx](https://msdn.microsoft.com/en-
us/library/ms181362.aspx)

It's been discussed on the pg mailing lists but nothing has happened so far

~~~
masklinn
An output parameter seems somewhat inconvenient to use from an application
context, but yes that's a feature which — as the MSDN correctly notes — would
be extremely useful for multiuser with connection pooling environment.

------
bwblabs
Can't wait to use the new JSONB operators of 9.5, e.g.:

    
    
        SELECT '{"k1":"v1"}'::JSONB || '{"k1":"v2","k2":true}'::JSONB

=> {"k1": "v2", "k2": true}

In 9.4 this is the 'best way' I know:

    
    
        SELECT
            ('{' || STRING_AGG(
    	    '"' || COALESCE(j2.key, j1.key) ||
    	    '": ' || TO_JSON(COALESCE(j2.value, j1.value)
            ), ',') || '}')::JSONB
        FROM JSONB_EACH('{"k1":"v1"}') j1
        FULL OUTER JOIN
        (SELECT * FROM JSONB_EACH('{"k1":"v2","k2":true}')) j2 ON j1.key = j2.key

~~~
andrewgleave
The JSONB 9.5 operators have been backported to 9.4 and are available here:

[https://github.com/erthalion/jsonbx](https://github.com/erthalion/jsonbx)

------
orf
The ALTER TABLE UNLOGGED feature is awesome, we've been looking for ways to
speed up our Django test runner and this seems like it would help.

I've been thinking that a "test-only" mode of Postgres would be quite useful -
i.e no persistence/WAL/crash-safety at all, in return for raw speed. My tests
don't need any of this, and the Django test runner is awfully wasteful when it
comes to setting up and tearing down tests.

~~~
pilif
Unlogged tables have existed since 9.1. The only thing that was added now was
a quick way to toggle between modes. Before, you could achieve the he same by
creating a second unlogged table (using 'like x including indexes'), followed
by an 'insert into ... select ...' an the subsequent dropping and renaming
(remember: DDL is transactional in Postgres, so you can do all of this in a
transaction).

Yes, the new toggle is much more convenient, but you do not have to wait for
9.5 if you really want it.

In our case, we converted some tables we use as kind of materialized views the
moment 9.1 came out.

edit: fixed autocorrect wittyness (unclogged => unlogged)

~~~
orf
Thanks for that tip, I will try and integrate it with our tests later and see
if there is much of a speedup (before I was trying to alter Django's CREATE
TABLE statements which is harder than I thought).

I was also suggesting something further than unlogged tables, maybe something
completely in-memory? Good tests are run in isolation and are really just care
about the constraints rather than any form of persistence, combined with the
fact that all data inserted is erased at the end of the test anyway it just
seems a bit wasteful.

~~~
rgbrenner
if it's just for testing, you can use a ram disk to store the data directory.
If you don't want the entire db on the ramdisk, you can create a tablespace
for the ramdisk, and then create tables on it.
[http://www.postgresql.org/docs/9.5/static/manage-ag-
tablespa...](http://www.postgresql.org/docs/9.5/static/manage-ag-
tablespaces.html)

~~~
pjungwir
I'm very tempted to try that, but this warning in the docs you linked sounds
pretty scary:

> Placing a tablespace on a temporary file system like a ramdisk risks the
> reliability of the entire cluster

Are you sure putting your test db on a ramdisk won't destroy the development
db too?

~~~
rgbrenner
The idea is, since it's a development db, after you destroy it, you can
recreate it easily.

But there is a way to do it without destroying the db. When you're finished,
stop postgres, copy the files off of the ramdisk, and then you can destroy the
ramdisk. When you want to start it back up, create the ramdisk, copy the files
back, and then start postgres.

Obviously something like MySQL's memory tables would be a better solution..
but postgres doesn't have anything similar. So this is as close as you can
get.

~~~
pjungwir
Thanks for writing back! I don't love either of those options. Maybe I should
just make a separate _cluster_ for my test database?!: `initdb -D /pgramdisk`
I wonder if anyone has tried this, and what the drawbacks are.

------
jmnicolas
Genuine question : why is this on HP's website ? Are they a Postgres
contributor or use it massively internally ?

~~~
maxerickson
They have big enterprise software and consulting businesses.

------
tux
Official Wiki:
[https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...](https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5)

------
po
What's the easiest way to try out an alpha version of postgres? I can't find a
ubuntu or homebrew pacakged build of it... easiest to build it from source?

 _edit_ : seems like the alpha release is available on the downloads page now:
[http://www.postgresql.org/download/](http://www.postgresql.org/download/)

~~~
leftnode
Building Postgres from source for testing is relatively simple - at least on
Ubuntu / Debian.

    
    
        useradd --home-dir /home/postgres \
            --create-home --shell /bin/bash \
            --user-group postgres
    
        apt-get install -y build-essential libkrb5-dev \
            libxml2 libxml2-dev libxslt1-dev \
            libossp-uuid-dev uuid python-dev \
            libreadline6 libreadline-dev
    
        ./configure --disable-debug --enable-thread-safety \
            --with-gssapi --with-openssl \
            --with-libxml --with-libxslt \
            --with-ossp-uuid --with-python \
            --without-bonjour
    
        make world && make install
        sudo su - postgres -c "/usr/local/pgsql/bin/initdb -D /home/postgres/cluster -E UTF8"

~~~
mslate
> "Relatively simple - at least on Ubuntu / Debian"

I realize we're all professionals here and technical know-how is expected, but
nothing about this is "simple". Brings me PTSD of working w/ academics'
alphabet soup of "open source" simulation code written in C, Perl and
Makefiles.

Build tools have come along away, maybe not with vanilla Postgres installs,
but I guess that's what DBAs are paid to know these days.

~~~
craigching
I honestly don't see what's so hard about the gp's set of instructions. Your
grandma doesn't need to install alpha builds of PostgreSQL (though I shouldn't
assume, there are some technical grandmas out there ;) ), for 99% of people
out there they can use "apt-get" or "yum" or whatever and can wait until the
release.

So let's turn this around, how would you _like_ to see builds become easier?

~~~
mslate
I was cranky earlier, I just want better documentation. Postgres has slowly
gotten better about this over the years.

------
joeseeder
honestly who still produces PDF in A4 for content which 99% of a time will be
read on a screen ...

