a bash script to change postgresql user password

Question

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?

Answer 1

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.