It appears the rotate function in PostGIS only rotates the geometry about the center of the co-ordinate systems. There was a neat little trick that was mentioned on the postgis-list by Bruce Rindhal to rotate a polygon about its center:

CREATE TABLE shapes (gid serial PRIMARY KEY,  name varchar);
SELECT AddGeometryColumn('', 'shapes', 'the_geom', -1, 'POLYGON', 2);
INSERT INTO "shapes" ("the_geom", "name") VALUES (
       'POLYGON((2 2, 2 7, 12 7, 12 2, 2 2))', 'laying down');

Next we are going to translate the polygon’s centroid to (0,0). Rotate it by 45 degrees and then move it back to the original centroid.

SELECT
translate(
  rotate(
    translate( the_geom, -x(centroid(the_geom)), -y(centroid(the_geom)) ), 
    radians(45)
  ), 
  x(centroid(the_geom)), y(centroid(the_geom)) 
)
FROM shapes WHERE name = 'laying down';

postgis rotation
Without the translate on line 4 and line 2 and 7 it would look like this:
postgis rotation without translate