Check if a role exists in PostgreSQL using psql-CodePudding

I need in a bash script a IF condition on the existence of a role in a PostgreSQL database. I have found solutions in SQL code [1, 2], but I need something I can use directly in bash, I assume with the help of psql. In [2] there are also psql solutions, but I don't manage to adapt it in a IF statement.

I have tried this unsuccessfully (I am a PostgreSQL and bash newbie):

psql_USER=my
if [ "$( psql -h db -U postgres --no-psqlrc --single-transaction --pset=pager=off --tuples-only --set=ON_ERROR_STOP=1 -tc "SELECT 1 FROM pg_user WHERE usename = $psql_USER" | grep -q 1 )" == '1'  ]  > /dev/null 2> /dev/null; then
    echo "HOURRA !"
fi;

Result is:

Password for user postgres: 
ERROR:  column « my » does not exist
LINE 1: SELECT 1 FROM pg_user WHERE usename = my
                                              ^

CodePudding user response：

I would avoid the quoting problem like this:

if psql -Atq -c "SELECT '@' || usename || '@' FROM pg_user" | grep -q '@'"$psql_USER"'@'
then
    echo yes
fi

The psql invocation selects a list of all usernames, prefixed and suffixed with @. The grep has return code 0 if psql_USER contains one of these user names, else 1. The then branch of if is only taken if the return code of the pipeline is 0, that is, if the user exists in the database.