Re: Grants and privileges issue

On 3/28/24 2:10 PM, sud wrote:
> Hi, It's postgres 15.4.
> 
> We want to give required privilege to certain users or roles and ensure 
> to not to provide any elevated privilege. I have below questions,

I would suggest spending some time here:

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

It should answer many of your questions.

> 
> 1)I am seeing in many places, we have "usage on schema" privilege given. 
> along with "grant select on <object_name> to <role1>" for the objects of 
> the schema (something as below). So I wanted to understand, what exact 
> privilege "grant usage on schema <schema1> to <role1>" will provide 
> which the "select on" privilege won't?
> 
> grant usage on schema <schema1> to <role1>;
> grant select on  schema1.tab1 to <role1>;
> 
> 2)Additionally , when we are trying to give select privilege on "cron" 
> and "partman" schema to a role (something as below) , so that anybody 
> logging through that role would be able to see/fetch the data from the 
> tables inside cron and partman schema. its giving output '/no privileges 
> were granted for cron/partman/part_config/' message. And during 
> accessing that object from the cron/partman schema through that role, it 
> errors out with an access denied message. So I wanted to understand the 
> cause of this and how we should fix it , such that anybody logging in 
> through that role can see/fetch the data from the cron and partman 
> schema tables.
> 
> grant select on cron.job to <role1>;
> grant select on cron.job_run_details to <role1>;
> grant select on partman.part_config to <role1>;
> 
> Regards
> Sud

-- 
Adrian Klaver
adrian.klaver@aklaver.com