TL;DR: Yes, it probably improves security, but you could do even better with a "real" password manager.
Long version:
Firefox's "remember password" combined with a master password is the equivalent of a cloud-based password manager with excellent browser integration, local encryption, and automatic sync, but lacking pretty much any other features. Here's why I say that:
Firefox Sync stores your passwords on Mozilla servers, but it does not store them in plaintext. They are encrypted locally using a key derived from your Firefox Sync password; the Mozilla servers never get the plaintext passwords. When you log into Firefox on another device, your encrypted passwords are downloaded, and decrypted locally. This is similar to popular cloud-based password managers like LastPass.
Since your passwords are encrypted locally before sending them to the server, Mozilla cannot access them. Thus, when you forget your Firefox Sync password, you lose any data stored on Mozilla's servers, so you'd better hope you still have a local copy. Again, this is consistent with any good password manager.
Where Firefox differs, is that its passwords are actually stored locally in unencrypted form by default. That's where the master password comes in. Like the sync password, the master password never reaches Mozilla; it is used only locally, to decrypt the local data for use. There are some concerns that the PBKDF used for the master password is not as strong as that used for the sync password, so it is potentially vulnerable to dictionary attack; but a strong, randomly-generated password or passphrase should make up for that.
The master password also encrypts saved Firefox Sync credentials, so you still only need to enter a single password to get access to all of the password manager features in Firefox, even though under the hood the Sync password is separate from the master password. However, this does mean you need to set up your master password separately on every device. The master password is not synced between devices, it is only used to encrypt the data locally. This makes Firefox's password manager features a little easier to screw up than dedicated apps.
Now, when I say it's equivalent to a cloud password manager, that means a few things:
Pros
- You have an online backup of all your passwords
- You can set up hundreds of accounts with unique, fully random, max-length passwords (and you don't need to remember them). You are less vulnerable to server breaches.
- Your passwords will be automatically entered for you, but only when the website is actually the correct website. You are less vulnerable to phishing.
- You can conveniently log in from any device where you can log into Firefox.
- You can use Firefox on your mobile device to view your saved passwords, when you can't log into Firefox on the device you are using for some reason.
Cons
- All your passwords can be compromised, if malware on your machine can capture your saved passwords and master password; or if it can access the memory of Firefox. While a key logger can certainly capture logins when you don't use a password manager, you are less likely to lose all of your passwords that way. Thus you are more vulnerable to malware on your computer. This is the "all your eggs in one basket" problem, so be sure to secure your basket with a very good master password.
- Using hard-to-remember passwords (normally a good thing) means that you will probably become reliant on the saved passwords, which could leave you unable to log into devices you don't normally use.
One of the big things you point out, is that someone sitting down at your computer when you step away, could view your saved passwords still, if you leave yourself logged into Firefox with your master password. This is a shortcoming most password managers can mitigate with a timeout period that locks the password database. There are extensions for Firefox that can do the same thing.
So, should you use it?
That's really up to you, but I (and people smarter than me) certainly recommend using a password manager. I prefer a more fully-featured password manager (specifically, KeePass) but a properly configured Firefox with a strong master password and a strong sync password should do the job. If the choice is between Firefox with a master password, vs. no password manager at all, I think you should go with Firefox. It encrypts all the data in smart ways that prevent anyone (including Mozilla!) from getting it in case of a stolen device or server breach, yet still provides the convenience needed to create and use strong passwords everywhere with very little effort.
But really, I think features you can get from "real" password managers, like strong password generation, non-browser passwords, secure storage of non-password data, linking sites together that share credentials, multi-browser support, etc. are worth trying out if you're willing to step outside of the browser.