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

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?

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