[BUG] postgresql-common conflicts with pgdg-keyring

[teankie]

Your setup

Formula commit hash / release tag

----------
          ID: postgresql-repo-keyring
    Function: pkg.installed
      Result: False
     Comment: Problem encountered installing package(s). Additional info follows:
              
              errors:
                  - Running scope as unit: run-r7e39a56323a442429316b6f81fd3db21.scope
                    dpkg: regarding .../pgdg-keyring_2018.2_all.deb containing pgdg-keyring:
                     postgresql-common conflicts with pgdg-keyring
                      pgdg-keyring (version 2018.2) is to be installed.
                    
                    dpkg: error processing archive /var/cache/salt/minion/extrn_files/base/download.postgresql.org/pub/repos/apt/pool/main/p/pgdg-keyring/pgdg-keyring_2018.2_all.deb (--install):
                     conflicting packages - not installing pgdg-keyring
                    Errors were encountered while processing:
                     /var/cache/salt/minion/extrn_files/base/download.postgresql.org/pub/repos/apt/pool/main/p/pgdg-keyring/pgdg-keyring_2018.2_all.deb
     Started: 13:24:21.208597
    Duration: 2418.584 ms
     Changes:

Versions reports (master & minion)

Pillar / config used

---

Bug details

Describe the bug

Last friday there is a new version of postgresql-common (246.pgdg18.04+1) which includes pgdg-keyring. So it will remove the package pgdg-keyring. This will break the formula.

Steps to reproduce the bug

sall-call state.sls postgres
dpkg -l | grep postgres
sall-call state.sls postgres

Expected behaviour

Option to disable postgres.postgresql-repo-keyring

Attempts to fix the bug

Troubleshooting repo:

Package: postgresql-common
Version: 246.pgdg18.04+1
Architecture: all
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Installed-Size: 708
Depends: adduser, debconf (>= 0.5.00) | debconf-2.0, libjson-perl, lsb-base (>= 3.0-3), postgresql-client-common (= 246.pgdg18.04+1), ssl-cert (>= 1.0.11), ucf, perl:any
Recommends: e2fsprogs, logrotate
Conflicts: pgdg-keyring
Breaks: postgresql-9.1 (<< 9.1.1-3~), postgresql-server-dev-all (<< 204)
Replaces: pgdg-keyring, postgresql-server-dev-all (<< 204)
Multi-Arch: foreign
Priority: optional
Section: database
Filename: pool/main/p/postgresql-common/postgresql-common_246.pgdg18.04+1_all.deb
Size: 235188
SHA256: 571c3a5988af273adb90b8465fbcac5005d6adfc2ca0eb72eca1217142608742
SHA1: 5d50af627b32523c72f9dc916f7464c4bc256c50
MD5sum: de294677d8414a51497920709ea57ce7
Description-en: PostgreSQL database-cluster manager
 The postgresql-common package provides a structure under which
 multiple versions of PostgreSQL may be installed and/or multiple
 clusters maintained at one time.
 .
 The commands provided are pg_conftool, pg_createcluster, pg_ctlcluster,
 pg_dropcluster, pg_lsclusters, pg_renamecluster, pg_upgradecluster,
 pg_virtualenv.
 .
 PostgreSQL is a fully featured object-relational database management
 system. It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects. Its features include ACID
 transactions, foreign keys, views, sequences, subqueries, triggers,
 outer joins, multiversion concurrency control, and user-defined types
 and functions.
Description-md5: 1baa00ede83212202318224f2d18d771

Additional context

[technikerk]

I can confirm this issue on an up2date Ubuntu 22.04.1 LTS, using postgresql-common 246.pgdg22.04.

One could read a few more details on the change here: https://www.postgresql.org/message-id/Y25%2BRkZxiZKBOKio%40msg.df7cb.de

My temporary workaround/fix was to update upstream.sls:

--- upstream.sls	2022-11-14 17:02:32.779693190 +0100
+++ upstream.sls.orig	2022-11-14 16:57:29.287919474 +0100
@@ -22,8 +22,8 @@
   pkg.installed:
     - pkgs: {{ postgres.pkgs_deps | json }}
 
+# Add upstream repository for your distro
   {% if grains.os_family == 'Debian' %}
-  {% if salt['pkg.version_cmp'](postgres.version, '15') >= 0 %}
 postgresql-repo-keyring:
   pkg.installed:
     - sources:
