PostgreSQL has just detected to a really bad information disclosure bug, CVE-2017-7547.
Unfortunately upgrading to a fixed version (for Debian see their security-tracker on CVE-2017-7547) is not enough, existing installations need manual work, as described in PostgreSQL's own news article 1772 describes. That howto is not only less then optimal (first half of step 4 should happen before step 3 for easier scripting) there does not seem to be a script yet.
Therefore I decided to create the following scripts ...
For manual execution and the interested here is what our full script (see below) puts in
/tmp/pg_fix_user_mappings.sql and "executes" on all databases after making additional config changes to and restarting postgres:
SET search_path = pg_catalog; CREATE OR REPLACE VIEW pg_user_mappings AS SELECT U.oid AS umid, S.oid AS srvid, S.srvname AS srvname, U.umuser AS umuser, CASE WHEN U.umuser = 0 THEN 'public' ELSE A.rolname END AS usename, CASE WHEN (U.umuser <> 0 AND A.rolname = current_user AND (pg_has_role(S.srvowner, 'USAGE') OR has_server_privilege(S.oid, 'USAGE'))) OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE')) OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN U.umoptions ELSE NULL END AS umoptions FROM pg_user_mapping U LEFT JOIN pg_authid A ON (A.oid = U.umuser) JOIN pg_foreign_server S ON (U.umserver = S.oid);
pg_fix_usermappings.sh performs the following operations:
/tmp/pg_fix_user_mappings.sqlto ALL databases
To try to fix your PostgreSQL installation in a debian or similar environment:
Overall (several variants, read before execution)
# download cd /tmp #sudo apt-get install ca-certificates wget https://download.clazzes.org/pg_fix_usermappings/pg_fix_usermappings.sh \ -O /tmp/pg_fix_usermappings.sh # make it executable, for user postgres chmod ugo+rx /tmp/pg_fix_usermappings.sh # it's safe to call the script without any parameters ... /tmp/pg_fix_usermappings.sh # execute for 9.6 as non root logging the output ( sudo sudo -u postgres /tmp/pg_fix_usermappings.sh 9.6 ) 2>&1 |tee /var/tmp/pg_fix_usermappings_9.6.log # after success maybe document sudo mv -v /tmp/pg_fix_usermappings.sh /var/tmp/pg_fix_usermappings*.log /var/log/postgresql/