Yes, failed login attempts should be logged:
- You want to know when people are trying to get in
- You want to understand why your accounts are getting locked out
It's also very important - older Windows logging process never emphasized this enough - to log successful login attempts as well. Because if you have a string of failed login attempts, you really really really should know if the last one was followed by a successful login.
Logs are relatively small. If there was enough login attempts that logging would cause a problem, then "not knowing about the attempts" is probably a worse-case problem than "found out about them when we ran out of disk."
A quick caveat - as @Polynomial points out, the password should not be logged (I seem to recall that 25 years ago some systems still did that). However, you also need to be aware that some legitimate login attempts will fail when people enter their password into the username field, so passwords do get logged. Doubt me? Trawl your logs for Windows Event ID 4768:
SourceName=Microsoft Windows security auditing.
TaskCategory=Kerberos Authentication Service
Message=A Kerberos authentication ticket (TGT) was requested.
Account Name: gowenfawr-has-a-cool-password
Supplied Realm Name: TEST.INT
User ID: NULL SID
Correspondingly, you should limit access to these logs to the necessary people - don't just dump them into a SIEM that the whole company has read access to.
Update to address question edit:
Based on the answers so far, one other question that occurred to me is
whether web server logs would be enough for logging such attempts.
Would it be redundant to log them in the database?
Best practices are that logs should be forwarded to a separate log aggregator in any case - for example, consider PCI DSS 10.5.4. In practice, such an aggregator is usually a SIEM, and functions like a database rather than flat log files.
So, yes, it's "redundant" by definition, but it's the kind of redundancy that's a security feature, not an architectural mistake.
The advantages of logging them into a database include searching, correlation, and summation. For example, the following Splunk search:
source="/var/log/secure" | regex _raw="authentication failure;" | stats count by user,host
Will allow us to roll up authentication failures by user and host:
Note that the ability to query discrete fields like 'user' and 'host' is dependent upon the SIEM picking logs apart and understanding what means what. The accessibility of those fields here is a side effect of Splunk automagically parsing the logs for me.
Given that your original question dealt with space constraints, it should be pointed out that any database or SIEM solution is going to take more disk space than flat text file logs. However, if you use such a solution, you'll almost always put it on a separate server for security and space management reasons. (There are even SIEM-in-the-cloud solutions now to make life easier for you!)