Parece que a função rotate no PostGIS gira a geometria somente sobre o centro do sistema de coordenada. Havia um truque que foi mencionado na lista do PostGIS por Bruce Rindhal para girar um polígono sobre seu centro:

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');

Em seguida fazemos um translate no centróide do polygon para (0.0), o giramos 45 graus e movemos para baixo (para o centróide original).

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';

Veja como ficou:

Antes Depois
Polígono Antes Polígono Depois

Fonte: GeoLabs