Re: Role Permissions are not inherited to the new objects

On Mon, 2020-02-17 at 14:24 +0530, Rajin Raj wrote:
> Permissions are not inherited to the new objects. I have followed the below steps,
>
> 1. Created a new role "user_role";
> 2. Created a new schema "test_schema";
> 3. Granted SELECT ON ALL TABLES IN SCHEMA "test_schema" to "user_role";
> 4. Grant "user_role" to user_1 and user_2;
> 5. Create a new table "T1" in "test_schema" using user_1 id.
> 6. Now login with user_2 and try to query the "T1" table.
>
> Getting permission denied error.
>
> But, if I execute the grant command (step 3) after creating the table (step 4), this issue is not happening.
>
> Is there any setting in pg to inherit the permissions given at top-level or refresh the permissions automatically?

To set permissions for objects created in the future, use
ALTER DEFAULT PRIVILEGES.

Be aware that you must run one ALTER DEFAULT PRIVILEGES for each
role that can create tables (keep that number low).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com