[Pkg-postgresql-public] Bug#838812: pg_upgradecluster skips databases owned by a role who is not a user

[Tilman Koschnick]

Package: postgresql-common
Version: 177.pgdg80+1
Followup-For: Bug #838812

Dear Maintainer,

I can confirm the problem; the attached patch fixes it.

A brief explanation: In the query to select databases which need upgrading, pg_database is joined onto pg_user, which is a view of pg_shadow, which in turn is a view of pg_authid, containing only roles with login privileges. Thus any databases owned by roles without login privileges are not included in the list. The fix is to join on pg_authid instead.

See https://www.postgresql.org/docs/9.6/static/view-pg-user.html and https://www.postgresql.org/docs/9.6/static/view-pg-shadow.html .

Regards, Til

PS #614374 is a duplicate of this bug.

-- System Information:
Debian Release: 8.6
  APT prefers stable-updates
  APT policy: (500, 'stable-updates'), (500, 'stable')
Architecture: amd64 (x86_64)
Foreign Architectures: i386

Kernel: Linux 3.16.0-4-amd64 (SMP w/4 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)

Versions of packages postgresql-common depends on:
ii  adduser                   3.113+nmu3
ii  debconf [debconf-2.0]     1.5.56
ii  init-system-helpers       1.22
ii  lsb-base                  4.1+Debian13+nmu1
ii  postgresql-client-common  177.pgdg80+1
ii  procps                    2:3.3.9-9
ii  ssl-cert                  1.0.35
ii  ucf                       3.0030

Versions of packages postgresql-common recommends:
ii  logrotate  3.8.7-1+b1

postgresql-common suggests no packages.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pg_upgradecluster.diff
Type: text/x-diff
Size: 646 bytes
Desc: not available
URL: <http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/attachments/20161031/8efeff2d/attachment.diff>