Jira (PDB-4934) PuppetDB does not support username@hostname auth for Azure PostgreSQL

[Kevin Reeuwijk (Jira)]

Title: Message Title

Kevin Reeuwijk updated an issue   PuppetDB / PDB-4934 PuppetDB does not support username@hostname auth for Azure PostgreSQL Change By: Kevin Reeuwijk 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|https://github.com/chef/chef-server/issues/1559]). I can manually modify {{database.ini}} to set the username to that format, but then you’ll see this in the logs: {noformat}clojure.lang.ExceptionInfo: Connected to database as "pe-puppetdb-migrator", not migrator "pe-puppetdb-migrator@pdb01" {noformat} It seems we have the same limitations as Chef has (see linked issue).   This requirement from Azure stems from their architecture: {noformat}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). {noformat}   Some issues I’ve encountered while trying to get this to work: * The [docs|https://puppet.com/docs/pe/2019.8/installing_postgresql.html#create_pe_databases_on_the_postgresql_instance] don’t tell you to also create a {{pe-puppetdb-migrator}} user * The [docs|https://puppet.com/docs/pe/2019.8/installing_postgresql.html#create_pe_databases_on_the_postgresql_instance] 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. Add Comment   This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.375238.1603127441000.58075.1603128360029%40Atlassian.JIRA.