Horton

Thread

  1. prevent users from SELECT-ing from pg_roles/pg_database

    Andreas Joseph Krogh <andreas@visena.com> — 2024-05-24T15:51:59Z

    
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.



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: prevent users from SELECT-ing from pg_roles/pg_database

    Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net> — 2024-05-24T16:28:20Z

    Greetings,

To prevent a user or role from selecting data from certain system tables in
PostgreSQL, you can revoke the default select permissions on those tables.
Here’s how you can do it:

   1. Revoke SELECT permission on the system tables from the public role.
   2. Grant SELECT permission only to specific roles that need it.

Here’s a step-by-step guide on how to achieve this:

Salahuddin.

On Fri, 24 May 2024, 20:52 Andreas Joseph Krogh, <andreas@visena.com> wrote:

> 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.
>
>
>
> Thanks.
>
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com>
>

  3. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-24T17:02:13Z

    Andreas Joseph Krogh <andreas@visena.com> 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

  4. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net> — 2024-05-24T17:43:56Z

    Greetings,

Yes, you are correct. And

For applications/systems/scripts relying  on this information may require
sgnificent modifications to handle the restricted access.

Alternative approches can be.

Auditing and monitoring.
You can use pgaudit extension for auditing and minitoring.

Use SE-PostgeSQL extension that give fine grained access control other than
PG standard permissions.

Try RLS row level security. May involve careful planning and may not cover
all use cases.

Regards,
Salahuddin.


On Fri, 24 May 2024, 22:02 Tom Lane, <tgl@sss.pgh.pa.us> wrote:

> Andreas Joseph Krogh <andreas@visena.com> 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
>
>
>

  5. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Andreas Joseph Krogh <andreas@visena.com> — 2024-05-27T07:33:51Z

    
På fredag 24. mai 2024 kl. 19:02:13, skrev Tom Lane <tgl@sss.pgh.pa.us 
<mailto:tgl@sss.pgh.pa.us>>:
Andreas Joseph Krogh <andreas@visena.com> 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

  6. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-05-27T09:10:10Z

    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

  7. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Andreas Joseph Krogh <andreas@visena.com> — 2024-05-27T09:33:30Z

    
På mandag 27. mai 2024 kl. 11:10:10, skrev Laurenz Albe <
laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>>:
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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

  8. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-05-27T10:45:02Z

    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 <laurenz.albe@cybertec.at>:
> > 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

  9. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-27T23:48:17Z

    Laurenz Albe <laurenz.albe@cybertec.at> 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

  10. Re: prevent users from SELECT-ing from pg_roles/pg_database

    Andreas Joseph Krogh <andreas@visena.com> — 2024-05-28T06:07:38Z

    
På tirsdag 28. mai 2024 kl. 01:48:17, skrev Tom Lane <tgl@sss.pgh.pa.us 
<mailto:tgl@sss.pgh.pa.us>>:
Laurenz Albe <laurenz.albe@cybertec.at> 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>