It can't be done fully automatically. But you can have the Auto Lock menu open automatically when you connect to CarPlay, and again when you disconnect CarPlay. It also requires that your iPhone is unlocked when you connect/disconnect.
If you want to try it out, do this:
1. Open the Shortcuts App
2. Tap Automation in the bottom bar
3. Select CarPlay from the list
4. Select When Connecting or Disconnecting.
5. Check "Start immediately"
6. Tap "Create New Shortcut"
7. Search for and select "Open URL"
8. In the url field copy/paste: prefs:root=DISPLAY&path=AUTOLOCK
9. Save the automation, done.
Now the Auto Lock menu will open automatically when you connect to CarPlay. From there you can quickly select "Never" - set another automation for when CarPlay disconnects, so that Auto Lock also opens when you disconnect from CarPlay. Same guide.