Your example is obscuring the issue by incorporating a tenant_name condition (where did that come from, anyway?) in one case and not the other. Without knowing how selective that is, it's hard to compare the EXPLAIN results.
That's RLS kicking in - RLS condition is defined as
((tenant_name)::name = CURRENT_USER)
However, wild-guess time: it might be that without access to the table statistics, the "search like '%yo'" condition is estimated to be too unselective to make an indexscan profitable. And putting RLS in the way would disable that access if the ~~ operator is not marked leakproof, which it isn't.
I didn't realize you could set access to table statistics. How do I enable this access for this user? If that's not possible, it sounds like it effectively blocks the use of GIN/GIST indexes when RLS is in use.
I'm not sure that you should get too excited about this, however. You're evidently testing on a toy-size table, else the seqscan cost estimate would be a lot higher. With a table large enough to make it really important to guess right, even the default selectivity estimate might be enough to get an indexscan.
I've tried this with larger data sets, with the same results. I discovered this problem because the select was taking 10-30 seconds instead of the expected sub-second, when using larger data sets and more fields getting searched. The example is the simplest repro case I could create.
