Hi 2017-02-21 15:19 GMT+01:00 Caleb Cushing <xenoterrac...@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 >