Postgres: creating role and password from CLI, nested quotes problems

Question

I'm trying to create a postgres role and password from bash. However, it seems I'm struggling with the escaping of quotes.

Running:

$ sudo su - postgres -c '''psql -c "CREATE USER testuser WITH PASSWORD 'somepass';" '''

Produces:

ERROR:  syntax error at or near "somepass"
LINE 1: CREATE USER testuser WITH PASSWORD somepass;

Other unsuccessful variations I've tried;

sudo su - postgres -c """psql -c "CREATE USER testuser WITH PASSWORD 'somepass';" """
sudo su - postgres -c $''' psql -c "CREATE USER testuser WITH PASSWORD \'pass\';" '''

How can I deal with the nested quotes, and get postgres to create the role from cli?

melpomene · Accepted Answer · 2016-12-30 12:45:47Z

3

''' and $''' are pointless. They're equivalent to '.

That's because '''foo' is parsed as an empty quoted string '' immediately followed by another quoted string 'foo', which is just foo.

To get a single ' into a single-quoted shell string, use '\'':

sudo su - postgres -c 'psql -c "CREATE USER testuser WITH PASSWORD '\''somepass'\'';"'

The reason this works: 'foo'\''bar' is parsed as a quoted part ('foo') followed by an unquoted but backslash-escaped character (\') followed by another quoted part ('bar'). These are all concatenated to form foo'bar.

answered Dec 30, 2016 at 12:45

melpomene

86.2k8 gold badges95 silver badges154 bronze badges

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

Comments

wildplasser · Accepted Answer · 2016-12-30 13:37:12Z

1

You can avoid all quoting problems by using a here-document :

#!/bin/sh
sudo su - postgres -c psql <<OMG
CREATE USER testuser WITH PASSWORD 'somepass';
-- DROP USER testuser ;
OMG

BTW: you dont need a script for this, it will work just as well in an interactive shell. (I just used the script to test it)

edited Dec 30, 2016 at 13:37

answered Dec 30, 2016 at 13:21

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

Postgres: creating role and password from CLI, nested quotes problems

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related