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

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?

Solution

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?.