Postgres Pro
Downloads
Home   >   mailing lists

Thread: [MASSMAIL]Grants and privileges issue

[MASSMAIL]Grants and privileges issue

From
sud
Date:
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,

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

Re: Grants and privileges issue

From
Adrian Klaver
Date:

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

Re: Grants and privileges issue

From
sud
Date:

On Fri, Mar 29, 2024 at 2:43 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:


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.



Thank you Adrian.

I think I got the answer for my first question , as the doc says below. So it means the "grant usage on schema" is a must for the user to access the object within the schema along with the "select on table" access. And with just "select on table" we won't be able to access the object inside the schema.

"For schemas, allows access to objects contained in the schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to “look up” objects within the schema. Without this permission, it is still possible to see the object names, e.g., by querying system catalogs. " 

Regarding my second question, I am still unable to find out why we are seeing "no privileges were granted for cron/partman/part_config' message while adding the grants to the user?


Re: Grants and privileges issue

From
Erik Wienhold
Date:
On 2024-03-28 22:43 +0100, sud wrote:
> Regarding my second question, I am still unable to find out why we are
> seeing "*no privileges were granted for cron/partman/part_config*' message
> while adding the grants to the user?

Because the role that is granting the privileges is missing the grant
option.  For example below, alice is missing the grant option and she
gets that warning when she wants to grant her privileges to bob.

    =# CREATE ROLE alice; CREATE ROLE bob; CREATE TABLE t (a int);
    CREATE ROLE
    CREATE ROLE
    CREATE TABLE
    =# GRANT ALL ON t TO alice;
    GRANT
    =# SET ROLE alice;
    SET
    => GRANT ALL ON t TO bob;
    WARNING:  no privileges were granted for "t"
    GRANT

With GRANT ALL ON t TO alice WITH GRANT OPTION it would've worked.

-- 
Erik