Thread: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
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
Principal Technician, Fisheries
NIWA
DDI: +64 (4) 3860529
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, September 12, 2024 16:36
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
Sent: Thursday, September 12, 2024 16:36
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
Andreas Joseph Krogh <andreas@visena.com> 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://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2F18604-04d64b68e981ced6%2540postgresql.org&data=05%7C02%7CBrent.Wood%40niwa.co.nz%7Cd471a43339634ba57f4208dcd2e48c6a%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C638617126279440102%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=Qbk8QFtdRcaWAzgvEHEA0kKTVIu4umFtqfNcYjCzJj4%3D&reserved=0
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
> 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://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2F18604-04d64b68e981ced6%2540postgresql.org&data=05%7C02%7CBrent.Wood%40niwa.co.nz%7Cd471a43339634ba57f4208dcd2e48c6a%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C638617126279440102%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=Qbk8QFtdRcaWAzgvEHEA0kKTVIu4umFtqfNcYjCzJj4%3D&reserved=0
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
 |
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.