I would not interpret your son's statement as cause for thinking that he might "give up on being the best that he can be." He is six years old and in the first grade. He is not about to choose a life of purposeful underachievement at this age. His telling you that he does well in school only to please you has underlying reasons that you should explore.
He may indeed not want to continue to "do good" in school if his doing very well academically somehow means that his life is not the one he wants to lead. At this point, you may only conjecture why he doesn't want the life of a straight A, advanced student. Maybe his social life in school is suffering because classmates deride him for his academic excellence. Maybe he has drawn the conclusion that you won't appreciate him or love him as much unless he performs up to the expectations that you have articulated. Maybe his teacher is pushing him academically more than he wants. Here's a question he should be asked: "What would not "doing good" in school give you that you don't have right now?" Having a family focus on many matters, I wonder also if there have been any changes/problems/worries within the family that may be impacting your son, concerns that is unable to articulate.
I'd make it a point to have some casual conversations with him -- while travelling in the car, eating breakfast at a restaurant with him on a Saturday morning, or after you've read him a story. You might try role-playing with him, asking him to pretend to be you and his teacher, to see if he gives you any clues about his dilemma. He is, after all, telling you that he is giving you the gift of good grades, because he knows you want them. He did not make up the idea that good grades were very important to you. He had to hear that from you to consider it his duty. On the other hand, he is telling you that he feels like he has to get great grades, even though he doesn't want to. He is letting you know that he's going through some inner turmoil and confusion over his role as a student and his relationship with you.
My guess is that some non-pressured conversations with open-ended questions will get him talking about what's behind his dilemma. You might find it helpful to see a family therapist once, by yourself, to get some advice on how to frame your questions and for some insight about what could be behind his recent declaration. I'd welcome an update.