The remarkably smooth, satiny finish you see on professionally decorated cakes is usually achieved with fondant, a special type of frosting with a consistency resembling modeling clay. Fondant isn't applied directly to cakes, but instead usually goes on over a coat of buttercream. By smoothing any irregularities in the cake's surface, binding up loose crumbs and concealing the cake's color, this layer of buttercream contributes significantly to the cake's final appearance.
The Best Option ...
The best buttercream for the purpose is one that can be applied in a very smooth, even coat, which in turn makes the fondant look smoother. However, it should also "crust" under refrigeration, forming a firm and slightly hard surface that won't shift around as you smooth the fondant. A simple "American buttercream" or "confectioner's buttercream," made with butter, confectioner's sugar and a splash of milk and vanilla, often provides the most practical choice. When freshly made and well whipped, it's very spreadable. Yet after you've refrigerated the covered cake for an hour or two, it sets very nicely.
... and Your Alternatives
As you gain experience in cake decorating, you might come to favor other alternatives. For example, meringue-based Swiss or Italian buttercreams don't crust as well but can be brought to a very smooth finish. Hot-climate bakers favor shortening in their icing rather than butter, because it's more stable when warm. Another useful alternative is a glaze of ganache, a mixture of roughly 3 parts by weight of chocolate or white chocolate to 1 of hot cream. Ganache sets hard and provides a fine base for the fondant.