I know that "that" can function as many different parts of speech, so what part of speech is it in the phrase "the stuff that dreams are made of"?
ODO believes it to be a relative pronoun:
that pronoun (plural those)
5 (plural that) [relative pronoun] used to introduce a defining clause, especially one essential to identification:
• instead of ‘which’, ‘who’, or ‘whom’:
the woman that owns the place
• instead of ‘when’ after an expression of time:
the year that Anna was born
The example “instead of which, who or whom” exactly matches your sentence.
"That" introduces a sentence complement to a noun, as in "the fact that we left", where the sentence "we left" is complement to "fact". When the complement is a relative clause, there is a common alternative analysis that makes "that" a relative pronoun, yet this is not right, because relative pronouns need not occur at the beginning of a relative clause. "That" in relative clauses, unlike relative pronouns, always comes at the very beginning of the clause. Compare, "the facts with which we are familiar", *"the facts with that we are familiar", "the facts that we are familiar with".