public inbox for [email protected]help / color / mirror / Atom feed
Re: prevent users from SELECT-ing from pg_roles/pg_database 6+ messages / 3 participants [nested] [flat]
* Re: prevent users from SELECT-ing from pg_roles/pg_database @ 2024-05-27 07:33 Andreas Joseph Krogh <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andreas Joseph Krogh @ 2024-05-27 07:33 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] På fredag 24. mai 2024 kl. 19:02:13, skrev Tom Lane <[email protected] <mailto:[email protected]>>: Andreas Joseph Krogh <[email protected]> writes: > Hi, is there a way to prevent a user/role from SELECT-ing from certain > system-tables? > I'd like the contents of pg_{user,roles,database} to not be visible to all > users. As noted, you can in principle revoke the public SELECT grant from those views/catalogs. However, the system is not designed to hide such information, which means you'll have (at least) two kinds of issues to worry about: 1. Queries or tools breaking that you didn't expect to break. It's hardly uncommon for instance for queries on pg_class to join to pg_roles to get the owner names for tables. 2. Information leaks. For example, mapping role OID to role name is trivial with either regrole or pg_get_userbyid(), so it wouldn't take long to scan the plausible range of role OIDs and get all their names, even without SQL access to the underlying catalog. regards, tom lane I tried: REVOKE SELECT ON pg_catalog.pg_database FROM public; But that doesn't prevent a normal user from querying pg_database it seems… -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 [email protected] <mailto:[email protected]> www.visena.com <https://www.visena.com; <https://www.visena.com; ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: prevent users from SELECT-ing from pg_roles/pg_database @ 2024-05-27 09:10 Laurenz Albe <[email protected]> parent: Andreas Joseph Krogh <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Laurenz Albe @ 2024-05-27 09:10 UTC (permalink / raw) To: Andreas Joseph Krogh <[email protected]>; Tom Lane <[email protected]>; +Cc: [email protected] On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: > I tried: > > REVOKE SELECT ON pg_catalog.pg_database FROM public; > > But that doesn't prevent a normal user from querying pg_database it seems… It works here. Perhaps the "normal" user is a member of "pg_read_all_data". Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: prevent users from SELECT-ing from pg_roles/pg_database @ 2024-05-27 09:33 Andreas Joseph Krogh <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Andreas Joseph Krogh @ 2024-05-27 09:33 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] På mandag 27. mai 2024 kl. 11:10:10, skrev Laurenz Albe < [email protected] <mailto:[email protected]>>: On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: > I tried: > > REVOKE SELECT ON pg_catalog.pg_database FROM public; > > But that doesn't prevent a normal user from querying pg_database it seems… It works here. Perhaps the "normal" user is a member of "pg_read_all_data". Yours, Laurenz Albe Don't think so: andreak@[local]:5432 16.3 andreak=# REVOKE pg_read_all_data from nisse; WARNING: role "nisse" has not been granted membership in role "pg_read_all_data" by role "postgres" REVOKE ROLE Any hints welcome. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 [email protected] <mailto:[email protected]> www.visena.com <https://www.visena.com; <https://www.visena.com; ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: prevent users from SELECT-ing from pg_roles/pg_database @ 2024-05-27 10:45 Laurenz Albe <[email protected]> parent: Andreas Joseph Krogh <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Laurenz Albe @ 2024-05-27 10:45 UTC (permalink / raw) To: Andreas Joseph Krogh <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] On Mon, 2024-05-27 at 11:33 +0200, Andreas Joseph Krogh wrote: > På mandag 27. mai 2024 kl. 11:10:10, skrev Laurenz Albe <[email protected]>: > > On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: > > > I tried: > > > > > > REVOKE SELECT ON pg_catalog.pg_database FROM public; > > > > > > But that doesn't prevent a normal user from querying pg_database it seems… > > > > It works here. > > > > Perhaps the "normal" user is a member of "pg_read_all_data". > > Don't think so: > andreak@[local]:5432 16.3 andreak=# REVOKE pg_read_all_data from nisse; > WARNING: role "nisse" has not been granted membership in role "pg_read_all_data" by role "postgres" > REVOKE ROLE Possibilities: - you are running a modified version of PostgreSQL - you are actually a superuser, perhaps by inheritance Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: prevent users from SELECT-ing from pg_roles/pg_database @ 2024-05-27 23:48 Tom Lane <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2024-05-27 23:48 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Andreas Joseph Krogh <[email protected]>; [email protected] Laurenz Albe <[email protected]> writes: > On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: >> I tried: >> REVOKE SELECT ON pg_catalog.pg_database FROM public; >> But that doesn't prevent a normal user from querying pg_database it seems… > It works here. Works for me too, although you'd have to do it over in each database where you want it to be effective. (Although pg_database is a shared catalog, the metadata about it is not shared.) regards, tom lane ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: prevent users from SELECT-ing from pg_roles/pg_database @ 2024-05-28 06:07 Andreas Joseph Krogh <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Andreas Joseph Krogh @ 2024-05-28 06:07 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] På tirsdag 28. mai 2024 kl. 01:48:17, skrev Tom Lane <[email protected] <mailto:[email protected]>>: Laurenz Albe <[email protected]> writes: > On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: >> I tried: >> REVOKE SELECT ON pg_catalog.pg_database FROM public; >> But that doesn't prevent a normal user from querying pg_database it seems… > It works here. Works for me too, although you'd have to do it over in each database where you want it to be effective. (Although pg_database is a shared catalog, the metadata about it is not shared.) regards, tom lane Ah, that's what I was missing. Thanks for pointing that out, it's working as expected now. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 [email protected] <mailto:[email protected]> www.visena.com <https://www.visena.com; <https://www.visena.com; ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-28 06:07 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-05-27 07:33 Re: prevent users from SELECT-ing from pg_roles/pg_database Andreas Joseph Krogh <[email protected]> 2024-05-27 09:10 ` Laurenz Albe <[email protected]> 2024-05-27 09:33 ` Andreas Joseph Krogh <[email protected]> 2024-05-27 10:45 ` Laurenz Albe <[email protected]> 2024-05-27 23:48 ` Tom Lane <[email protected]> 2024-05-28 06:07 ` Andreas Joseph Krogh <[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