public inbox for [email protected]
help / color / mirror / Atom feedRe: prevent users from SELECT-ing from pg_roles/pg_database
2+ messages / 2 participants
[nested] [flat]
* Re: prevent users from SELECT-ing from pg_roles/pg_database
@ 2024-05-24 17:02 Tom Lane <[email protected]>
2024-05-24 17:43 ` Re: prevent users from SELECT-ing from pg_roles/pg_database Muhammad Salahuddin Manzoor <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Lane @ 2024-05-24 17:02 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected]
Andreas Joseph Krogh <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: prevent users from SELECT-ing from pg_roles/pg_database
2024-05-24 17:02 Re: prevent users from SELECT-ing from pg_roles/pg_database Tom Lane <[email protected]>
@ 2024-05-24 17:43 ` Muhammad Salahuddin Manzoor <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Muhammad Salahuddin Manzoor @ 2024-05-24 17:43 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andreas Joseph Krogh <[email protected]>; pgsql-general <[email protected]>
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, <[email protected]> wrote:
> Andreas Joseph Krogh <[email protected]> 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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-05-24 17:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-24 17:02 Re: prevent users from SELECT-ing from pg_roles/pg_database Tom Lane <[email protected]>
2024-05-24 17:43 ` Muhammad Salahuddin Manzoor <[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