Thread: Super user password explicit in patroni yml
Hi All,
As part of the security standardization we are working on postgres super user DB password rotation POC.
In that POC we have successfully rotated the password with the help of password management tool.
We have noticed in patroni yml file for authentication we are explicitly using super user name and credentials same for replicator user as well.
Is there any option we can pass this password instead of direct mentioning or using .pgpass file.
Our intention here is we should not expose superuser password anywhere at server level.
If we change the super user password at DB level should we update the same in patroni yml every time ? If we not update that password in patroni yml file is that impact anything of replication , API calls , primary and replica connectivity?
And also pls share the best way to rotate the DB user password in postgres.
Your valuable suggestion is highly appreciated.
Regards,
SK.
On Wed, Feb 26, 2025 at 2:07 PM kamal deen <kamaldeendba@gmail.com> wrote:
Hi All,
[snip]
And also pls share the best way to rotate the DB user password in postgres.
I wrote a shell script to generate(*) a password, run the ALTER ROLE command, push the VALID UNTIL out by 3 months, and either(**) "sed edit .pgpass" or send the user an email with the new password.
*Via picking two random words from /usr/share/dict/words, a random 2 digit number, concatenated with a period. "openssl rand -base64 48" works, too. Got the basics from a StackExchange post.
**Depending on the user
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Thank you John,
Without .pgpass file patroni can connect to postgres ?
How patroni service works in this sinario?
Any insight on that postgres patroni configuration?
***//Authentication Config Sample from Patrnoi yml file // ***
=================
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: **********
superuser:
username: postgres
password: **********
===================
Regards,
SK
On Thu, Feb 27, 2025, 12:58 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Wed, Feb 26, 2025 at 2:07 PM kamal deen <kamaldeendba@gmail.com> wrote:Hi All,[snip]And also pls share the best way to rotate the DB user password in postgres.I wrote a shell script to generate(*) a password, run the ALTER ROLE command, push the VALID UNTIL out by 3 months, and either(**) "sed edit .pgpass" or send the user an email with the new password.*Via picking two random words from /usr/share/dict/words, a random 2 digit number, concatenated with a period. "openssl rand -base64 48" works, too. Got the basics from a StackExchange post.**Depending on the user--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
I don't use patroni, so just answered one specific question: how to rotate a role password.
On Wed, Feb 26, 2025 at 3:41 PM kamal deen <kamaldeendba@gmail.com> wrote:
Thank you John,Without .pgpass file patroni can connect to postgres ?How patroni service works in this sinario?Any insight on that postgres patroni configuration?***//Authentication Config Sample from Patrnoi yml file // ***=================pgpass: /tmp/pgpassauthentication:replication:username: replicatorpassword: **********superuser:username: postgrespassword: **********===================Regards,SKOn Thu, Feb 27, 2025, 12:58 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Wed, Feb 26, 2025 at 2:07 PM kamal deen <kamaldeendba@gmail.com> wrote:Hi All,[snip]And also pls share the best way to rotate the DB user password in postgres.I wrote a shell script to generate(*) a password, run the ALTER ROLE command, push the VALID UNTIL out by 3 months, and either(**) "sed edit .pgpass" or send the user an email with the new password.*Via picking two random words from /usr/share/dict/words, a random 2 digit number, concatenated with a period. "openssl rand -base64 48" works, too. Got the basics from a StackExchange post.**Depending on the user--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!