public inbox for [email protected]
help / color / mirror / Atom feed101 Grants and Access Right Table/View
6+ messages / 3 participants
[nested] [flat]
* 101 Grants and Access Right Table/View
@ 2024-10-17 18:38 Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 18:40 ` Re: 101 Grants and Access Right Table/View rams nalabolu <[email protected]>
2024-10-17 19:48 ` Re: 101 Grants and Access Right Table/View Tom Lane <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2024-10-17 18:38 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
I have a simple question for "access rights" view or table within Postgres.
Here is my grant query:
GRANT USAGE ON SCHEMA abc TO abc_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA abc TO abc_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA abc TO abc_user;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA abc TO abc_user;
But I can't find a view/table that is associated with the above grants? The view/table below doesn't shown the above granted rights.
select * from information_schema.role_table_grants;
select * from information_schema.usage_privileges
Thank you
Kam Fook Wong
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 101 Grants and Access Right Table/View
2024-10-17 18:38 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
@ 2024-10-17 18:40 ` rams nalabolu <[email protected]>
2024-10-17 21:06 ` RE: [EXT] Re: 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: rams nalabolu @ 2024-10-17 18:40 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Who is the grantor? And the objects owner?
Only the objects owner can grant the privileges
On Thu, Oct 17, 2024 at 1:38 PM Wong, Kam Fook (TR Technology) <
[email protected]> wrote:
> I have a simple question for “access rights” view or table within
> Postgres.
>
> Here is my grant query:
>
> GRANT USAGE ON SCHEMA abc TO abc_user;
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA abc TO
> abc_user;
>
> GRANT ALL ON ALL SEQUENCES IN SCHEMA abc TO abc_user;
>
> GRANT EXECUTE ON ALL ROUTINES IN SCHEMA abc TO abc_user;
>
> But I can’t find a view/table that is associated with the above grants?
> The view/table below doesn’t shown the above granted rights.
>
>
>
> select * from information_schema.role_table_grants;
>
> select * from information_schema.usage_privileges
>
>
> Thank you
>
> Kam Fook Wong
> This e-mail is for the sole use of the intended recipient and contains
> information that may be privileged and/or confidential. If you are not an
> intended recipient, please notify the sender by return e-mail and delete
> this e-mail and any attachments. Certain required legal entity disclosures
> can be accessed on our website:
> https://www.thomsonreuters.com/en/resources/disclosures.html
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [EXT] Re: 101 Grants and Access Right Table/View
2024-10-17 18:38 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 18:40 ` Re: 101 Grants and Access Right Table/View rams nalabolu <[email protected]>
@ 2024-10-17 21:06 ` Wong, Kam Fook (TR Technology) <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2024-10-17 21:06 UTC (permalink / raw)
To: rams nalabolu <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Usename = postgres is the grantor. The object owner is flyway. I tried the grants on both postgres user and flyway user and the result is the same.
From: rams nalabolu <[email protected]>
Sent: Thursday, October 17, 2024 1:41 PM
To: Wong, Kam Fook (TR Technology) <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: [EXT] Re: 101 Grants and Access Right Table/View
External Email: Use caution with links and attachments.
Who is the grantor? And the objects owner?
Only the objects owner can grant the privileges
On Thu, Oct 17, 2024 at 1:38 PM Wong, Kam Fook (TR Technology) <[email protected]<mailto:[email protected]>> wrote:
I have a simple question for “access rights” view or table within Postgres.
Here is my grant query:
GRANT USAGE ON SCHEMA abc TO abc_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA abc TO abc_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA abc TO abc_user;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA abc TO abc_user;
But I can’t find a view/table that is associated with the above grants? The view/table below doesn’t shown the above granted rights.
select * from information_schema.role_table_grants;
select * from information_schema.usage_privileges
Thank you
Kam Fook Wong
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 101 Grants and Access Right Table/View
2024-10-17 18:38 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
@ 2024-10-17 19:48 ` Tom Lane <[email protected]>
2024-10-17 21:09 ` RE: [EXT] Re: 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-10-17 19:48 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <[email protected]>; +Cc: Pgsql-admin <[email protected]>
"Wong, Kam Fook (TR Technology)" <[email protected]> writes:
> I have a simple question for "access rights" view or table within Postgres.
> Here is my grant query:
> GRANT USAGE ON SCHEMA abc TO abc_user;
> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA abc TO abc_user;
> GRANT ALL ON ALL SEQUENCES IN SCHEMA abc TO abc_user;
> GRANT EXECUTE ON ALL ROUTINES IN SCHEMA abc TO abc_user;
> But I can't find a view/table that is associated with the above
> grants?
Those commands would simply apply the grants to any existing objects
in the given schema, so you'd have to look at such objects to see
the effects. If there are none, nothing happens.
You might be looking for ALTER DEFAULT PRIVILEGES?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [EXT] Re: 101 Grants and Access Right Table/View
2024-10-17 18:38 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 19:48 ` Re: 101 Grants and Access Right Table/View Tom Lane <[email protected]>
@ 2024-10-17 21:09 ` Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 21:17 ` Re: [EXT] Re: 101 Grants and Access Right Table/View Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2024-10-17 21:09 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Tom,
These are existing objects/tables. So I assume after the grants were run, the information_schema.role_table_grants and information_schema.usage_privileges shouldn't be blank right/now rows return?
Thank you
Kam
-----Original Message-----
From: Tom Lane <[email protected]>
Sent: Thursday, October 17, 2024 2:49 PM
To: Wong, Kam Fook (TR Technology) <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: [EXT] Re: 101 Grants and Access Right Table/View
External Email: Use caution with links and attachments.
"Wong, Kam Fook (TR Technology)" <[email protected]> writes:
> I have a simple question for "access rights" view or table within Postgres.
> Here is my grant query:
> GRANT USAGE ON SCHEMA abc TO abc_user; GRANT SELECT, INSERT, UPDATE,
> DELETE ON ALL TABLES IN SCHEMA abc TO abc_user; GRANT ALL ON ALL
> SEQUENCES IN SCHEMA abc TO abc_user; GRANT EXECUTE ON ALL ROUTINES IN
> SCHEMA abc TO abc_user;
> But I can't find a view/table that is associated with the above
> grants?
Those commands would simply apply the grants to any existing objects in the given schema, so you'd have to look at such objects to see the effects. If there are none, nothing happens.
You might be looking for ALTER DEFAULT PRIVILEGES?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [EXT] Re: 101 Grants and Access Right Table/View
2024-10-17 18:38 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 19:48 ` Re: 101 Grants and Access Right Table/View Tom Lane <[email protected]>
2024-10-17 21:09 ` RE: [EXT] Re: 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
@ 2024-10-17 21:17 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2024-10-17 21:17 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <[email protected]>; +Cc: Pgsql-admin <[email protected]>
"Wong, Kam Fook (TR Technology)" <[email protected]> writes:
> These are existing objects/tables. So I assume after the grants were run, the information_schema.role_table_grants and information_schema.usage_privileges shouldn't be blank right/now rows return?
Maybe. I believe both of those views will only show information about
grants where you (the user reading the view) are grantor or grantee.
You've not provided any details about your test setup ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-10-17 21:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-17 18:38 101 Grants and Access Right Table/View Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 18:40 ` rams nalabolu <[email protected]>
2024-10-17 21:06 ` Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 19:48 ` Tom Lane <[email protected]>
2024-10-17 21:09 ` Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-17 21:17 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox