Horton

Thread

  1. Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Andreas Joseph Krogh <andreas@visena.com> — 2024-09-12T03:41:03Z

    
Hei all.



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).



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?



Thanks.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

  2. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-12T04:36:45Z

    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://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

  3. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Brent Wood <brent.wood@niwa.co.nz> — 2024-09-12T04:48:23Z

    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 <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

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://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgresql.org<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


[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.

  4. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Andreas Joseph Krogh <andreas@visena.com> — 2024-09-12T04:51:54Z

    
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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

På torsdag 12. september 2024 kl. 06:36:45, skrev Tom Lane <tgl@sss.pgh.pa.us 
<mailto:tgl@sss.pgh.pa.us>>:
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://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

  5. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Greg Sabino Mullane <htamfids@gmail.com> — 2024-09-12T13:05:48Z

    On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh <andreas@visena.com>
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

  6. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Dominique Devienne <ddevienne@gmail.com> — 2024-09-12T13:11:59Z

    On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane <htamfids@gmail.com> 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

  7. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Andreas Joseph Krogh <andreas@visena.com> — 2024-09-12T13:21:49Z

    
På torsdag 12. september 2024 kl. 15:05:48, skrev Greg Sabino Mullane <
htamfids@gmail.com <mailto:htamfids@gmail.com>>:

On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh <andreas@visena.com 
<mailto:andreas@visena.com>> 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

  8. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Greg Sabino Mullane <htamfids@gmail.com> — 2024-09-12T13:53:02Z

    On Thu, Sep 12, 2024 at 9:12 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

> On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane <htamfids@gmail.com>
> 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

  9. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Greg Sabino Mullane <htamfids@gmail.com> — 2024-09-12T13:58:53Z

    On Thu, Sep 12, 2024 at 9:21 AM Andreas Joseph Krogh <andreas@visena.com>
wrote:

> Yes, it *is* theater, but that doesn't prevent “compliance people” to
> care about it. We have to take measures to prevent “information leaks”.
>

*shrug* Then the compliance people are not good at their jobs, frankly.

But if it works for you, go ahead. As Tom said, it will work 95% of the
time. But it will break things that should work, and it will not prevent
the ability to get the information in other ways. To be clear, we never
recommend messing with the system catalogs, and this falls under the
umbrella of messing with the system catalogs.

Cheers,
Greg

  10. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Christophe Pettus <xof@thebuild.com> — 2024-09-12T14:10:26Z

    
> On Sep 12, 2024, at 06:58, Greg Sabino Mullane <htamfids@gmail.com> wrote:
> 
> But if it works for you, go ahead. As Tom said, it will work 95% of the time. But it will break things that should work, and it will not prevent the ability to get the information in other ways. To be clear, we never recommend messing with the system catalogs, and this falls under the umbrella of messing with the system catalogs.

I can only echo that if the compliance people are taking a position that "you need to make an unsupported, ad-hoc modification to the database software's authentication system in order to meet this requirement," then the requirement is one that you should run, not walk, to get a waiver to, as that's a very unreasonable position for them to take.

  11. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Dominique Devienne <ddevienne@gmail.com> — 2024-09-12T14:10:56Z

    On Thu, Sep 12, 2024 at 3:53 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
> On Thu, Sep 12, 2024 at 9:12 AM Dominique Devienne <ddevienne@gmail.com> wrote:
>> On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane <htamfids@gmail.com> 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: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "eve" does not exist
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  database "theater" does not exist
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "alice"

Ah silly me, I was thinking of something else.
Sure, "probing" for DB or USER names by attempting connecting is of
course an option.
Thanks, --DD

  12. Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

    Andreas Joseph Krogh <andreas@visena.com> — 2024-09-12T14:13:26Z

    
På torsdag 12. september 2024 kl. 16:10:26, skrev Christophe Pettus <
xof@thebuild.com <mailto:xof@thebuild.com>>:


> On Sep 12, 2024, at 06:58, Greg Sabino Mullane <htamfids@gmail.com> wrote:
> 
> But if it works for you, go ahead. As Tom said, it will work 95% of the 
time. But it will break things that should work, and it will not prevent the 
ability to get the information in other ways. To be clear, we never recommend 
messing with the system catalogs, and this falls under the umbrella of messing 
with the system catalogs.

I can only echo that if the compliance people are taking a position that "you 
need to make an unsupported, ad-hoc modification to the database software's 
authentication system in order to meet this requirement," then the requirement 
is one that you should run, not walk, to get a waiver to, as that's a very 
unreasonable position for them to take.


We're probably going down the postgres_fdw route, that seems to do the job.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>