7 ms·

PostgreSQL 9.5 New Features with Examples [pdf]

140 points by snaga 11y ago

y0ghur7_xxx 11y ago
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-highlight-row-level-security/ http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-...
- rectangletangle 11y ago
  I wish they referred to it as UPSERT in the syntax.
- porker 11y ago
  > 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?
  - jerven 11y ago
    It does help this use case, as you can restrict which role's a user/connection can have. i.e. you can't set role to anything you want if you are not a superuser.
  - blackbeard 11y ago
    If someone builds this into a database engine it'd be a big win.
  - masklinn 11y ago
    > 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 11y ago
      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
  - kawera 11y ago
    For small multi-tenant apps, we use one database per customer. It's simple and works pretty well.
  - elchief 11y ago
    by multi-tenant you mean tenant info in table column or by schema, or db?
    - porker 11y ago
      I'm confused by your question, but I think "table column". Think generic SaaS application: one app, one (sharded) database, one set of tables, used by many different companies.
orf 11y ago
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 11y ago
  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)
  - rgbrenner 11y ago
    and when you use alter table unlogged.. that's actually what it does anyway -- it rewrites the entire table (holding an exclusive lock the entire time). So this is really just a convenient syntax.
  - orf 11y ago
    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.
    - 11y ago
jmnicolas 11y ago
Genuine question : why is this on HP's website ? Are they a Postgres contributor or use it massively internally ?
- maxerickson 11y ago
  They have big enterprise software and consulting businesses.
po 11y ago
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/
- anarazel 11y ago
  https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_b... Could be explained a bit better...
- leftnode 11y ago
  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 11y ago
    > "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 11y ago
      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?
tux 11y ago
Official Wiki: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
bwblabs 11y ago
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 11y ago
  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
- d_luaz 11y ago
  Interesting to see excitement over new features which I am not aware of. I would like to encourage more people to share on StackBus (FYI, I built it) on why they use PostgreSQL and what they use it for, to provide more insights to those who are evaluating PostgreSQL. http://www.stackbus.com/ http://www.stackbus.com/
deleted 11y ago
[deleted]
joeseeder 11y ago
honestly who still produces PDF in A4 for content which 99% of a time will be read on a screen ...

140 points by snaga 11y ago

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-highlight-row-level-security/ http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-...

rectangletangle 11y ago
I wish they referred to it as UPSERT in the syntax.
porker 11y ago
> 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?
- jerven 11y ago
  It does help this use case, as you can restrict which role's a user/connection can have. i.e. you can't set role to anything you want if you are not a superuser.
- blackbeard 11y ago
  If someone builds this into a database engine it'd be a big win.
- masklinn 11y ago
  > 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 11y ago
    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
- kawera 11y ago
  For small multi-tenant apps, we use one database per customer. It's simple and works pretty well.
- elchief 11y ago
  by multi-tenant you mean tenant info in table column or by schema, or db?
  - porker 11y ago
    I'm confused by your question, but I think "table column". Think generic SaaS application: one app, one (sharded) database, one set of tables, used by many different companies.

orf 11y ago

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 11y ago
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)
- rgbrenner 11y ago
  and when you use alter table unlogged.. that's actually what it does anyway -- it rewrites the entire table (holding an exclusive lock the entire time). So this is really just a convenient syntax.
- orf 11y ago
  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.
  - 11y ago

jmnicolas 11y ago

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

maxerickson 11y ago
They have big enterprise software and consulting businesses.

po 11y ago

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/

anarazel 11y ago
https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_b... Could be explained a bit better...
leftnode 11y ago
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 11y ago
  > "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 11y ago
    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?

tux 11y ago

Official Wiki: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...

bwblabs 11y ago

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 11y ago
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
d_luaz 11y ago
Interesting to see excitement over new features which I am not aware of. I would like to encourage more people to share on StackBus (FYI, I built it) on why they use PostgreSQL and what they use it for, to provide more insights to those who are evaluating PostgreSQL. http://www.stackbus.com/ http://www.stackbus.com/

deleted 11y ago

[deleted]

joeseeder 11y ago

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