I've noticed that most FOSS applications (Wordpress, for example) only uses a single set of database credentials that have been granted all permissions. This seems like it violates the principle of least privilege.
In writing such an application, would it be better to use several accounts, for example, an account only for SELECT queries, another for UPDATE, etc?
This is definitely a violation of the principle of least privilege. Let's go back to the definition:
In information security, computer science, and other fields, the principle of least privilege, also known as the principle of minimal privilege or just least privilege, requires that in a particular abstraction layer of a computing environment, every module (such as a process, a user or a program on the basis of the layer we are considering) must be able to access only such information and resources that are necessary to its legitimate purpose.
In your Wordpress example, a public user is retrieving data from the database with a SQL account which also has the ability to change or delete that data. The "least privilege" for this user would not include access to change that data whether it be directly on the table of via a stored procedure. This is definitely not compliant with "access only such information and resources that are necessary to its legitimate purpose".
The risk in a SQL environment is primarily SQL injection. One little flaw and if that public account has the rights to do damage then you end up with all sorts of problems. Yes, input should be validated, yes queries should be parameterised but this is one additional layer of defence that gives you some extra insurance.
I talk about this specifically in OWASP Top 10 for .NET developers part 1: Injection.