Thread: Initial Postgres admin account setup using Ansible?

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`.

On Dec 31, 2024, at 13:31, Nick <lists2@ageofdream.com> wrote:
> 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`.

This is generally done with "become" and "become_user" in a shell command, something like:

- name: Do something as the postgres user
  ansible.builtin.shell: "psql ..."
  register: pgbackrest_which_output
  become: true
  become_user: postgres

On Tue, 2024-12-31 at 23:16 +0100, Andreas 'ads' Scherbaum wrote:
>
>
>
> 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
>
>

When trying this:

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


I get:

Ping PostgreSQL...
  xxx.xxx.xxx.xxx failed | msg: Failed to set permissions on the
temporary files Ansible needs to create when becoming an unprivileged
user (rc: 1, err: chmod: invalid mode: ‘A+user:postgres:rx:allow’
Try 'chmod --help' for more information.
}). For information on working around this, see

https://docs.ansible.com/ansible-core/2.17/playbook_guide/playbooks_privilege_escalation.html#risks-of-becoming-an-unprivileged-user

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

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?