Search code examples
postgresqlpsqlrolesddlprivileges

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

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.

Solution

  • 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: