Sounds like you have the relay wired NC

Check out this photo
12 VDC Automotive 5-Pin Relay SPDT 30/40A Bosch Type 330-073

Look at this relay you will two lugs 87A and 87

I think you have the pump wired to 87A

It needs to be on lug 87

The path to the pump is from lug 30 (battery) through the contact connecting lug 87A, ignition switch off. Pump will run when battery is connected. Ignition switch on the dotted line pushes the contact up and opens the circuit, no power for the pump

When you turn on the ignition switch that dotted line pushes the contact up to lug 87 now making the path, This is where you want the pump wired.

Hope this will clear the fog, your close just need to move a wire.