How to set postgresql user password in bash script

Question

I want to set a password for the default Postgresql server user, postgres. I did it by using:

sudo -u postgres psql
# \password postgres

I want to do this step in many machines, so I would like to create a bash script to do the same. How to accomplish this in bash?

Solution 1

As documented you can run meta-commands via the --command option.

sudo -u postgres psql --command '\password postgres'

The single quotes ensure that the shell doesn't treat the backslash as an escape-character.

Solution 2

Instead of using the psql \password command, which expects an interactive terminal, you can use:

ALTER USER postgres WITH PASSWORD 'newpassword';

Say, via a psql -c command:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Comments

[Admin]

Upvoted the question : Many questions on Server Fault can be learnt from documentation, books, websites... The person who downvoted you will not teach you to use the man command. Please be sure to try man psql in the future.

[saji89]

Thanks, I just did the same, but with a minor difference: sudo -u postgres psql --command "\password". Wondering, why my question was downvoted.

[FooBee]

@saji89: It's not my downvote, but I guess because this can be easily learned by reading the documentation.

[Ansgar Wiechers]

@saji89 When you use double quotes, the shell treats the backslash as an escape-character, i.e. as an instruction to treat the next character as a literal rather than a special character. To get a literal backslash inside double qoutes you have to use "\\...".

[saji89]

@AnsgarWiechers, Thanks for that correction. But the funny part is that that line is working for me. From what I read at gnu.org/software/bash/manual/html_node/Double-Quotes.html It says: The backslash retains its special meaning only when followed by one of the following characters: ‘$’, ‘`’, ‘"’, ‘\’, or newline. I think that is why "\password" worked fine.

[ChocoDeveloper]

WITH, not SET (15 chars)

[Aaron Hudon]

It's sad that the \password command does not have a non-interactive method. I can understand the reasoning (hard to call it safely on the client), but the end effect is that people use the ALTER ROLE command which sends the password in the statement unencrypted (which is the major advantage of \password or createuser -E -P.