Columns combined when using `select ...pgp_sym_decrypt ()` from postgresql databse through Bash psql

Question

System is Debian 11.2 with PostgreSQL 11.5.

I created a database and table as below:

CREATE DATABASE dbname OWNER=postgres
ENCODING= 'UTF8'

\c dbname

CREATE TABLE test(
   id serial primary key,
   site varchar(100)    NOT NULL,
   username char(30) NOT NULL,
   password        char(300)    NOT NULL,
   note            varchar(200) DEFAULT NULL
);

Create bash file as below:

#!/bin/bash
res_user='me'
db_user='postgres'
db_name='dbname'
table_name='test'
sym_key='key'

#insert 4 columns
    su $db_user <<EOFU
psql -d "$db_name" -U "$db_user" << EOF
INSERT INTO $table_name (site,username,password,note) VALUES ('v4','u3',pgp_sym_encrypt('password','key','cipher-algo=aes128,compress-algo=0,convert-crlf=1,sess-key=0,s2k-mode=3'),'note3');
EOF
EOFU

#column note has no output
   password_arr=($(su $db_user <<EOFU
psql -tAq --field-separator= -d "$db_name" -U "$db_user" << EOF
SELECT "username",pgp_sym_decrypt(password::bytea,'key'),"note" FROM "$table_name" WHERE "site" LIKE '%v4%';
EOF
EOFU
))
echo "${password_arr[1]}" #output is passwordnote3
echo "${password_arr[2]}" #no ouput?

The expect output is:

${password_arr[1]} is `password`
${password_arr[2]} is `note3`

Run above bash script, but output "${password_arr[2]}" has no value,"${password_arr[1]}" is passwordnote3. Where is the problem?

Unrelated, but: `(SELECT pgp_sym_decrypt(password::bytea,'key'))` can be simplified to `pgp_sym_decrypt(password::bytea,'key')` there is no need to prefix a function call with SELECT — , Jan 08 '22 at 08:57
@konsolebox,output is `psql -tAq --field-separator= -d "dbname" -U "postgres" << EOF SELECT "username",pgp_sym_decrypt(password::bytea,'key'),"note" FROM "test" WHERE "site" LIKE '%v4%'; EOF ` — kittygirl, Jan 08 '22 at 12:43
I checked the resulting syntax. There seems nothing wrong with it. Try storing the result to a string variable and run `declare -p` against that variable first before splitting the string to an array. Share to us the output of `declare -p`. (Edit: Thanks, just saw the comment. Indeed nothing seems wrong with it.) — konsolebox, Jan 08 '22 at 12:46
@konsolebox,Output of `declare -p $password_arr` is `INSERT 0 1 ./a.sh: line 22: declare: u3: not found`.Thanks for your help! — kittygirl, Jan 08 '22 at 13:02
@konsolebox,Output of `declare -p` is `declare -- db_name="dbname" declare -- db_user="postgres" declare -a password_arr=([0]="u3" [1]="passwordnote3" [2]="u3" [3]="passwordnote3" [4]="u3" [5]="passwordnote3" [6]="u3" [7]="passwordnote3" [8]="u3" [9]="passwordnote3" [10]="u3" [11]="passwordnote3") declare -- res_user="me" declare -- sym_key="key" declare -- table_name="test"` — kittygirl, Jan 08 '22 at 13:05
Sorry I should have suggested a syntax. Should be this: `response=$(su $db_user < — konsolebox, Jan 08 '22 at 13:05
@konsolebox,`declare -- response="u3 passwordnote3 u3 passwordnote3 u3 passwordnote3 u3 passwordnote3 u3 passwordnote3 u3 passwordnote3 u3 passwordnote3" declare -a password_arr=([0]="u3" [1]="passwordnote3" [2]="u3" [3]="passwordnote3" [4]="u3" [5]="passwordnote3" [6]="u3" [7]="passwordnote3" [8]="u3" [9]="passwordnote3" [10]="u3" [11]="passwordnote3" [12]="u3" [13]="passwordnote3") ` — kittygirl, Jan 08 '22 at 13:10

score 1 · Accepted Answer · answered Jan 08 '22 at 14:23

I found the issue. The problem is you specified --field-separator to "nothing" instead of a space. It should be --field-separator=" ". This allowed the output of pgp_sym_decrypt() to concatenate with note. The username field however always had spaces probably since it has a fixed width of 30.

I also suggest that you reduce the number of row outputs to 1, and also enable "noglob" option when you're relying on word splitting. This can be done with set -f. You can also use read to get the needed fields. See How to split a string into an array in Bash?.

Columns combined when using `select ...pgp_sym_decrypt ()` from postgresql databse through Bash psql

1 Answers1