First of all, in ATG the Profile's password is hashed to prevent any malicious attacker from reading it in clear text. Depending on your ATG version it will be hashed in MD5 or SHA-1 plus some salt, so you will NOT be able to see a clear text password.
Second, why do you need to access the password? If you have a specific requirement, such as logging the user in, post it and I will be able to assist.
That said, assuming that you have a valid scenario where you need to access the hashed password, how to do it depends on whether you just need the password of the user that is logged in, or some other user.
For the currently logged in user, you just need to resolve the /atg/userprofiling/Profile component, and do
String password = (String) profile.getPropertyValue("password");
Obviously the "password" string should replaced with a constant or with the Profile propertyManager, depending on your project's coding practices.
If you want to access the password (remember, you can't access the clear text password, only the hashed version of it) of any other user, you need to locate the user first. The /atg/userprofiling/ProfileItemFinder component has tools to help you with these, so you'll have to inject it into whatever component you are writing:
RepositoryItem user = profileItemFinder.findByEmail("your@email.com", "user")[0];
String password = (String) user.getPropertyValue("password");
Hope this helps.
Edit
If all you have is the profile id, you can use the following snippet after injecting the /atg/userprofiling/ProfileTools component:
Profile user = profileTools.findProfile("profile id");
String password = (String) user.getPropertyValue("password");