Why is alcohol such a weak drug?
As Christiaan already pointed out, this depends on the pharmacokinetic and pharmacodynamic parameters. The effective drug concentration depends on the drug-receptor association constant and the drug degradation constant (for a highly simplified model). The degradation/removal depends on the abundance of the enzyme catalyzing this reaction. 

Ethanol is primarily thought to cause intoxication by activation of GABAA receptors. However, only a very specific subclass of these receptors are activated at the levels of alcohol that one consumes during "social/moderate" drinking. Others require very high alcohol concentrations (Hanchar et al., 2006). Basically alcohol is not a very specific agonist/antagonist of any neurotransmitter or neuromodulator receptors. In other words, it does not have high affinity to any of these receptors. There is no answer to "why". I would guess that since ethanol is a very common molecule (and is also used as energy substrate by some organisms, to a certain extent), high sensitivity to ethanol would have been evolutionarily selected against. 


  Although many proteins show changes in their function at very high
  alcohol concentrations (>50 mM), the molecular basis for behavioral
  alcohol effects at low to moderately intoxicating doses experienced
  during social alcohol consumption remains elusive (1). GABAA receptors
  (GABAARs) and the inhibitory GABAergic system have long been suspected
  to be targets for acute alcohol effects (2–4). For example, the GABAAR
  agonist muscimol potentiates the sedative actions of alcohol, whereas
  the opposite effect, a reduction of ethanol (EtOH)-produced sedation,
  is detected with the GABAAR blockers picrotoxin and bicuculline (5).
  Although most GABAAR subunit combinations can be activated by high
  (anesthetic) alcohol concentrations (6), only very specific GABAAR
  subunit combinations (containing the δ as well as the β3 subunit)
  exhibit dose dependencies that mirror blood alcohol levels that are
  associated with mild to moderate intoxication in humans (7, 8) (≈3–30
  mM, because the legal drinking limit is 17 mM or 0.08%).
  
  From Hanchar et al., 2006


Other psychoactive drugs are highly specific and have high affinity to their corresponding receptors. For example, caffeine acts on the adenosine receptors at µM concentrations (Daly et al., 1983). This is because of caffeine's high affinity to these receptors.

Moreover, ethanol is readily metabolized to acetyl-CoA by alcohol dehydrogenase and aldehyde dehydrogenase enzymes which are quite abundant in the body. Some people who have mutations in these enzymes leading to their reduced activity, "get drunk" and experience hangovers even with a low level of alcohol consumption.