That's not what leadership is.
He is not the CnC or the President of the Federation. Even as a 4-star, you have superior officers and civilian leaders. If the President tells the 4 star to stand down, then you stand down. Yes, Mr President. That's not a failure of leadership. It does mean that if everyone knows that he did not make this decision, he is not to blame for it. That doesnt mean the 4-star doesnt feel bad about an executive decision that s/he strongly disagrees with, but it wasnt his decision to make.
The Romulans and the entire Federation knew he opposed this, knew that he staked his career on this, and knew he was overruled by his superiors, who accepted his resignation when he threatened it. Picard did not fail as a leader. He was overruled by his superiors. He doesnt make Federation policy.
They could and should have written that he is responsible for at least SOME of this. Then there is a meaningful atonement/redemption story. But not when they went out of their way to show how he had no role in causing any of this to happen.
It's been made clear that Picard was the man who Spearheaded the operation and got Starfleet command on board. That is demonstrable leadership. It was not it seemed Federation policy to save the Romulans. That was all Picard, as far as we've been told. So whether or not he made Federation policy, he certain got what he wanted and as far as we've been told, was made the leader of the rescue mission.
As far as we've been told, after the attack occurred, he failed to sway Starfleet to keep on the goal as they agreed. This is called managing upwards, and yes it is a form of leadership and in this he failed.
We have no indication that he was willing to accept a reduced rescue mission which might have still saved many millions, and his statements in the show suggest that he was only willing to accept the full rescue as he felt the Federation was obligated to commit to or nothing. And as of ep. 4, he appears to recognize this as a tragic mistake on his part, not to mention his 15 years doing nothing to help those he promised to champion. These are multiple failures on his part, which he now readily acknowledges.