MIME-Version: 1.0
References: <75b177a0f2627519419009a2134fe050f3f623cb.camel@ageofdream.com>
In-Reply-To: <75b177a0f2627519419009a2134fe050f3f623cb.camel@ageofdream.com>
From: "Andreas 'ads' Scherbaum" <adsmail@wars-nicht.de>
Date: Tue, 31 Dec 2024 23:16:46 +0100
Message-ID: <CAMDzVO_HnP+V6bL0myTt0=RRXYLNOfj4XszEKa+LvPSnePO9yg@mail.gmail.com>
Subject: Re: Initial Postgres admin account setup using Ansible?
To: Nick <lists2@ageofdream.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e1d9cc062a984990"
Archived-At: <https://www.postgresql.org/message-id/CAMDzVO_HnP%2BV6bL0myTt0%3DRRXYLNOfj4XszEKa%2BLvPSnePO9yg%40mail.gmail.com>
Precedence: bulk

--000000000000e1d9cc062a984990
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,

On Tue, Dec 31, 2024 at 10:32=E2=80=AFPM 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`.
>

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_-_Pe=
rcona_Live_2022.pdf


Regards,

--=20
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

--000000000000e1d9cc062a984990
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div>Hello,<br></div><br><div c=
lass=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_=
attr">On Tue, Dec 31, 2024 at 10:32=E2=80=AFPM Nick &lt;<a href=3D"mailto:l=
ists2@ageofdream.com">lists2@ageofdream.com</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><br>
I&#39;m trying to create an Ansible playbook that sets up and manages<br>
Postgres on Debian 12.<br>
<br>
I&#39;m having issues with the default username/login structure, and could<=
br>
use some help.<br>
<br>
I&#39;m installing the `postgresql` package via apt, and Debian creates a<b=
r>
`postgres` system account that has a locked password.<br>
<br>
I can login to Postgres manually by first becoming root then running<br>
`sudo -u postgres psql` as root. But when the Ansible user (which has<br>
passwordless sudo) tries to run `sudo -u postgres psql`, I get:<br>
<br>
&quot;Sorry, user Ansible is not allowed to execute &#39;/usr/bin/psql&#39;=
 as<br>
postgres on <a href=3D"http://example.com" rel=3D"noreferrer" target=3D"_bl=
ank">example.com</a>.&quot;<br>
<br>
This is likely because the postgres POSIX account has a locked<br>
password, so only root can become postgres. Other users with sudo<br>
permissions can&#39;t become a locked account.<br>
<br>
So I **could** unlock the `postgres` POSIX account, but I understand<br>
that this account is locked for a reason.<br>
<br>
The goal is to have Ansible manage the creation of databases and roles<br>
in the Postgres database.<br>
<br>
So I need to create an account in Postgres that Ansible can use as the<br>
super user. I would like to do this in a way that doesn&#39;t require me to=
<br>
manually login to the server, become root, become postgres as root,<br>
then manually create an Ansible role.<br>
<br>
What is the proper (secure) way to let the Ansible POSIX user manage<br>
postgres? It seems there should be a fully automated way to bootstrap<br>
an Ansible user for `postgres`.<br></blockquote><div><br></div><div>Can you=
 please provide an example of the task(s) which fail?<br></div><div>If you =
have passwordless &quot;sudo&quot; configured tor the user running Ansible,=
</div><div>this works:</div><div><br></div><div><span style=3D"font-family:=
monospace">- name: Ping PostgreSQL<br>=C2=A0 postgresql_ping:<br>=C2=A0 db:=
 postgres<br>=C2=A0 login_unix_socket: &quot;/var/run/postgresql&quot;<br>=
=C2=A0 login_user: postgres<br>=C2=A0 become: yes<br>=C2=A0 become_user: po=
stgres</span></div><div>=C2=A0</div><div>More examples and details:</div><d=
iv><a href=3D"https://andreas.scherbaum.la/writings/Managing_PostgreSQL_wit=
h_Ansible_-_Percona_Live_2022.pdf">https://andreas.scherbaum.la/writings/Ma=
naging_PostgreSQL_with_Ansible_-_Percona_Live_2022.pdf</a></div></div><div>=
<br clear=3D"all"></div><div><br></div><div>Regards,<br></div><div><br></di=
v><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" cla=
ss=3D"gmail_signature"><div dir=3D"ltr"><span><div><div dir=3D"ltr"><span><=
span style=3D"white-space:pre">				</span></span>Andreas &#39;ads&#39; Sche=
rbaum<br>German PostgreSQL User Group<br>European PostgreSQL User Group - B=
oard of Directors<br>Volunteer Regional Contact, Germany - PostgreSQL Proje=
ct<br></div></div></span></div></div></div>

--000000000000e1d9cc062a984990--