Re: Restricting user to see schema structure

On Thu, May 12, 2022 at 11:37 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> *neerajmr12219(at)gmail(dot)com <neerajmr12219(at)gmail(dot)com> wrote:*
>
> *bryn(at)yugabyte(dot)com <bryn(at)yugabyte(dot)com> wrote:*
>
> What exactly do you mean by "have created a new user and granted
> connection access to database"? As I understand it, there's no such thing.
> I mentioned a simple test in my earlier email that showed that any user
> (with no schema of its own and no granted privileges) can connect to any
> database—and see the full metadata account of all its content. I'm teaching
> myself to live with this.
>
>
> What I meant by 'created a new user' is that I have used the following
> commands.
>
> CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';
> GRANT CONNECT ON DATABASE <database> TO <user_name>;
> GRANT USAGE ON SCHEMA <schema> TO <user_name>;
>
> In a freshly initialized cluster the newly created user will have already
inherited the necessary connect privilege making this one redundant (though
that property can be considered useful here).

Ah… there's obviously something I don't understand here. I've never used
> "grant connect on database"—and not experience an ensuing problem. I just
> tried this:
>
> \c postgres postgres
> create user joe login password 'joe';
> revoke connect on database postgres from joe;
> \c postgres joe
>
> It all ran without error. (I've turned off the password challenge in my
> MacBook PG cluster.) I don't have a mental model that accommodates this.
> And a quick skim for this variant in the "GRANT" section of the PG doc
> didn't (immediately) help me. I obviously need to do more study. I'll shut
> up until I have.
>

It's because joe hasn't been granted connect on the database directly. It
is through their mandatory membership in the PUBLIC pseudo-role, and that
role's default grant of connect on all newly created databases, that joe
receives permission to connect. You can only revoke what has been
explicitly granted so one must revoke the grant from PUBLIC - then
re-assign it to the subset of roles that require it.

https://www.postgresql.org/docs/current/ddl-priv.html

David J.