Message-ID: <36cebb6894294c521aa92a8f1183d8e9dfb2e379.camel@ageofdream.com>
Subject: Re: Initial Postgres admin account setup using Ansible?
From: Nick <lists2@ageofdream.com>
To: pgsql-general@lists.postgresql.org
Date: Tue, 31 Dec 2024 19:17:07 -0500
In-Reply-To: <CAMDzVO_HnP+V6bL0myTt0=RRXYLNOfj4XszEKa+LvPSnePO9yg@mail.gmail.com>
References: <75b177a0f2627519419009a2134fe050f3f623cb.camel@ageofdream.com>
	 <CAMDzVO_HnP+V6bL0myTt0=RRXYLNOfj4XszEKa+LvPSnePO9yg@mail.gmail.com>
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/36cebb6894294c521aa92a8f1183d8e9dfb2e379.camel%40ageofdream.com>
Precedence: bulk

>=20
> On Tue, Dec 31, 2024 at 10:32=E2=80=AFPM Nick <lists2@ageofdream.com> wro=
te:
> >=20
> > I'm trying to create an Ansible playbook that sets up and manages
> > Postgres on Debian 12.
> >=20
> > I'm having issues with the default username/login structure, and
> > could
> > use some help.
> >=20
> > I'm installing the `postgresql` package via apt, and Debian creates
> > a
> > `postgres` system account that has a locked password.
> >=20
> > 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:
> >=20
> > "Sorry, user Ansible is not allowed to execute '/usr/bin/psql' as
> > postgres on example.com."
> >=20
> > 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.
> >=20
> > So I **could** unlock the `postgres` POSIX account, but I
> > understand
> > that this account is locked for a reason.
> >=20
> > The goal is to have Ansible manage the creation of databases and
> > roles
> > in the Postgres database.
> >=20
> > 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.
> >=20
> > 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`.
> >=20
>=20

I think I found a working solution:

In `pg_hba.conf`, change:

```
local   all             postgres        peer
```

to:

```
local    all             all             peer map=3Dansible_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?