Cannot drop tables in remote psql

chrisdel101 · March 24, 2023, 8:58pm

This is not really elixir specific, but I’m using it in elixir right now. I’ve connected to a remote psql instance with Elephant DB. I asked another question earlier.

I can’t drop the DB now or do mix ecto.reset (which I use alot) returns

9:35:29.583 [error] GenServer #PID<0.701.0> terminating
** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "XXX", user "my_db_name", database "postgres", SSL off
    (db_connection 2.4.3) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2
    (connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 4.3) proc_lib.erl:240: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (Mix) The database for MyApp couldn't be dropped: killed

psql says I can’t do it while I’m logged into it.
DROP DATABASE my_db_name;

I see it says no pg_hba.conf entry but I don’t how to fix this.

From psql trying this fails:

my_db_name=> SHOW config_file;
ERROR: must be superuser or a member of pg_read_all_settings to examine "config_file"
How this means my migrations are stuck and I can’t rerun them.

al2o3cr · March 24, 2023, 11:34pm

database: "postgres" in this error is because (as you noted above) the DB can’t be dropped while connected to it. Ecto uses a “maintenance database” (that defaults to postgres) here:

github.com

elixir-ecto/ecto_sql/blob/2462c640b7f9cc036f6ec853e967cdaf529031d8/lib/ecto/adapters/postgres.ex#L217-L232


      
          def storage_down(opts) do
            database = Keyword.fetch!(opts, :database) || raise ":database is nil in repository configuration"
            command = "DROP DATABASE \"#{database}\""
                      |> concat_if(opts[:force_drop], fn _ -> "WITH (FORCE)" end)
            maintenance_database = Keyword.get(opts, :maintenance_database, @default_maintenance_database)
            opts = Keyword.put(opts, :database, maintenance_database)
          
          
  case run_query(command, opts) do
              {:ok, _} ->
                :ok
              {:error, %{postgres: %{code: :invalid_catalog_name}}} ->
                {:error, :already_down}
              {:error, error} ->
                {:error, Exception.message(error)}
            end
          end

The error message is PG’s way of saying “I know that user but they aren’t configured to talk to that database”; are you on a shared plan with Elephant? That FAQ entry suggests that if you are, you can’t create/drop databases.

chrisdel101 · April 2, 2023, 5:12pm

U are right. The reason is due to the free plan on Elephant. Support replied to me:

We don’t allow superuser permissions, however if you’re on a dedcated plan we can perform certain changes for you.

So how do you handle DB changes in dev then?

Like when changes are made to the schema/mig files. Once I run the migrations once, I cannot re-run them, it will just say “up to date” and so I am stuck with a useless, outdated DB. I had to create a totally new one.

I would normally do ecto.reset but here I cannot.

This question would apply to any language and is b/c I don’t know how to handle not having superuser perm, and maybe I am lacking knowledge about migration best-practices.

katafrakt · April 2, 2023, 7:27pm

You can roll the migration back (mix ecto.rollback), change it and run again.

If that is not enough, you can write your custom reset task that lists all tables, views, functions etc., drops them and runs migrations on an empty database. Tedious, but I think your setup (where you cannot drop the database) and workflow (where you need to reser the database from scratch often) is not a usual one, so it’s not well supported.