public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andreas Joseph Krogh <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
Date: Thu, 12 Sep 2024 06:51:54 +0200 (CEST)
Message-ID: <VisenaEmail.1.4f10ceb0099d6ab1.191e48a6946@origo-test01.app.internal.visena.net> (raw)
In-Reply-To: <[email protected]>
References: <VisenaEmail.0.81936c517b2d9cfe.191e44de951@origo-test01.app.internal.visena.net>
	<[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

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
  In-Reply-To: <VisenaEmail.1.4f10ceb0099d6ab1.191e48a6946@origo-test01.app.internal.visena.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox