Re: Restricting user to see schema structure

tgl@sss.pgh.pa.us wrote:

bryn@yugabyte.com writes:

Maybe this entire discussion is moot when hackers can read the C code of PG's implementation…

We have pretty much no interest in revisiting that design choice, even if doing so wouldn't likely break a couple decades' worth of client-side software development.

Anyway, if you feel a need to prevent user A from seeing the catalog entries for user B's database objects, the only answer we have is to put A and B into separate databases.  If despite that you want A and B to be able to share some data, you can probably build the connections you need using foreign tables or logical replication; but there's not a lot of pre-fab infrastructure for that, AFAIK.

Thanks Tom. It certainly helps to know that nothing in how PG works in the space that's relevant here is going to change in my lifetime. (I just wrote exactly the same in reply to David Johnston.)

My sense is that the database is intended to be a hermetic unit of encapsulation and provides some of the features that multi-tenancy requires. But there's the caveat that users are defined, and operate, cluster-wide.

If a cluster has two databases, "app_1" and "app_2", each populated using the general scheme that I sketched, then users "client_1" and "client_2" (designed, respectively to let them operate as intended in their corresponding databases) could always connect each to the other's database. They couldn't do much in the "wrong" database. But they could certainly list out all the application's objects and the source code of all the application's user-defined subprograms.

In general, it's best to use any system in the way that it was designed to be used. And PG was designed to allow all users to see the metadata account of all of the content of every database in the cluster—but not to use any of the content unless this has been specifically catered for.