If I may, I'll abstract your question into "I have something short and secure that needs to be stored - where can I store it?"
Secret Manager and RDS are both legitimate places to store small things. Other places to store stuff include: the onboard hard drive, Dynamo Db, and S3 buckets.
You mention that the tokens are discarded pretty quickly - the onboard hard drive might be a fine place to store this ephemeral data (but it doesn't scale out to multiple uses very well!)
Dynamo Db is VERY quick and can handle lots of different AWS services writing and reading at once - if you have a situation where a single user is getting access tokens to a dozen different services within seconds, concurrency is going to be a concern and Dynamo Db (also RDS) will handle this well. If you have a situation where LOTS of microservices hit APIs and rely on the access token, scaling is going to be a concern, and Dynamo Db will handle this well. MAKE SURE YOU DELETE THE OLD Access Tokens after a time.
S3 can handle scaling out very well - but you will need to architect for concurrency. That is, you should have a specific bucket and "folder" (S3 is key value, folders in S3 aren't real) for the Access Tokens, and have a separate file for each user. A single file will contain each of the Access Tokens that user has logged into - and then on your software side you have to avoid reading and writing the file when a different service is about to read/write. Avoiding concurrency isn't a big problem if you have infrequent writes from 3-5 services, but it will be difficult to handle if you have dozens of services that are writing often (which is why you should have a separate file for each user). Also, make sure you clean up old Access Tokens when you are done with them.
