Message-ID: <75b177a0f2627519419009a2134fe050f3f623cb.camel@ageofdream.com>
Subject: Initial Postgres admin account setup using Ansible?
From: Nick <lists2@ageofdream.com>
To: pgsql-general@lists.postgresql.org
Date: Tue, 31 Dec 2024 16:31:44 -0500
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4-2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/75b177a0f2627519419009a2134fe050f3f623cb.camel%40ageofdream.com>
Precedence: bulk


I'm trying to create an Ansible playbook that sets up and manages
Postgres on Debian 12.

I'm having issues with the default username/login structure, and could
use some help.

I'm installing the `postgresql` package via apt, and Debian creates a
`postgres` system account that has a locked password.

I can login to Postgres manually by first becoming root then running
`sudo -u postgres psql` as root. But when the Ansible user (which has
passwordless sudo) tries to run `sudo -u postgres psql`, I get:

"Sorry, user Ansible is not allowed to execute '/usr/bin/psql' as
postgres on example.com."

This is likely because the postgres POSIX account has a locked
password, so only root can become postgres. Other users with sudo
permissions can't become a locked account.

So I **could** unlock the `postgres` POSIX account, but I understand
that this account is locked for a reason.

The goal is to have Ansible manage the creation of databases and roles
in the Postgres database.

So I need to create an account in Postgres that Ansible can use as the
super user. I would like to do this in a way that doesn't require me to
manually login to the server, become root, become postgres as root,
then manually create an Ansible role.

What is the proper (secure) way to let the Ansible POSIX user manage
postgres? It seems there should be a fully automated way to bootstrap
an Ansible user for `postgres`.