Re: Restricting user to see schema structure

Hi all,

Thanks for your suggestions, I would like to define my problem a little
more.

I am using pgAdmin . I have a database 'db' and it has got 2 schemas
'schema1' and 'schema2', I have created some views in schema2 from tables
of schema1. I have created a new user and granted connection access to
database and granted usage on tables and views of schema2 only. But now the
problem is that the new user is able to see the table names of schema1 even
though the user cannot see the data present in them they can see the table
names.Is there any way I can completely hide schema1 from the new user.

Thanks & Regards
Neeraj

On Fri, May 13, 2022, 09:40 Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> *tgl(at)sss(dot)pgh(dot)pa(dot)us <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:*
>
> *bryn(at)yugabyte(dot)com <bryn(at)yugabyte(dot)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.
>