Tldr:

Rootful podman with podman run --userns=auto is more secure than one rootless host user running many pods, because those pods could (theoretically) attack each other.
though you still have the possibility of an exploit in the image pull

Rootless podman running one pod (as in service including database and so on) per host user with different subuid Ranges is the most secure, but you have to actually set that up which can be a lot of work depending on distribution.