SuperDrive ejects all discs after a few seconds on iMac 27-inch

The Apple SuperDrive is notoriously power-hungry. It requires 900mA of power to spin up and read a disc, which is significantly more than standard USB devices. 

• Avoid third-party cables/hubs: The SuperDrive has a built-in, non-removable USB-A cable. If you are replacing that connection with a separate "USB-C to USB-A cable," you might be trying to extend or adapt it incorrectly. 
• The fix: Plug the SuperDrive's captive USB-A cable directly in the Mac. If your Mac does not have usb A ports, use an official apple usb C to USB A adapter. For me, that was the fix to use the superdrive with my M1 iMac

1) Make sure that the Apple SuperDrive is right side up.

2) Connect the Apple SuperDrive directly to the iMac instead of thru a USB hub.

Test the Apple SuperDrive in different iMac port using a USB adapter if necessary.

3) Return that Apple SuperDrive and buy an aftermarket (LG, ASUS, Verbatim, OWC) CD/DVD drive.

cd/dvd drives for mac

I have two original Apple Superdrives and when connected to Macs, they simply work.

(1) Is your a true Apple Superdrive, there are many drives labeled as "Superdrives" sold on Amazon and elsewhere but many of them are not actual Apple Superdrives.

(2) Yours is "refurbished," (Amazon calls it "renewed") which may mean it broke and now has presumably been fixed. By Amazon, in your case. Or maybe it is simply pre-owned and used by someone else previously. Unclear to me why someone would be getting rid of a good, working Superdrive.

(3) Perhaps purchasing a new external device capable of writing to CDs and DVDs makes more sense. See den.thed's suggestions.

(4) I believe that Amazon "renewed" products can be returned within 90 days if they are not working. Yours is not working.

Also, Apple says this on How to connect the Apple USB SuperDrive - Apple Support:

Connect your SuperDrive

To use your SuperDrive, connect it to a USB-A port on your Mac, then insert a disc. Make sure that the aluminum enclosure of the SuperDrive is facing up.

To connect your SuperDrive to a Thunderbolt 4, Thunderbolt 3, or USB-C port on your Mac, you can use one of these adapters: 

• USB-C Digital AV Multiport Adapter
• USB-C VGA Multiport Adapter
• USB-C to USB Adapter

You indicated "I'm using a new USB-C to USB-A cable to the superdrive" but since a true Apple Superdrive has a built in cable, this leads me to think you don't have a genuine Apple Superdrive. Note that Apple suggests specific adaptors to use with its Superdrive.