Re: Feature request - psql --quote-variable

Hi

2017-02-21 15:19 GMT+01:00 Caleb Cushing <xenoterracide(at)gmail(dot)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(at)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
>