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.

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. — Charles Duffy, Jan 05 '21 at 19:50
See https://www.postgresql.org/docs/9.6/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING as a place to start — Charles Duffy, Jan 05 '21 at 19:52
Why not use the same pattern as you use for `CREATE DATABASE`, except `WHERE EXISTS`. — Adrian Klaver, Jan 05 '21 at 20:59
I'd be open to that, but I'm not clear what the WHERE clause would look like. — Dave, Jan 06 '21 at 01:56
`WHERE EXISTS (SELECT rolname FROM pg_roles WHERE rolname = $DB_USER` — Adrian Klaver, Jan 08 '21 at 00:03

score 3 · Accepted Answer · answered Jan 10 '21 at 01:38

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.

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

1 Answers1