Postgres "ERROR: must be superuser to create a base type" during clean of DOMAIN

[skissane]

What version of Flyway are you using?

4.2.0

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Reproduced issued with command line.

What database are you using (type & version)?

Postgres 9.6.5

What operating system are you using?

macOS 10.12.6

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

1. Create migration SQL file V1__init.sql:

CREATE DOMAIN uuid_not_null_dom AS UUID NOT NULL;

2. Run "flyway migrate"

3. Run "flyway clean"

What did you expect to see?

"flyway clean" successfully deletes the domain "uuid_not_null_dom"

What did you see instead?

org.flywaydb.core.internal.dbsupport.FlywaySqlException: 
Unable to clean schema "public"
-------------------------------
SQL State  : 42501
Error Code : 0
Message    : ERROR: must be superuser to create a base type

	at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:150)
	at org.flywaydb.core.internal.command.DbClean$4.call(DbClean.java:184)
	at org.flywaydb.core.internal.command.DbClean$4.call(DbClean.java:181)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
	at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:181)
	at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:130)
	at org.flywaydb.core.Flyway$3.execute(Flyway.java:1082)
	at org.flywaydb.core.Flyway$3.execute(Flyway.java:1078)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1464)
	at org.flywaydb.core.Flyway.clean(Flyway.java:1078)
	at org.flywaydb.commandline.Main.executeOperation(Main.java:144)
	at org.flywaydb.commandline.Main.main(Main.java:103)
Caused by: org.postgresql.util.PSQLException: ERROR: must be superuser to create a base type
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
	at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:248)
	at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLSchema.doClean(PostgreSQLSchema.java:92)
	at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:148)
	... 11 more

[skissane]

Workaround: Add the following to beforeClean.sql:

DO LANGUAGE plpgsql $$ DECLARE
  v_sql TEXT;
BEGIN
  FOR v_sql IN (SELECT FORMAT('DROP DOMAIN %I CASCADE', DOMAIN_NAME)
                FROM INFORMATION_SCHEMA.DOMAINS
                WHERE DOMAIN_SCHEMA = CURRENT_SCHEMA) LOOP
    EXECUTE v_sql;
  END LOOP;
END;
$$;

[axelfontaine]

I can't seem to reproduce this. Flyway only recreates base types when cleaning Pseudo-types (category P) and User-defined types (category U), not domains (category S).

This most likely means you have some other type in your database which Flyway is attempting to clean and fails due to a permission issue.

[johnwright]

I'm hitting this issue in Flyway 5.0.7 and Postgres 9.6.

To reproduce, you need a domain whose type comes back as U, e.g.

create domain hash bytea check(length(value) = 32);

Flyway, in PostgreSQLSchema.generateDropStatementsForBaseTypes will incorrectly identify this as a user-defined type and attempt to recreate it.

[johnwright]

Looking into this a bit more, you can exclude domains from the results of the query where typtype = 'd'. The docs for the pg_type table are helpful: https://www.postgresql.org/docs/9.6/static/catalog-pg-type.html

[axelfontaine]

@johnwright Thank you very much for the detailed investigation. That was super useful!