Re: Req for some help in figuring out table create grants

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On Apr 27, 2020, at 4:22 PM, Tharmarajah, Sam <sambavan.tharmarajah@xxxxxxxx> wrote:

Now when I create a user with no privs and grant only these roles with inherit, what does allow the user to create a table in the public schema? Shouldn’t these privs limit the table creation for the user?

 

If I want to limit the user’s ability of operation to the role, that were granted, how do I accomplish that?

 

There is a special public role that all users belong to.  The public schema is setup already with grants to the public role.  You would have to remove privileges from the public role to public schema. 

i.e. 

revoke create on schema public from public;

etc.

Personally, I drop the public schema and create users with noinherit option. 

Here is a good read by Tomas Vondra from 2ndQuadrant on the topic:

https://www.2ndquadrant.com/en/blog/auditing-users-and-roles-in-postgresql/