Re: [GENERAL] Feature request - psql --quote-variable

Hi

2017-02-21 15:19 GMT+01:00 Caleb Cushing <xenoterracide@gmail.com>:

recently while exploring this problem http://stackoverflow.com/q/40945277/206466. I decided to go with the docker container approach of a shell script.

 I realized that postgres' variables aren't quoted either, which results in me quoting them with bash, to help avoid accidents, and even then I'm not 100% sure I'm doing it right.

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 \
-v db="${POSTGRES_DB//\'/''}" \
-v user_changeset="${DB_USER_CHANGESET//\'/''}" \
-v user_readwrite="${DB_USER_READWRITE//\'/''}" \
-v user_readonly="${DB_USER_READONLY//\'/''}" \
-v pass_changeset="'${DB_PASS_CHANGESET//\'/''}'" \
-v pass_readwrite="'${DB_PASS_READWRITE//\'/''}'" \
-v pass_readonly="'${DB_PASS_READONLY//\'/''}'" \
--username "${POSTGRES_USER}" \
--dbname "${POSTGRES_DB}" \
--file="init-user.sql"

given the Popularity of Docker and that their are UI's to pass environment variables now (meaning the person doing so might not be a qualified "DBA", nor as trusted as they should be to have "root dba" access).  Even if the person is trusted, I feel like one shouldn't have to document "don't put quotes or SQL into your password" is an indication that something is wrong.

It would be nice to have some way to properly have these variables quoted.

1. provide a new argument name say --quote-variable (or -qv) and postgres will figure out how to quote based on the position of the variable

2. allow psql (or another app?) to provide an output quoter (since it has access to the lib) `pql -v user_changeset=$( psql --quote-string $DB_USER_CHANGESET )`, kind of a weird caller but basically allows you to pass an input to a function that does quoting properly

there might be other idea's too, these are just the ones I have.

Yes I know people who are able to manage such a container should be trusted... in theory though you can provide a UI that gives them access to manage the container with no actual access to the container. I don't actually have that problem it's more of a hypothetical to me, but I'm sure it will exist at some point.

Just sharing my pain in hopes that improvements can be developed.

[pavel@localhost ~]$ psql

Debug assertions "on"

psql (10devel)

Type "help" for help.

postgres=# \set var AHOJ

postgres=# \echo :var :'var' :"var"

AHOJ 'AHOJ' "AHOJ"

postgres=# 

https://www.postgresql.org/docs/9.2/static/app-psql.html looks to "SQL Interpolation"

Regards

Pavel

 

p.s. pg is still hashing its passwords with md5? :(

--

Caleb Cushing

http://xenoterracide.com