agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedSuper user password explicit in patroni yml
5+ messages / 3 participants
[nested] [flat]
* Super user password explicit in patroni yml
@ 2025-02-26 19:06 kamal deen <kamaldeendba@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: kamal deen @ 2025-02-26 19:06 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Super user password explicit in patroni yml
@ 2025-02-26 19:27 Ron Johnson <ronljohnsonjr@gmail.com>
parent: kamal deen <kamaldeendba@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-02-26 19:27 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Super user password explicit in patroni yml
@ 2025-02-26 20:00 kamal deen <kamaldeendba@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: kamal deen @ 2025-02-26 20:00 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
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!
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Super user password explicit in patroni yml
@ 2025-02-26 21:01 Ron Johnson <ronljohnsonjr@gmail.com>
parent: kamal deen <kamaldeendba@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2025-02-26 21:01 UTC (permalink / raw)
To: pgsql-admin
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/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!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Super user password explicit in patroni yml
@ 2025-03-03 14:16 Michael Banck <mbanck@gmx.net>
parent: kamal deen <kamaldeendba@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Banck @ 2025-03-03 14:16 UTC (permalink / raw)
To: kamal deen <kamaldeendba@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin
Hi,
On Thu, Feb 27, 2025 at 01:30:55AM +0530, kamal deen wrote:
> Without .pgpass file patroni can connect to postgres ?
Well, I think you could also set the password via environment variables,
but I am not sure that is much better form a security point-of-view.
As an alternative, if you have unix sockets configured and keep the
superuser password empty, Patroni will use a local unix socket
connection, i.e. does not require a superuser password to be set.
> How patroni service works in this sinario?
If you want to use pg_rewind, you will need to configure an additional
pg_rewind user (with a password, cause pg_rewind connects remotely) if
your superuser has no password. If you add this to a running Patroni
cluster, I think Patroni will not GRANT the necessary function execution
rights to this pg_rewind user so you will have to do this yourself. This
should all be in the Patroni documentation.
Michael
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-03-03 14:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-26 19:06 Super user password explicit in patroni yml kamal deen <kamaldeendba@gmail.com>
2025-02-26 19:27 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-26 20:00 ` kamal deen <kamaldeendba@gmail.com>
2025-02-26 21:01 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-03 14:16 ` Michael Banck <mbanck@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox