Re: Initial Postgres admin account setup using Ansible?

>
> On Tue, Dec 31, 2024 at 10:32 PM Nick <lists2(at)ageofdream(dot)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`.
> >
>

I think I found a working solution:

In `pg_hba.conf`, change:

```
local all postgres peer
```

to:

```
local all all peer map=ansible_map
```

In `pg_ident.conf`, add:

```
ansible_map ansible postgres
ansible_map postgres postgres

```

Then in the playbook, don't become (stay as `ansible`):

```
- name: Ping PostgreSQL
postgresql_ping:
db: postgres
login_unix_socket: "/var/run/postgresql"
login_user: postgres
become: false
```

This seems to work, but is it secure? If USER is `all` in
`pg_hba.conf`, can any POSIX account login?