This psalm was written by David after his affair with Bathsheba (and subsequent murder of her husband Uriah), the low point of his life.
The backstory for this Psalm is in 2 Samuel 11, David at the height of his power. Everything he touched, he conquered. Everyone loved him; He was God’s king. And then the cheese starts to slide off his cracker. Verse 11:1 says that in the spring when kings would go out to war, David sent his troops out and stayed behind. Instead of trusting in God, walking in obedience, David has begun to trust in himself (Psalm 49). This trust in himself in terms of his kingly duties begins David’s exchange of shepherds. The man who wrote “the Lord is my shepherd” is now shepherding himself. So he see Bathsheba bathing, finds out she is married, and decides to sleep with her anyway. He’s the king, right? He’s in charge, he can do what he wants.
Then Bathsheba gets pregnant. Here is the perfect time for David to seek counsel, to seek the Lord, to stop being his own shepherd. But instead he persists in the self-trust of Psalm 49 and decides to "handle it” by trying first to deceive Uriah and then having him killed in the line of duty. And he tells his general Joab, who had Uriah killed, not to worry about it or feel guilty.
At this point, virtually no one in David’s kingdom knows anything about his sin. And for months, David might feel like he has gotten away with it. Bathsheba is pregnant, gives birth to a son, and David is going about his business, probably attending worship, and God has not called him to account. He’s living out Psalm 50:21 – he’s taking God’s silence over the course of 9 or 10 months as approval.
He’s about to find out that he is badly mistaken.