Hello, On Tue, Dec 31, 2024 at 10:32 PM Nick <lis...@ageofdream.com> wrote:
> > 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`. > Can you please provide an example of the task(s) which fail? If you have passwordless "sudo" configured tor the user running Ansible, this works: - name: Ping PostgreSQL postgresql_ping: db: postgres login_unix_socket: "/var/run/postgresql" login_user: postgres become: yes become_user: postgres More examples and details: https://andreas.scherbaum.la/writings/Managing_PostgreSQL_with_Ansible_-_Percona_Live_2022.pdf Regards, -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
