CODEWITHSUNDEEP
bashpostgresqlshellcommand
Xianlin
Xianlin

Reputation: 1189

a bash script to change postgresql user password

I can change the postgresql user password in this way (2 steps):

$ su - postgres -c 'psql -U postgres -d postgres'
# Alter user postgres with password 'password';

Now I want to use a single line command (1 step) to change the password such as:

su - postgres -c 'psql -U postgres -d postgres -c "alter user postgres with password ''password'';"'

I heard using double single quote to escape one single quote so I added double quote '. However is shows error message:

ERROR:  syntax error at or near "password"
LINE 1: alter user postgres with password password;

Could someone let me know how to use one line of command to do this?

Upvotes: 25

Views: 28251

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324751

Easier if you use sudo:

sudo -u postgres psql -U postgres -d postgres -c "alter user postgres with password 'password';"

but it's possible with su too, this should work:

su - postgres -c "psql -U postgres -d postgres -c \"alter user postgres with password 'password';\""

I've used outer double quotes and escaped the inner double quotes so they pass through as part of a single call argument to su and get unescaped by the shell so the actual query text gets passed as a single arg to psql including the single-quoted password.

One of the reasons sudo is easier for this is that it uses a smarter way of executing the subprocess instead of running a second shell instance to do it. You need one less layer of shell metacharacter escaping.

Upvotes: 52

Related Questions