Postgresql – Drop USER (with privileges) IF EXIST in Postgres

automated-testspostgresql

I need to conditionally drop a user (if it does not already not exists) in Postgres. DROP USER IF EXIST is fine but according to the documentation, I also need to remove privileges first, however either DROP OWNED or REVOKE seem to have conditional IF EXIST clauses. How Can I conditionally remove privileges from my user?

My database is Postgres v9.6

Background:

I am trying to create a set of setup/teardown scripts for Postgres that will create a test area for a bunch of functions. The setup script should:

  • Create a user (if it does not already exist)
  • Create a schema with some tables (if not already exist)

and teardown will:

  • Drop the user (if it exists)
  • Drop the schema (if it exists)

The thing is that the test suite can be interrupted and leave the database/test area in a corrupt state (eg if teardown was never executed) so I have to account for that in my scripts.

According to the documentation for Postgres:

A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so. Before dropping the role, you must drop all the objects it owns (or reassign their ownership)

My user has one privilege, namely CONNECT to my test database where my test schema lies. This is because I am planning to login using the test user and execute the tests.

But because there doesn't seem to be any conditional clauses on either DROP OWNED BY or REVOKE, I cant see how that could be done?

Best Answer

Maybe I am missing some simpler solution, but from the question Detect role in Postgresql dynamically, it seems one solution is to use an anonymous code block:

-- Only revoke if user 'my_user exists'
DO $$DECLARE count int;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'my_user';
IF count > 0 THEN
    EXECUTE 'REVOKE CONNECT ON DATABASE "my-testdb" FROM my_user';
END IF;
END$$;

-- No privileges left, now it should be possible to drop
DROP USER IF EXISTS my_user;
Related Topic