Les mises à jour de sécurité 9.5.4, 9.4.9, 9.3.14, 9.2.18 et 9.1.23 ont été publiées. Mettez à jour dès que possible ! https://www.postgresql.org/about/news/1688/

PostgreSQL 9.6 Beta 4 disponible. À vos tests ! https://www.postgresql.org/docs/devel/static/release-9-6.html

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en août

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA. La version originale se trouve à l'adresse suivante : http://www.postgresql.org/message-id/20160814214512.GB10448@fetter.org

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

Correctifs appliqués

  • Fix TOAST access failure in RETURNING queries. Discussion of commit 3e2f3c2e4 exposed a problem that is of longer standing: since we don't detoast data while sticking it into a portal's holdStore for PORTAL_ONE_RETURNING and PORTAL_UTIL_SELECT queries, and we release the query's snapshot as soon as we're done loading the holdStore, later readout of the holdStore can do TOAST fetches against data that can no longer be seen by any of the session's live snapshots. This means that a concurrent VACUUM could remove the TOAST data before we can fetch it. Commit 3e2f3c2e4 exposed the problem by showing that sometimes we had *no* live snapshots while fetching TOAST data, but we'd be at risk anyway. I believe this code was all right when written, because our management of a session's exposed xmin was such that the TOAST references were safe until end of transaction. But that's no longer true now that we can advance or clear our PGXACT.xmin intra-transaction. To fix, copy the query's snapshot during FillPortalStore() and save it in the Portal; release it only when the portal is dropped. This essentially implements a policy that we must hold a relevant snapshot whenever we access potentially-toasted data. We had already come to that conclusion in other places, cf commits 08e261cbc94ce9a7 and ec543db77b6b72f2. I'd have liked to add a regression test case for this, but I didn't see a way to make one that's not unreasonably bloated; it seems to require returning a toasted value to the client, and those will be big. In passing, improve PortalRunUtility() so that it positively verifies that its ending PopActiveSnapshot() call will pop the expected snapshot, removing a rather shaky assumption about which utility commands might do their own PopActiveSnapshot(). There's no known bug here, but now that we're actively referencing the snapshot it's almost free to make this code a bit more bulletproof. We might want to consider back-patching something like this into older branches, but it would be prudent to let it prove itself more in HEAD beforehand. Discussion: <87vazemeda.fsf@credativ.de> http://git.postgresql.org/pg/commitdiff/9ee1cf04ab6bcefe03a11837b53f29ca9dc24c7a
  • Fix crash when pg_get_viewdef_name_ext() is passed a non-view relation. Oversight in commit 976b24fb4. Andreas Seltenreich Report: <87y448l3ag.fsf@credativ.de> http://git.postgresql.org/pg/commitdiff/8a8c6b53810026641a1e12f60f873a7bd3cea5e3
  • Fix misestimation of n_distinct for a nearly-unique column with many nulls. If ANALYZE found no repeated non-null entries in its sample, it set the column's stadistinct value to -1.0, intending to indicate that the entries are all distinct. But what this value actually means is that the number of distinct values is 100% of the table's rowcount, and thus it was overestimating the number of distinct values by however many nulls there are. This could lead to very poor selectivity estimates, as for example in a recent report from Andreas Joseph Krogh. We should discount the stadistinct value by whatever we've estimated the nulls fraction to be. (That is what will happen if we choose to use a negative stadistinct for a column that does have repeated entries, so this code path was just inconsistent.) In addition to fixing the stadistinct entries stored by several different ANALYZE code paths, adjust the logic where get_variable_numdistinct() forces an "all distinct" estimate on the basis of finding a relevant unique index. Unique indexes don't reject nulls, so there's no reason to assume that the null fraction doesn't apply. Back-patch to all supported branches. Back-patching is a bit of a judgment call, but this problem seems to affect only a few users (else we'd have identified it long ago), and it's bad enough when it does happen that destabilizing plan choices in a worse direction seems unlikely. Patch by me, with documentation wording suggested by Dean Rasheed Report: <VisenaEmail.26.df42f82acae38a58.156463942b8@tc7-visena> Discussion: <16143.1470350371@sss.pgh.pa.us> http://git.postgresql.org/pg/commitdiff/95bee941be4c009ebbc29162a0dc9664f40de12f
  • Release notes for 9.5.4, 9.4.9, 9.3.14, 9.2.18, 9.1.23. http://git.postgresql.org/pg/commitdiff/19322c0a785f656b82a780c3db44920dcc9bd658
  • Update 9.6 release notes through today. http://git.postgresql.org/pg/commitdiff/be7f7ee5ea73626c037600b515087e8f98038140
  • Update 9.6 release notes through today. http://git.postgresql.org/pg/commitdiff/de4b3ea16d8b053e95eb9d862075b0de123e45b9
  • Stamp 9.6beta4. http://git.postgresql.org/pg/commitdiff/67c08c0d704a5f828492642bf9d133cbb2af3661
  • Doc: clarify description of CREATE/ALTER FUNCTION ... SET FROM CURRENT. Per discussion with David Johnston. http://git.postgresql.org/pg/commitdiff/e775d35317590793863327fedcf3737c3ab838d4
  • Doc: write some for adminpack. Previous contents of adminpack.sgml were rather far short of project norms. Not to mention being outright wrong about the signature of pg_file_read(). http://git.postgresql.org/pg/commitdiff/ff2fd6b06ac00a3c5d451063e0a87dca6156db4e
  • Fix busted Assert for CREATE MATVIEW ... WITH NO DATA. Commit 874fe3aea changed the command tag returned for CREATE MATVIEW/CREATE TABLE AS ... WITH NO DATA, but missed that there was code in spi.c that expected the command tag to always be "SELECT". Fortunately, the consequence was only an Assert failure, so this oversight should have no impact in production builds. Since this code path was evidently un-exercised, add a regression test. Per report from Shivam Saxena. Back-patch to 9.3, like the previous commit. Michael Paquier Report: <97218716-480B-4527-B5CD-D08D798A0C7B@dresources.com> http://git.postgresql.org/pg/commitdiff/0f249fe5f5cb3c83fd8e05743740b35ff5d34196
  • Trivial cosmetic cleanup in bloom/blutils.c. Don't spell "InvalidOid" as "0". Initialize method fields in the same order as amapi.h declares them (and every other AM handler initializes them). http://git.postgresql.org/pg/commitdiff/e3049285a3b8790e26e584fdf1ca31ea2e2eb4bc
  • Last-minute updates for release notes. Security: CVE-2016-5423, CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/9b8271c5a655ef1c35141b266d5039da8d3b2337
  • Fix two errors with nested CASE/WHEN constructs. ExecEvalCase() tried to save a cycle or two by passing &econtext->caseValue_isNull as the isNull argument to its sub-evaluation of the CASE value expression. If that subexpression itself contained a CASE, then *isNull was an alias for econtext->caseValue_isNull within the recursive call of ExecEvalCase(), leading to confusion about whether the inner call's caseValue was null or not. In the worst case this could lead to a core dump due to dereferencing a null pointer. Fix by not assigning to the global variable until control comes back from the subexpression. Also, avoid using the passed-in isNull pointer transiently for evaluation of WHEN expressions. (Either one of these changes would have been sufficient to fix the known misbehavior, but it's clear now that each of these choices was in itself dangerous coding practice and best avoided. There do not seem to be any similar hazards elsewhere in execQual.c.) Also, it was possible for inlining of a SQL function that implements the equality operator used for a CASE comparison to result in one CASE expression's CaseTestExpr node being inserted inside another CASE expression. This would certainly result in wrong answers since the improperly nested CaseTestExpr would be caused to return the inner CASE's comparison value not the outer's. If the CASE values were of different data types, a crash might result; moreover such situations could be abused to allow disclosure of portions of server memory. To fix, teach inline_function to check for "bare" CaseTestExpr nodes in the arguments of a function to be inlined, and avoid inlining if there are any. Heikki Linnakangas, Michael Paquier, Tom Lane Report: https://github.com/greenplum-db/gpdb/pull/327 Report: <4DDCEEB8.50602@enterprisedb.com> Security: CVE-2016-5423 http://git.postgresql.org/pg/commitdiff/f0c7b789ab12fbc8248b671c7882dd96ac932ef4
  • Fix inappropriate printing of never-measured times in EXPLAIN. EXPLAIN (ANALYZE, TIMING OFF) would print an elapsed time of zero for a trigger function, because no measurement has been taken but it printed the field anyway. This isn't what EXPLAIN does elsewhere, so suppress it. In the same vein, EXPLAIN (ANALYZE, BUFFERS) with non-text output format would print buffer I/O timing numbers even when no measurement has been taken because track_io_timing is off. That seems not per policy, either, so change it. Back-patch to 9.2 where these features were introduced. Maksim Milyutin Discussion: <081c0540-ecaa-bd29-3fd2-6358f3b359a9@postgrespro.ru> http://git.postgresql.org/pg/commitdiff/4b234fd8bf21cd6f5ff44f1f1c613bf40860998d
  • Doc: clarify that DROP ... CASCADE is recursive. Apparently that's not obvious to everybody, so let's belabor the point. In passing, document that DROP POLICY has CASCADE/RESTRICT options (which it does, per gram.y) but they do nothing (I assume, anyway). Also update some long-obsolete commentary in gram.y. Discussion: <20160805104837.1412.84915@wrigleys.postgresql.org> http://git.postgresql.org/pg/commitdiff/499787819309293f3d2cd7219aee334a0e7d5069
  • Add SQL-accessible functions for inspecting index AM properties. Per discussion, we should provide such functions to replace the lost ability to discover AM properties by inspecting pg_am (cf commit 65c5fcd35). The added functionality is also meant to displace any code that was looking directly at pg_index.indoption, since we'd rather not believe that the bit meanings in that field are part of any client API contract. As future-proofing, define the SQL API to not assume that properties that are currently AM-wide or index-wide will remain so unless they logically must be; instead, expose them only when inquiring about a specific index or even specific index column. Also provide the ability for an index AM to override the behavior. In passing, document pg_am.amtype, overlooked in commit 473b93287. Andrew Gierth, with kibitzing by me and others Discussion: <87mvl5on7n.fsf@news-spur.riddles.org.uk> http://git.postgresql.org/pg/commitdiff/ed0097e4f9e6b1227935e01fa67f12a238b66064
  • Fix assorted bugs in contrib/bloom. In blinsert(), cope with the possibility that a page we pull from the notFullPage list is marked BLOOM_DELETED. This could happen if VACUUM recently marked it deleted but hasn't (yet) updated the metapage. We can re-use such a page safely, but we *must* reinitialize it so that it's no longer marked deleted. Fix blvacuum() so that it updates the notFullPage list even if it's going to update it to empty. The previous "optimization" of skipping the update seems pretty dubious, since it means that the next blinsert() will uselessly visit whatever pages we left in the list. Uniformly treat PageIsNew pages the same as deleted pages. This should allow proper recovery if a crash occurs just after relation extension. Properly use vacuum_delay_point, not assorted ad-hoc CHECK_FOR_INTERRUPTS calls, in the blvacuum() main loop. Fix broken tuple-counting logic: blvacuum.c counted the number of live index tuples over again in each scan, leading to VACUUM VERBOSE reporting some multiple of the actual number of surviving index tuples after any vacuum that removed any tuples (since they'd be counted in blvacuum, maybe more than once, and then again in blvacuumcleanup, without ever zeroing the counter). It's sufficient to count them in blvacuumcleanup. stats->estimated_count is a boolean, not a counter, and we don't want to set it true, so don't add tuple counts to it. Add a couple of Asserts that we don't overrun available space on a bloom page. I don't think there's any bug there today, but the way the FreeBlockNumberArray size calculation is set up is scarily fragile, and BloomPageGetFreeSpace isn't much better. The Asserts should help catch any future mistakes. Per investigation of a report from Jeff Janes. I think the first item above may explain his report; the other changes were things I noticed while casting about for an explanation. Report: <CAMkU=1xEUuBphDwDmB1WjN4+td4kpnEniFaTBxnk1xzHCw8_OQ@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/d6c9e05cb7db64239887fac65b243229594f331d
  • Remove bogus dependencies on NUMERIC_MAX_PRECISION. NUMERIC_MAX_PRECISION is a purely arbitrary constraint on the precision and scale you can write in a numeric typmod. It might once have had something to do with the allowed range of a typmod-less numeric value, but at least since 9.1 we've allowed, and documented that we allowed, any value that would physically fit in the numeric storage format; which is something over 100000 decimal digits, not 1000. Hence, get rid of numeric_in()'s use of NUMERIC_MAX_PRECISION as a limit on the allowed range of the exponent in scientific-format input. That was especially silly in view of the fact that you can enter larger numbers as long as you don't use 'e' to do it. Just constrain the value enough to avoid localized overflow, and let make_result be the final arbiter of what is too large. Likewise adjust ecpg's equivalent of this code. Also get rid of numeric_recv()'s use of NUMERIC_MAX_PRECISION to limit the number of base-NBASE digits it would accept. That created a dump/restore hazard for binary COPY without doing anything useful; the wire-format limit on number of digits (65535) is about as tight as we would want. In HEAD, also get rid of pg_size_bytes()'s unnecessary intimacy with what the numeric range limit is. That code doesn't exist in the back branches. Per gripe from Aravind Kumar. Back-patch to all supported branches, since they all contain the documentation claim about allowed range of NUMERIC (cf commit cabf5d84b). Discussion: <2895.1471195721@sss.pgh.pa.us> http://git.postgresql.org/pg/commitdiff/9389fbd0385776adf3252eb8cfe6e37a640fdff4