@@ -31,7 +31,6 @@
     - require_in:
       - pkgrepo: postgresql-repo
   {%- endif %}
-  {%- endif %}
 
 postgresql-repo:
   pkgrepo.managed:

[teankie]

The solution from [technikerk] is not the correct one because is use the postgresql version and the postgresql-common version.
So we made a change for postgresql-common version.

--- a/postgres/upstream.sls
+++ b/postgres/upstream.sls
@@ -1,6 +1,8 @@
 {%- from tpldir + "/map.jinja" import postgres with context -%}
 {%- from tpldir + "/macros.jinja" import format_kwargs with context -%}
 
+{% set pg_common_version = salt['pkg.version']('postgresql-common') %}
+
 {%- if 'pkg_repo' in postgres -%}
 
   {%- if postgres.use_upstream_repo == true -%}
@@ -24,6 +26,7 @@ postgresql-pkg-deps:
 
 # Add upstream repository for your distro
   {% if grains.os_family == 'Debian' %}
+  {% if salt['pkg.version_cmp'](pg_common_version, '246') <= 0 %}
 postgresql-repo-keyring:
   pkg.installed:
     - sources:
@@ -31,6 +34,7 @@ postgresql-repo-keyring:
     - require_in:
       - pkgrepo: postgresql-repo
   {%- endif %}
+  {%- endif %}
 
 postgresql-repo:
   pkgrepo.managed:

We will make a merge request for this.

[technikerk]

@teankie Thanks for your improvment of my quick workaround and your better contribution! I wasn't trying to sell it as a solution. ;)

Now, I was wondering, if one now does a fresh installation of a modern postgresql (no pgdg repo configured yet), wouldn't the repo setup fail because /usr/share/postgresql-common/pgdg/...gpg/asc is still missing? Should we rather install postgresql-common (needs postgresql-client-common) manually? But sounds wrong to me, so perhaps the better option would be just fetch/import the key for Debian on our own?

According to https://download.postgresql.org/pub/repos/apt/README the key could be obtained from https://www.postgresql.org/media/keys/ACCC4CF8.asc. That doesn't convince me in terms of trust/authenticity of the repo's content, but well..

update the package lists, and start installing packages:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Also one could think about copying /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh to this formula (, perhaps adopting it a bit) and using it?

I will test the upstream.sls version of your pull request with a fresh installation, to see if my assumption is even valid, or if everything works. :)

[technikerk]

Now, I was wondering, if one now does a fresh installation of a modern postgresql (no pgdg repo configured yet), wouldn't the repo setup fail because /usr/share/postgresql-common/pgdg/...gpg/asc is still missing? (...)
I will test the upstream.sls version of your pull request with a fresh installation, to see if my assumption is even valid, or if everything works. :)

OK, nevermind. It works perfectly (including from my top.sls: postgres.client, postgres.upstream, postgres.server, postgres.manage) as postgresql-repo-keyring would still install pgdg-keyring before postgresql-common is available on the system.

----------
          ID: postgresql-pkg-deps
    Function: pkg.installed
      Result: True
     Comment: All specified packages are already installed
     Started: 14:41:47.786339
    Duration: 6.085 ms
     Changes:   
----------
          ID: postgresql-repo-keyring
    Function: pkg.installed
      Result: True
     Comment: The following packages were installed/updated: pgdg-keyring
     Started: 14:41:47.792503
    Duration: 791.498 ms
     Changes:   
              ----------
              pgdg-keyring:
                  ----------
                  new:
                      2018.2
                  old:
----------
          ID: postgresql-repo
    Function: pkgrepo.managed
        Name: deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main
      Result: True
     Comment: Configured package repo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main'
     Started: 14:41:48.588176
    Duration: 2367.789 ms
     Changes:   
              ----------
              repo:
                  deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main

(...)

----------
          ID: postgresql-server
    Function: pkg.installed
      Result: True
     Comment: The following packages were installed/updated: postgresql-15
     Started: 14:41:56.172275
    Duration: 13954.955 ms
     Changes:   
              ----------
(...)
              pgdg-keyring:
                  ----------
                  new:
                  old:
                      2018.2
              postgresql-15:
                  ----------
                  new:
                      15.1-1.pgdg22.04+1
                  old:
              postgresql-common:
                  ----------
                  new:
                      246.pgdg22.04+1
                  old:
(...)

[christophlsa]

The change from #327 (comment) helped. Thanks!

[Lucianovici]

Hey - we also experience this. Can we merge please? :)