public inbox for [email protected]help / color / mirror / Atom feed
Re: 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]> 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:22 Nick <[email protected]> parent: Andreas 'ads' Scherbaum <[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? @ 2025-01-01 00:17 Nick <[email protected]> parent: Andreas 'ads' Scherbaum <[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