Tarkin respected both Darth Vader and Darth Sidious. He didn't know for sure that they were Sith Lords, but he suspected it. He agreed with their political goals and willingly served Sidious.
Tarkin's thoughts are best explained by the appropriately named canon novel Tarkin.
Tarkin did not know for sure that Sidious and Vader were Sith Lords, but he suspected that they were:
There were many stories about what had occurred that day in the chancellor’s office. The official explanation was that members of the Jedi Order had turned up to arrest Supreme Chancellor Palpatine, and a ferocious duel had ensued. The matter of precisely how the Jedi had been killed or the Emperor’s face deformed had never been settled to everyone’s satisfaction, and so Tarkin had his private thoughts about the Emperor, as well. That he and Vader were kindred spirits suggested that both of them might be Sith. Tarkin often wondered if that wasn’t the actual reason Palpatine had been targeted for arrest or assassination by the Jedi. It wasn’t so much that the Order wished to take charge of the Republic; it was that the Jedi couldn’t abide the idea of a member of the ancient Order they opposed and abhorred emerging as the hero of the Clone Wars and assuming the mantle of Emperor.
Tarkin, p. 77
Tarkin and Palpatine go back awhile -- they are from nearby planets in the galaxy and they formed a friendship while Palpatine was a senator. They were even on a first name basis, which is how we learned of Palpatine's ridiculous first name:
[Palpatine] fixed Tarkin with a gimlet stare. “Perhaps you and I could serve each other, as well as the Republic, by taking Valorum down a notch.” His shoulders heaved in a shrug of uncertainty. “With the backing of your family, you may not even need our help, but rest assured that we will bolster you if necessary.” Palpatine quirked a sly smile. “You will be Eriadu’s finest leader, Wilhuff.”
“Thank you, Sheev,” Tarkin said, with obvious sincerity, and using Palpatine’s given name.
Tarkin, p. 93
Later, after the Empire was formed, they were still on very friendly terms:
[Palpatine] “We’ve come far, you and I.”
[Tarkin] "My lord?"
[Palpatine] “Twenty years ago, who would have thought that two men from the Outer Rim would sit at the center of the galaxy.”
[Tarkin] “You flatter me, my lord.”
Tarkin, p. 76
Tarkin respects Vader, too:
Very early on in their partnership—soon after both had been introduced to the secret mobile battle station—Tarkin grew convinced that Vader knew him much better than he let on, and that behind the bulging lenses of his face mask, whatever remained of Vader’s human eyes regarded him with clear recognition. More than anything else it was those initial feelings that had provided Tarkin with his first suspicion as to Vader’s identity. Later, observing the rapport the Dark Lord shared with the stormtroopers who supported him, and the technique he displayed in wielding his crimson lightsaber, Tarkin grew more and more convinced that his suspicions were right. Vader might very well be Jedi Knight Anakin Skywalker, whom Tarkin had fought beside during the Clone Wars, and for whom he had developed a grudging appreciation.
Tarkin, p. 73
When Tarkin was promoted to Grand Moff, he displayed obedience and recognized that the Death Star would not be the ultimate power in the universe:
“We need to tighten our hold over the Outer Systems,” the Emperor continued. “You will be in charge of that, Moff Tarkin. Or should I say Grand Moff Tarkin.”
Tarkin’s gaped in genuine surprise. “Grand Moff?”
“The Empire’s first.” The Emperor spread his sickly hands. “Was it not you who suggested the creation of oversectors and oversector governance as a means of enhancing our control?”
“It was, my lord.”
“Then your wish is granted. The Outer Rim is yours to oversee—and with it, Grand Moff Tarkin, the whole of the mobile battle station project.”
Tarkin rose from his chair so he could bow from the waist in frank obedience. “I will not fail you.” When he looked up, he saw that the Emperor was leaning forward in his chair.
“It will be a momentous responsibility,” the Emperor said, drawing out the words. “For once the battle station is fully operational, you will wield the ultimate power in the galaxy.”
Tarkin’s gaze moved from the Emperor to Vader and back again. “I don’t believe that will ever be the case, my lord.”
Tarkin, p. 248
Finally, the Tarkin Doctrine (which Tarkin himself explained as a quote to the media) indicates that Tarkin fully supported the Emperor's political goals and respected them:
The factor that contributed most to the demise of the Republic was not, in fact, the war, but rampant self-interest. Endemic to the political process our ancestors engineered, the insidious pursuit of self-enrichment grew only more pervasive through the long centuries, and in the end left the body politic feckless and corrupt. Consider the self-interest of the Core Worlds, unwavering in their exploitation of the Outer Systems for resources; the Outer Systems themselves, undermined by their permissive disregard of smuggling and slavery; those ambitious members of the Senate who sought only status and opportunity.
The reason our Emperor was able to negotiate the dark waters that characterized the terminal years of the Republic and remain at the helm through a catastrophic war that spanned the galaxy is that he has never been interested in status or self-glorification. On the contrary, he has been tireless in his devotion to unify the galaxy and assure the well-being of its myriad populations. Now, with the institution of sector and oversector governance, we are in the unique position to repay our debt to the Emperor for his decades of selfless service, by lifting some of the burden of quotidian rulership from his shoulders. By partitioning the galaxy into regions, we actually achieve a unity previously absent; where once our loyalties and allegiances were divided, they now serve one being, with one goal: a cohesive galaxy in which everyone prospers. For the first time in one thousand generations our sector governors will not be working solely to enrich Coruscant and the Core Worlds, but to advance the quality of life in the star systems that make up each sector—keeping the spaceways safe, maintaining open and accessible communications, assuring that tax revenues are properly levied and allocated to improving the infrastructure. The Senate will likewise be made up of beings devoted not to their own enrichment, but to the enrichment of the worlds they represent.
This bold vision of the future requires not only the service of those of immaculate reputation and consummate skill in the just exercise of power, but also the service of a vast military dedicated to upholding the laws necessary to ensure galactic harmony. It may appear to some that the enactment of universal laws and the widespread deployment of a heavily armed military are steps toward galactic domination, but these actions are taken merely to protect us from those who would invade, enslave, exploit, or foment political dissent, and to punish accordingly any who engage in such acts. Look on our new military not as trespassers or interlopers, but as gatekeepers, here to shore up the Emperor’s vision of a pacified and prosperous galaxy.
Tarkin, p. 249