Home / server / Questions / 901071
Accepted
Jan Vladimir Mostert
Asked: 2018-03-12 01:34:33 +0800 CST

Change postgres password using remote ssh

  • 772

I'm running a postgres database on a Google Cloud instance that we drop and recreate daily as part of our nightly build. Currently this is being done manually and I want to automate it.

gcloud compute ssh --zone europe-west2-c postgres@postgresql-dev -- "dropdb mydb"
gcloud compute ssh --zone europe-west2-c postgres@postgresql-dev -- "createdb mydb"
gcloud compute ssh --zone europe-west2-c postgres@postgresql-dev -- "psql postgres -c 'GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;' "

This works fine to drop and recreate the database, the problem comes in when I have to re-setup the password ...

gcloud compute ssh --zone europe-west2-c postgres@postgresql-dev -- "psql postgres -c 'ALTER USER myuser WITH PASSWORD 'passwordhere' ; "

gcloud command is already using the double quotes, psql postgres -c is already using the single quotes, what quotes should I be using to put the password in quotes?

I've tried escaping the quotes, but it doesn't work:

WITH PASSWORD \'passwordhere\'

ERROR: syntax error at or near "\" LINE 1: ALTER USER myuser WITH PASSWORD \passwordhere'

or

WITH PASSWORD \"passwordhere\"

bash: -c: line 0: unexpected EOF while looking for matching `'' bash: -c: line 1: syntax error: unexpected end of file

How do I escape those quotes?

bash

1 Answers

  1. Best Answer

    this seems to work:

    gcloud compute ssh --zone europe-west2-c postgres@postgresql-dev -- "psql postgres -c 'ALTER USER myuser WITH PASSWORD '\''passwordhere'\'' '; "

    -

    WITH PASSWORD '\''passwordhere'\''
    • 3