The colour model of PDF is extremely sophisticated and bewilderingly flexible. You can read all about it in section 8.6 of the pdf specification (you can download it here). There are a number of prepress tools that exist for managing a colourspace workflow with PDF. Adobe Acrobat Professional has an "output preview" tool which is what I use.
PDF supports many colour spaces: gray, cmyk, rgb, indexed, LAB, deviceN. This allows for things like spot colours (and tints thereof), special metallic or varnish inks, 6-colour printing, duotone, multitone, registration colours, and so on. You can have multiple such colour spaces within the same PDF (although for printing you'll need to convert to some common space, at least for each page). There can even be multiple variants of the same colour space family (for example sRGB and the variant of RGB that your scanner or digicam uses; cmyk with different dot gains, etc).
The situation gets even more complicated when you involve transparency (see section 11.7 of the spec): in order to overlay one object on another the PDF viewer needs to convert them to a common "blending" colour space, which can be specified in various ways in the PDF (including at the "page group" level: this explains why including transparency on a page can change the appearance of other objects on that page, because those objects now have to run through a colour space conversion). There are various restrictions and special cases, for example device colours cannot be converted reliably into CIE based spaces (such as sRGB). Since transparency groups can nest, there can be multiple rounds of colour space conversion during rendering.
TeX support
Now for latex support via xcolor: If you load xcolor with the (default) natural option there will be no colour space conversions, and you will have access to the PDF "DeviceRGB", "DeviceCMYK" and "DeviceGray" spaces. Eg, \textcolor[rgb]{1,0,0}{DeviceRGB red}, and \textcolor[cmyk]{0,1,1,0}{DeviceCMYK Red}. Your PDF viewer (for screen) or your printer driver (for hardcopy) will have to convert colour spaces if necessary. If you load xcolor with options like rgb or cmyk then xcolor will convert to that colour space, using the formulas from section 6.3 of the xcolor manual (which are not very sophisticated -- compare them to the formulas in 10.3 of the PDF spec, with BG(k) and UCR(k) functions, etc).
If you use the dvips route to producing PDF then you may be able to access also spot colours and other colour spaces, using the named and ps models. I believe Context makes these things somewhat easier, but I don't speak from experience.
Remember that converting device colour spaces to the screen colour space is in general not well defined, and different viewers may do it slightly differently, resulting in the mismatches you've observed.
One good solution with pdflatex is to use only deviceRGB (for screen) or deviceCMYK (for most printers) and then set an "Output Intent" to define the device space as, eg, sRGB IEC61966-2.1. See section 14.11.5 of the PDF spec. The pdfx package does this, for example (although that package is pretty rough around the edges and you'll generally need to edit it directly to get it to work). A more "proper" way to use calibrated colour spaces would be the "Default Color Spaces" mechanism (section 8.6.5.6) which specifies a way to remap DeviceRGB, DeviceCMYK, DeviceGray into device-independent CIE-based colour spaces. I'm not aware of any latex package that makes use of this PDF1.1 feature, though. (Grepping through the sources suggests that again Context may have some support for this).
The case of the different coloured swatches
In your first pair of images, note that the left image has a gray triangle in the upper right corner. This indicates that it is a deviceRGB colour from a screen grab. Click on the icon to the left of the "HSB Sliders" indicator and choose the colour space as for the right swatch, and you'll see that the colours will then match.
The case of inkscape "autocorrecting" CMYK
Perhaps this article will be helpful.
These option clashes happen when a package is requested to be loaded on two different positions, like by you and inside another package, but with different options. The package is loaded by the first \usepackage (or its twin \RequirePackage); it isn't loaded again when it is requested again. It simply can't be loaded a second time. Therefore the new options can be activated and previous options might conflict with the second usage. So LaTeX creates an error to report this issue to you.
The way to fix this is to declare the options beforehand using \PassOptionsToPackage{<options>}{<package>}. Then they are used wherever the package is loaded.
\documentclass{article}
\usepackage{ifpdf}
\ifpdf
\PassOptionsToPackage{pdftex,usenames,dvipsnames}{color}
\usepackage[T1]{fontenc}
\usepackage{libertine} % most likely loads 'color' itself
%\usepackage{lmodern} % doesn't load 'color'
\usepackage{color}
\usepackage[pdftex]{graphicx}
\else
%\bye
\fi
\begin{document}
Hello, world!
\end{document}
I can't test it by myself, because I don't have the libertine package installed.
PS:
I don't think you need to set pdftex manually. Normally packages do a good job recognizing the driver by themselves. Also you might want to use the extended xcolor package instead of color.
Best Answer
This is not a perfect answer, but I did find a work around to my problem. Instead of defining the color using CMYK values, I tried defining it using RGB values:
The original definition has RGB values (149,172,207) in terms of 0-255 scale. The rendered color is the same as I wanted; I opened the document with Photoshop, and the Photoshop says that the rendered color has RGB values exactly (149,172,207).
So my conclusion is that the 'color' package is not perfect in rendering colors using CMYK values.
Further experiment, however, shows that not all RGB definitions work well. It seems that the definitions using the RGB values for the so called web-colors work best.