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?

Answer 1

''' 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.