current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
3

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?

share|improve this question

closed as not a real question by Steven Penny, Iswanto San, Craig Ringer, brasofilo, Ananda Mahto Apr 17 '13 at 4:36

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.
3  
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 Apr 17 '13 at 7:23

1 Answer 1

up vote 12 down vote accepted

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.

share|improve this answer
    
Thank you and it works. –  Xianlin Apr 18 '13 at 6:54
    
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 Nov 19 '13 at 20:50
    
Ended creating a Here Document but would still love to know how to do this as a one liner.. –  markdsievers Nov 19 '13 at 21:23
    
@markdsievers Use sudo. It's much saner, as it doesn't require an extra layer of quoting. sudo -u postgres psql -d template1 -c "..." –  Craig Ringer Nov 20 '13 at 0:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.