Peter Eisentraut pushed:

Bruce Momjian pushed:

Noah Misch pushed:

  • Promote pg_dumpall shell/connstr quoting functions to src/fe_utils. Rename these newly-extern functions with terms more typical of their new neighbors. No functional changes; a subsequent commit will use them in more places. Back-patch to 9.1 (all supported versions). Back branches lack src/fe_utils, so instead rename the functions in place; the subsequent commit will copy them into the other programs using them. Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/41f18f021a0882eccbeca62e2ed4b66c6b96e9c9
  • Sort out paired double quotes in \connect, \password and \crosstabview. In arguments, these meta-commands wrongly treated each pair as closing the double quoted string. Make the behavior match the documentation. This is a compatibility break, but I more expect to find software with untested reliance on the documented behavior than software reliant on today's behavior. Back-patch to 9.1 (all supported versions). Reviewed by Tom Lane and Peter Eisentraut. Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/984e5beb38a7c79a5a9243865d9598c405df17f6
  • Reject, in pg_dumpall, names containing CR or LF. These characters prematurely terminate Windows shell command processing, causing the shell to execute a prefix of the intended command. The chief alternative to rejecting these characters was to bypass the Windows shell with CreateProcess(), but the ability to use such names has little value. Back-patch to 9.1 (all supported versions). This change formally revokes support for these characters in database names and roles names. Don't document this; the error message is self-explanatory, and too few users would benefit. A future major release may forbid creation of databases and roles so named. For now, check only at known weak points in pg_dumpall. Future commits will, without notice, reject affected names from other frontend programs. Also extend the restriction to pg_dumpall --dbname=CONNSTR arguments and --file arguments. Unlike the effects on role name arguments and database names, this does not reflect a broad policy change. A migration to CreateProcess() could lift these two restrictions. Reviewed by Peter Eisentraut. Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/142c24c23447f212e642a0ffac9af878b93f490d
  • Field conninfo strings throughout src/bin/scripts. These programs nominally accepted conninfo strings, but they would proceed to use the original dbname parameter as though it were an unadorned database name. This caused "reindexdb dbname=foo" to issue an SQL command that always failed, and other programs printed a conninfo string in error messages that purported to print a database name. Fix both problems by using PQdb() to retrieve actual database names. Continue to print the full conninfo string when reporting a connection failure. It is informative there, and if the database name is the sole problem, the server-side error message will include the name. Beyond those user-visible fixes, this allows a subsequent commit to synthesize and use conninfo strings without that implementation detail leaking into messages. As a side effect, the "vacuuming database" message now appears after, not before, the connection attempt. Back-patch to 9.1 (all supported versions). Reviewed by Michael Paquier and Peter Eisentraut. Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/c400717172d77e5b07e51e04c5e5e13da181572e
  • Fix Windows shell argument quoting. The incorrect quoting may have permitted arbitrary command execution. At a minimum, it gave broader control over the command line to actors supposed to have control over a single argument. Back-patch to 9.1 (all supported versions). Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/bd65371851b7a9964b4b265d06fe1304315e37c1
  • Obstruct shell, SQL, and conninfo injection via database and role names. Due to simplistic quoting and confusion of database names with conninfo strings, roles with the CREATEDB or CREATEROLE option could escalate to superuser privileges when a superuser next ran certain maintenance commands. The new coding rule for PQconnectdbParams() calls, documented at conninfo_array_parse(), is to pass expand_dbname=true and wrap literal database names in a trivial connection string. Escape zero-length values in appendConnStrVal(). Back-patch to 9.1 (all supported versions). Nathan Bossart, Michael Paquier, and Noah Misch. Reviewed by Peter Eisentraut. Reported by Nathan Bossart. Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/fcd15f13581f6d75c63d213220d5a94889206c1b
  • Introduce a psql "\connect -reuse-previous=on|off" option. The decision to reuse values of parameters from a previous connection has been based on whether the new target is a conninfo string. Add this means of overriding that default. This feature arose as one component of a fix for security vulnerabilities in pg_dump, pg_dumpall, and pg_upgrade, so back-patch to 9.1 (all supported versions). In 9.3 and later, comment paragraphs that required update had already-incorrect claims about behavior when no connection is open; fix those problems. Security: CVE-2016-5424 http://git.postgresql.org/pg/commitdiff/9d924e9a64b91571e04252424c01210fc0f6f6d9

