escaping quotes in psql command for fabric script

Question

building on this question, I'm trying to delete all tables in my postgresql database with a fabric command. The bash command I'm trying to run is

#!/bin/bash
TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

echo Dropping tables:${TABLES}
psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"

which inside my fab script becomes:

def delete_tables():
    the_command = "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"
    run("TABLES=`psql -U db_user -d db_name $PGDB -t --command %s`" % the_command)

but the error is, Peer authentication failed for user "string_agg". Which seems to indicate the command is not considered as a command between " ", but a long single string ...

I've tried converting: ' into '\'' but no luck. Any suggestions are welcome.

Answer 1

Use pipes.quote() to quote something that goes to the shell.

import pipes
def delete_tables():
    the_command = "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"
    run("TABLES=`psql -U db_user -d db_name $PGDB -t --command %s`" % pipes.quote(the_command))