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?

I actually voted this as off-topic for migration to superuser.com. It really irritates me that Stack Overflow puts my name in as someone who closevoted it for being "not a real question" when I voted to migrate it. — Craig Ringer
– Craig Ringer, Commented Apr 17, 2013 at 7:23

Craig Ringer · Accepted Answer · 2013-04-17 04:17:39Z

52

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.

answered Apr 17, 2013 at 4:17

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

markdsievers Over a year ago

I used this as resource to try automate the provisioning of uuid extensions but am hitting problems I don't quite get. su command I am using is su - postgres -c "psql -d template1 -c \"CREATE EXTENSION \"uuid-ossp\";\"" however I am getting a syntax error similar to that if you used that command in the psql shell with the uuid-ossp unquoted. I thought I had adequately escaped my quotes. Any ideas?

markdsievers Over a year ago

Ended creating a Here Document but would still love to know how to do this as a one liner..

Craig Ringer Over a year ago

@markdsievers Use sudo. It's much saner, as it doesn't require an extra layer of quoting. sudo -u postgres psql -d template1 -c "..."

CriPstian Over a year ago

If it helps anyone that has issues with this and using characters like dashes in the usernames: sudo -u postgres psql -c "alter user \"username-with-dash\" with password 'password-with-dash';"

Dean Christian Armada Over a year ago

You are a life saver! :)

Collectives™ on Stack Overflow

a bash script to change postgresql user password

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related