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
Hello,
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`.
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
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?
On Tue, Dec 31, 2024 at 5:17 PM Nick <lists2@ageofdream.com> wrote:
```
local all all peer map=ansible_map
```In `pg_ident.conf`, add:
```
ansible_map ansible postgres
ansible_map postgres postgres```
This seems to work, but is it secure? If USER is `all` in
`pg_hba.conf`, can any POSIX account login?
The presence of the mapping file reference makes the entry secure in the
sense that only those connection combinations that are explicitly permitted
can happen. The "all" is automatically restricted to those accounts listed
in the file. At worst you might get an unwanted failure if, say, you
wanted some other account "alice" to be able to connect to the cluster
using the role "alice". The "all" would match and use the mapping that
doesn't include "alice".
David J.