I am trying to run an application in a specific search_path in postgres. I have a connection string like this: postgres://xxx:xxx@localhost:5432/prod_pg?options=-c%20search_path%3Dfoo.
And this application, which I do not control the source, has a query in pg_class:
SELECT reltuples FROM pg_class WHERE relname = 'users'
Which is problematic, as I have 10 schema name in this database, and 8 of them has a table called users. And searching like this bypass limitation of search_path.
I wonder how can I limit a role to query only rows in its search_path in pg_class? Is row-level-security an option for system tables in pg_catalog?
No, there is no way to automatically add AND relnamespace = 'myschema'::regnamespace to a catalog query like you want.
If you created these multiple schemas because you wanted to run several instances of the same application in a single database, the fault is yours. Instead, you should have created several databases. Each of these databases has its own catalog tables, and your problem would vanish. Moreover, this architecture provides better security, since an attacker who manages to break into one instance of the application cannot access the data of the other instances.
If these multiple schemas are there by design of the application, the query is faulty, and you'll have to file a bug report with the application vendor.