public inbox for [email protected]help / color / mirror / Atom feed
Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC 7+ messages / 5 participants [nested] [flat]
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 04:36 Tom Lane <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Tom Lane @ 2024-09-12 04:36 UTC (permalink / raw) To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected] Andreas Joseph Krogh <[email protected]> writes: > Motivation: I have PowerBI users, with a separate ‘reporting’-role, accessing > a database and I want to prevent them from listing all tables, users, databases > and view-definitions (to not see the underlying query). Postgres is not designed to support this requirement. > I'm evaluating this: > REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELECT ON > ALL TABLES IN SCHEMA information_schema FROM PUBLIC; > Will this affect “normal behaviour”, ie. prevent the planner, or other > internal mechanisms, from working properly for sessions logged in with the > ‘reporting’-role? Probably 95% of that stuff will still work. By the same token, there are plenty of information-leaking code pathways that will still leak. For instance, your restricted user will have no trouble discovering the OIDs and names of all extant tables, using something like do $$ begin for tid in 1..1000000 loop if tid::regclass::text != tid::text then raise notice 'tid % is %', tid, tid::regclass; end if; end loop; end $$; Functions such as pg_describe_object still work fine, too. Experimenting with psql, a lot of stuff is broken as expected: busted=> \d mytable ERROR: permission denied for table pg_class but some things still work: busted=> \sf sin CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision) RETURNS double precision LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$dsin$function$ This is pretty much the other side of the same coin. The reason you can still parse and plan a query is that it does not occur to large parts of the backend that there should be any reason to refuse to read a system catalog. That carries over to these operations as well. This recent thread might be enlightening: https://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgresql.org If you have a requirement like this, I think the only safe way to meet it is to not give those users direct SQL access. Put some kind of restrictive app in front of the database. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 04:48 Brent Wood <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Brent Wood @ 2024-09-12 04:48 UTC (permalink / raw) To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected] <[email protected]> Could you use FDW's in another completely separate db for them to access so they have no direct access to the source data (or database), only the linked tables which have no local data, other users, etc, present at all? Which is sort of what was suggested: "Put some kind of restrictive app in front of the database." This other db could be that app? Brent Wood Principal Technician, Fisheries NIWA DDI: +64 (4) 3860529 ________________________________ From: Tom Lane <[email protected]> Sent: Thursday, September 12, 2024 16:36 To: Andreas Joseph Krogh <[email protected]> Cc: [email protected] <[email protected]> Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC Andreas Joseph Krogh <[email protected]> writes: > Motivation: I have PowerBI users, with a separate ‘reporting’-role, accessing > a database and I want to prevent them from listing all tables, users, databases > and view-definitions (to not see the underlying query). Postgres is not designed to support this requirement. > I'm evaluating this: > REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELECT ON > ALL TABLES IN SCHEMA information_schema FROM PUBLIC; > Will this affect “normal behaviour”, ie. prevent the planner, or other > internal mechanisms, from working properly for sessions logged in with the > ‘reporting’-role? Probably 95% of that stuff will still work. By the same token, there are plenty of information-leaking code pathways that will still leak. For instance, your restricted user will have no trouble discovering the OIDs and names of all extant tables, using something like do $$ begin for tid in 1..1000000 loop if tid::regclass::text != tid::text then raise notice 'tid % is %', tid, tid::regclass; end if; end loop; end $$; Functions such as pg_describe_object still work fine, too. Experimenting with psql, a lot of stuff is broken as expected: busted=> \d mytable ERROR: permission denied for table pg_class but some things still work: busted=> \sf sin CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision) RETURNS double precision LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$dsin$function$ This is pretty much the other side of the same coin. The reason you can still parse and plan a query is that it does not occur to large parts of the backend that there should be any reason to refuse to read a system catalog. That carries over to these operations as well. This recent thread might be enlightening: https://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgresql.org<https://www.po...; If you have a requirement like this, I think the only safe way to meet it is to not give those users direct SQL access. Put some kind of restrictive app in front of the database. regards, tom lane [https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] <https://www.niwa.co.nz/; Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 National Institute of Water & Atmospheric Research Ltd (NIWA) 301 Evans Bay Parade Hataitai Wellington New Zealand Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz/; Facebook<https://www.facebook.com/nzniwa; LinkedIn<https://www.linkedin.com/company/niwa; Twitter<https://twitter.com/niwa_nz; Instagram<https://www.instagram.com/niwa_science; YouTube<https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A; To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems. Note: This email is intended solely for the use of the addressee and may contain information that is confidential or subject to legal professional privilege. If you receive this email in error please immediately notify the sender and delete the email. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 04:51 Andreas Joseph Krogh <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Andreas Joseph Krogh @ 2024-09-12 04:51 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] Thanks for info. I know PG is not designed for this, but I have this requirement nonetheless… I think preventing “most users and tools" from seeing/presenting this information is “good enough”. Maybe not revoking access to all the tables in the schemas, but the “most obvious ones”, like pg_user, pg_shadow, pg_tables etc. will suffice. If read-access (SELECT) on views in public-schema will still works, and pg_dump/restore etc. also works, this sounds like a solution to me. -- 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; På torsdag 12. september 2024 kl. 06:36:45, skrev Tom Lane <[email protected] <mailto:[email protected]>>: Andreas Joseph Krogh <[email protected]> writes: > Motivation: I have PowerBI users, with a separate ‘reporting’-role, accessing > a database and I want to prevent them from listing all tables, users, databases > and view-definitions (to not see the underlying query). Postgres is not designed to support this requirement. > I'm evaluating this: > REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELECT ON > ALL TABLES IN SCHEMA information_schema FROM PUBLIC; > Will this affect “normal behaviour”, ie. prevent the planner, or other > internal mechanisms, from working properly for sessions logged in with the > ‘reporting’-role? Probably 95% of that stuff will still work. By the same token, there are plenty of information-leaking code pathways that will still leak. For instance, your restricted user will have no trouble discovering the OIDs and names of all extant tables, using something like do $$ begin for tid in 1..1000000 loop if tid::regclass::text != tid::text then raise notice 'tid % is %', tid, tid::regclass; end if; end loop; end $$; Functions such as pg_describe_object still work fine, too. Experimenting with psql, a lot of stuff is broken as expected: busted=> \d mytable ERROR: permission denied for table pg_class but some things still work: busted=> \sf sin CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision) RETURNS double precision LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$dsin$function$ This is pretty much the other side of the same coin. The reason you can still parse and plan a query is that it does not occur to large parts of the backend that there should be any reason to refuse to read a system catalog. That carries over to these operations as well. This recent thread might be enlightening: https://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgresql.org If you have a requirement like this, I think the only safe way to meet it is to not give those users direct SQL access. Put some kind of restrictive app in front of the database. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 13:05 Greg Sabino Mullane <[email protected]> parent: Andreas Joseph Krogh <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Greg Sabino Mullane @ 2024-09-12 13:05 UTC (permalink / raw) To: Andreas Joseph Krogh <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh <[email protected]> wrote: > I know PG is not designed for this, but I have this requirement > nonetheless… > I think preventing “most users and tools" from seeing/presenting this > information is “good enough”. > As pointed out, there are very many workarounds. This is security theater. If read-access (SELECT) on views in public-schema will still works, and > pg_dump/restore etc. also works, this sounds like a solution to me. > pg_dump will absolutely not work without access to the system catalogs. If you want to prevent information, stop direct access and make the application call user functions. (Also note that determining if a database or user exists does not even require a successful login to the cluster.) Cheers, Greg ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 13:11 Dominique Devienne <[email protected]> parent: Greg Sabino Mullane <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Dominique Devienne @ 2024-09-12 13:11 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: Andreas Joseph Krogh <[email protected]>; Tom Lane <[email protected]>; [email protected] On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane <[email protected]> wrote: > (Also note that determining if a database or user exists does not even require a successful login to the cluster.) Hi. How so? I was not aware of such possibilities. Can you please give pointers (docs, examples) of this? Thanks, --DD ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 13:21 Andreas Joseph Krogh <[email protected]> parent: Greg Sabino Mullane <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Andreas Joseph Krogh @ 2024-09-12 13:21 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] På torsdag 12. september 2024 kl. 15:05:48, skrev Greg Sabino Mullane < [email protected] <mailto:[email protected]>>: On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh <[email protected] <mailto:[email protected]>> wrote: I know PG is not designed for this, but I have this requirement nonetheless… I think preventing “most users and tools" from seeing/presenting this information is “good enough”. As pointed out, there are very many workarounds. This is security theater. Yes, it is theater, but that doesn't prevent “compliance people” to care about it. We have to take measures to prevent “information leaks”. -- 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] 7+ messages in thread
* Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC @ 2024-09-12 13:53 Greg Sabino Mullane <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Greg Sabino Mullane @ 2024-09-12 13:53 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Andreas Joseph Krogh <[email protected]>; Tom Lane <[email protected]>; [email protected] On Thu, Sep 12, 2024 at 9:12 AM Dominique Devienne <[email protected]> wrote: > On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane <[email protected]> > wrote: > > (Also note that determining if a database or user exists does not even > require a successful login to the cluster.) > > Hi. How so? I was not aware of such possibilities. > Can you please give pointers (docs, examples) of this? > $ psql -U eve psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "eve" does not exist $ psql -U postgres -d theater psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "theater" does not exist $ psql -U alice -d template1 psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: password authentication failed for user "alice" Cheers, Greg ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-09-12 13:53 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-12 04:36 Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC Tom Lane <[email protected]> 2024-09-12 04:48 ` Brent Wood <[email protected]> 2024-09-12 04:51 ` Andreas Joseph Krogh <[email protected]> 2024-09-12 13:05 ` Greg Sabino Mullane <[email protected]> 2024-09-12 13:11 ` Dominique Devienne <[email protected]> 2024-09-12 13:53 ` Greg Sabino Mullane <[email protected]> 2024-09-12 13:21 ` 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