public inbox for [email protected]
help / color / mirror / Atom feedRe: Initial Postgres admin account setup using Ansible?
3+ messages / 2 participants
[nested] [flat]
* Re: Initial Postgres admin account setup using Ansible?
@ 2024-12-31 22:16 Andreas 'ads' Scherbaum <[email protected]>
2024-12-31 22:22 ` Re: Initial Postgres admin account setup using Ansible? Nick <[email protected]>
2025-01-01 00:17 ` Re: Initial Postgres admin account setup using Ansible? Nick <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas 'ads' Scherbaum @ 2024-12-31 22:16 UTC (permalink / raw)
To: Nick <[email protected]>; +Cc: [email protected]
Hello,
On Tue, Dec 31, 2024 at 10:32 PM Nick <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Initial Postgres admin account setup using Ansible?
2024-12-31 22:16 Re: Initial Postgres admin account setup using Ansible? Andreas 'ads' Scherbaum <[email protected]>
@ 2024-12-31 22:22 ` Nick <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Nick @ 2024-12-31 22:22 UTC (permalink / raw)
To: Andreas 'ads' Scherbaum <[email protected]>; +Cc: [email protected]
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-...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Initial Postgres admin account setup using Ansible?
2024-12-31 22:16 Re: Initial Postgres admin account setup using Ansible? Andreas 'ads' Scherbaum <[email protected]>
@ 2025-01-01 00:17 ` Nick <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Nick @ 2025-01-01 00:17 UTC (permalink / raw)
To: [email protected]
>
> On Tue, Dec 31, 2024 at 10:32 PM Nick <[email protected]> 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-01-01 00:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-31 22:16 Re: Initial Postgres admin account setup using Ansible? Andreas 'ads' Scherbaum <[email protected]>
2024-12-31 22:22 ` Nick <[email protected]>
2025-01-01 00:17 ` Nick <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox