Shell script for creating user with password in postgres failing with quotes

Question

I am trying to create a shell script to bootstrap new DBs. I am able to create users, grant privileges and do all actions, except running any queries with passwords. The single quotes in shell script creates statements which postgres is not accepting. Because of this, we cannot completely automate this process.

Below is one of the postgres line used in shell script.

PGPASSWORD=change123 psql -h $DB -p 5432 -d postgres -U root -c \"CREATE USER $(echo "$j" | cut -d "_" -f1)dbuser WITH PASSWORD \'$(echo $DBPASSWD|base64 --decode)\';\"

When executing the above script, the command is converted as

psql -h testdb -p 5432 -d postgres -U root -c '"CREATE' USER admindbuser WITH PASSWORD ''\''ZnuLEmu72R'\'''

where I want the command to be like

psql -h testdb -p 5432 -d postgres -U root -c "CREATE USER admindbuser WITH PASSWORD 'ZnuLEmu72R';"

Any help is very much appreciated. I want some help in guiding how to modify the line in shell so as to achieve the required command.

Why do you have a backslash before the initial double quotes? — Charles Duffy
– Charles Duffy, Commented Sep 6, 2019 at 15:12
...mind you, doing this securely (in a way that doesn't let someone choosing a malicious password run an arbitrary database command) is a whole different question. If that's something you'd consider important, I'd suggest amending the question to make it clear. — Charles Duffy
– Charles Duffy, Commented Sep 6, 2019 at 15:12
@thriqon tried it already and its failing like below psql -h testdb -p 5432 -d postgres -U root -c 'CREATE USER admindbuser WITH PASSWORD '\''1k4a24ILvzJqT'\'';' — yog raj
– yog raj, Commented Sep 6, 2019 at 15:18

chepner · Accepted Answer · 2019-09-06 15:13:44Z

3

Change

PGPASSWORD=change123 psql\
  -h $DB \
  -p 5432 \
  -d postgres \
  -U root \
  -c \"CREATE USER $(echo "$j" | cut -d "_" -f1)dbuser WITH PASSWORD \'$(echo $DBPASSWD|base64 --decode)\';\"

to

PGPASSWORD=change123 psql \
  -h "$DB" \
  -p 5432 \
  -d postgres \
  -U root \
  -c "CREATE USER ${j%%_*}dbuser WITH PASSWORD '$(printf '%s' "$DBPASSWD" | base64 --decode)';"

answered Sep 6, 2019 at 15:13

chepner

538k77 gold badges594 silver badges746 bronze badges

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

6 Comments

Charles Duffy Over a year ago

Only caveat here is that this doesn't take any effort to move the data out-of-band from the (SQL) code. (After all, if all the users we were setting passwords for were completely trusted, they could just be given a shared admin account).

Charles Duffy Over a year ago

Even using, say, sql_str() { printf "'%s'" "${1//\'/''}"; } and substituting in $(sql_str "$(base64 --decode --ignore-garbage <<<"$DBPASSWD") is better than no escaping at all.

yog raj Over a year ago

bash -x gives below output for the above changes. ` + PGPASSWORD=change123 + psql -h testdb -p 5432 -d postgres -U root -c 'CREATE USER admindbuser WITH PASSWORD '\''xWb0HL3CmG0dfXWY8e'\'';' `

Charles Duffy Over a year ago

@yograj, yes, and that's correct bash -x output; it's how your command is supposed to be run. Once you strip out the quotes that are shell syntax instead of SQL syntax (as with the printf '%s\n' trick I showed you in the question's comments), you're left with a query of CREATE USER admindbuser WITH PASSWORD 'xWb0HL3CmG0dfXWY8e';, and that query is what you want.

Charles Duffy Over a year ago

@yograj, ...as an explanation of why the shell has the extra single-quotes that don't look like they make sense to you, keep in mind that backslashes are literal inside single-quotes, so when it prints '\", what it's doing is first exiting the single-quoted context that was entered into earlier, then creating a backslashed (thus literal) double-quote character.

|

Collectives™ on Stack Overflow

Shell script for creating user with password in postgres failing with quotes

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related