How to execute "DROP OWNED BY" only if the user exists?

Question

I'm trying to write a bash script that will create a Postgres database, as well as the user and the user privileges to access that database. I'm using Postgres 9.6. I have the below ...

create_db_command="SELECT 'CREATE DATABASE $DB_NAME' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DB_NAME')\gexec"
drop_owned_by_command="DROP OWNED BY $DB_USER;"
drop_role_command="DROP ROLE IF EXISTS $DB_USER;"
create_user_command="create user $DB_USER with encrypted password '$DB_PASS';"
grant_privs_command="grant all privileges on database $DB_NAME to $DB_USER;"

PGPASSWORD=$ROOT_PASSWORD
# This command creates the db if it doesn't already exist
echo "SELECT 'CREATE DATABASE $DB_NAME' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DB_NAME')\gexec" | psql -U$PG_USER
psql -U$PG_USER $DB_NAME -c "$drop_owned_by_command"
psql -U$PG_USER -c "$drop_role_command"
psql -U$PG_USER -c "$create_user_command"
psql -U$PG_USER -c "$grant_privs_command"

The problem is when the script is run the very first time, the command

DROP OWNED BY $DB_USER;

fails because the user does not yet exist. Is there a way to write the above command so that it will only run if the user exists? Similar to DROP USER IF EXISTS ..., but DROP OWNED has no IF EXISTS clause.

Leake

Two major directions you could come at this from: One is trying to detect and handle the case inside your psql code, the other is trying to detect and handle it from bash. Personally, I'd suggest the former. – Leatherjacket

See postgresql.org/docs/9.6/… as a place to start – Leatherjacket

Why not use the same pattern as you use for CREATE DATABASE, except WHERE EXISTS. – Limitative

I'd be open to that, but I'm not clear what the WHERE clause would look like. – Leake

WHERE EXISTS (SELECT rolname FROM pg_roles WHERE rolname = $DB_USER – Limitative

Answer 1

You can use a similar technique like you already have for CREATE DATABASE.
In the shell:

drop_owned_by_command="SELECT 'DROP OWNED BY $DB_USER' FROM pg_roles WHERE rolname = '$DB_USER'\gexec"

echo $drop_owned_by_command | psql -U$PG_USER $DB_NAME

The SELECT only returns a row (containing the DDL command) if the role a actually exists. This is in turn executed by the psql command \gexec.

So we have a combination of SQL and psql commands and cannot use psql -c since, quoting the manual on --command:

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option.

Instead, pipe the echo to psql like demonstrated - and like suggested in the manual and in my related answer below and like you already do for CREATE DATABASE.

Related:

• Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
• Shell script to execute pgsql commands in files

Patchy