Álvaro Herrera pushed:

Simon Riggs pushed:

Correctifs en attente

Thomas Munro sent in a patch to implement dsm_unpin_segment.

Peter Eisentraut sent in a patch to set log_line_prefix and application name in test drivers for pg_regress.

David G. Johnston sent in a patch to make comprehension of the code in "TS_phrase_execute" a bit easier.

Amit Langote sent in another revision of the patch set to implement declarative table partitioning.

Robert Haas sent in a patch to run pgindent before the branch.

Tom Lane sent in a patch to allow GIN array_ops to work on anyarray.

Michaël Paquier sent in a patch to forbid use of LF and CR characters in database and role names.

Robert Haas sent in a patch to introduce condition variables. https://en.wikipedia.org/wiki/Monitor_(synchronization)#Condition_variables_2

Craig Ringer sent in a patch to give a more helpful hint as to when psql's \copy or similar trickery might be needed, as opposed to COPY.

Martín Marqués sent in a patch to add a test which will fail if it finds tables created by EXTENSIONs in pg_dump output.

Thomas Munro sent in a patch to create jumbo DSM segments, producing a SIGBUS if overallocated, which is not the right error.

Rushabh Lathia sent in a patch to fix a crash caused by a run of TPC-H.

Andreas Scherbaum sent in another revision of a patch to add to_date_valid().

Thomas Munro sent in a patch to add "barriers" to aid parallel computation.

Ryan Murphy sent in two revisions of a patch to make it possible for initdb to work with paths that contain spaces.