What you see on TV is entirely fictionalized. Nothing like what happens in Castle or Bones or The Mentalist happens in real life. Any remotely competent defense attorney would tear apart a case where an untrained civilian did half of the investigative work, handled evidence, etc. And that's not even counting putting a civilian in the line of fire on purpose.
However, "police consultant" -- the job title that the civilian half of these shows typically claim -- is a real position. Police departments have pretty broad leeway to hire civilian experts to assist in investigations. That even includes paying the occasional psychic (for a while, those occasionally popped up in missing-persons cases).
These police consultants are there to offer advice and information on topics that the police aren't familiar with. However, you would never see them going out with armed officers apprehending a suspect. Typically, what these consultants do include things like:
- Examine crime scenes and try to provide information to help the police determine likely suspects.
- Provide assistance on technical aspects of a case to help police identify useful evidence
- Testify in court in topics that fall within their area of expertise.
And yes, these people are paid for their work; in the case of expert testimony, their fees are a matter of public record (since being paid to testify calls into question their impartiality); other consultants are paid whatever hourly rate they can get the police departments to agree to.
To relate this back to TV shows: if you eliminated every instance where she accompanies Booth in the field, Brennan and her team from Bones are all reasonable approximations of what a civilian forensics lab would do for the police. A lot of scientific and forensic work is done by private labs, because most police departments can't afford to do their own. (The FBI actually has it's own labs, though, for routine stuff like DNA, bloodwork, drug testing, etc.)
The Mentalist (or it's far better sibling Psych) are pushing the boundaries of what a police department would even consider. It wouldn't be unusual for police to hire someone who does what Patrick Jayne really does -- criminal profiling -- but they would hire him as a profiler. (And again, a state-level police force might already have their own.) It's unlikely they'd hire someone who admits that he's a con man, as that wouldn't tend to go over very well in front or a jury.
The very first episode of Castle is a bit of a stretch, but not unreasonable to think that police might contact someone they think has relevant information related to a series of crimes, and request their assistance in an advisory role for that specific investigation. And it would mostly involve the detectives interviewing him a lot, not standing around the crime board theorizing with him. (The idea that a homicide detective would let a writer follow her around for 4 years and conduct interrogations with her is beyond absurd.)
As several people in the comments have pointed out, there is a second option here, as seen in shows like Monk, or the most recent seasons of Castle: that the "consultant" is a licensed private detective. The rules for PIs vary in the US from county to county, but in general, they're required to obtain a license to operate, and have to follow some (but not all) of the rules that police detectives do. My understanding is that PIs usually work for attorneys moreso than the police, but that they are sometimes brought it on capital cases where the police department may be understaffed.
In theory, their training and certification requirements make them better equipped to handle evidence, interview witnesses, and in some cases even detain suspects. They are often allowed to be armed beyond what local laws allow for private citizens (again, varies from place to place). Having not seen Monk myself, I don't know how realistic the show's depiction is, but a PI would most likely be hired on a case-by-case basis, rather than as a permanent addition to a homicide team.
