Uploaded image for project: 'PuppetDB'
  1. PuppetDB
  2. PDB-4934

PuppetDB does not support username@hostname auth for Azure PostgreSQL

    XMLWordPrintable

    Details

    • Template:
    • Team:
      Ghost
    • Story Points:
      13
    • Sprint:
      ghost-27.01.2021, ghost-10.02.2021, ghost-24.02.2021
    • Method Found:
      Needs Assessment
    • Release Notes:
      Enhancement
    • Release Notes Summary:
      Hide
      Added two new users `connection-migrator-username` and `connection-username` in `database.ini` config file. The new users are used to establish connections to the database when the connection username is different from the database username (this is the case for managed PostgreSQL in Azure)

      Show
      Added two new users `connection-migrator-username` and `connection-username` in `database.ini` config file. The new users are used to establish connections to the database when the connection username is different from the database username (this is the case for managed PostgreSQL in Azure)
    • QA Risk Assessment:
      Needs Assessment

      Description

      As a customer, I want to be able to use Azure PostgreSQL as my external PostgreSQL database for Puppet Enterprise.
       
      When attempting to use Azure PostgreSQL as an external database for PuppetDB (PE 2019.8.1), I encountered the problem that Azure requires the username for the Postgres connection to be in the username@hostname form, due to the way they publish access to PostgreSQL (as described here). I can manually modify database.ini to set the username to that format, but then you’ll see this in the logs:

      clojure.lang.ExceptionInfo: Connected to database as "pe-puppetdb-migrator", not migrator "pe-puppetdb-migrator@pdb01"
      

      It seems we have the same limitations as Chef has (see linked issue).
       
      This requirement from Azure stems from their architecture:

      Azure Database for PostgreSQL has a gateway in front of the actual database servers that forwards connections from username@hostname to hostname as username.
      This means that once the connection is established, you will actually be connected as username, not username@hostname, and any database queries involving users should just use username (e.g. granting permissions).
      

       
      Some issues I’ve encountered while trying to get this to work:

      • The docs don’t tell you to also create a pe-puppetdb-migrator user
      • The docs assume a Linux OS for the psql commands to create the users & databases. However, Azure PostgreSQL runs on Windows, which causes the locales to have different names. For Azure PostgreSQL, the ENCODING line needs to be changed to: ENCODING 'utf8' LC_CTYPE 'English_United States.1252' LC_COLLATE 'English_United States.1252' template template0;
      • You can’t specify username@hostname for the xxx_regular_db_user and xxx_migration_db_user settings in pe.conf, the @hostname part gets cutoff during installation.
      • I can manually re-add the @hostname back to the username in database.ini but then the queries also expect this for the connection, which they should not. And I can probably assume that another puppet run would overwrite the settings in database.ini again.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bogdan.irimie Bogdan Irimie
              Reporter:
              kevin.reeuwijk Kevin Reeuwijk
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Zendesk Support