public inbox for [email protected]  

help / color / mirror / Atom feed

From: Muhammad Salahuddin Manzoor <[email protected]>
To: Tom Lane <[email protected]>
Cc: Andreas Joseph Krogh <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: prevent users from SELECT-ing from pg_roles/pg_database
Date: Fri, 24 May 2024 22:43:56 +0500
Message-ID: <CAKD7CDkcRu8S6i=q8S06VoGyt9jfN5iS_2WvncAALrWwP3XyNg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <VisenaEmail.66.54f51e587b38c9cc.18fab4b4eb6@origo-test01.app.internal.visena.net>
	<[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
>
>
>

view thread (2+ messages)

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], [email protected]
  Subject: Re: prevent users from SELECT-ing from pg_roles/pg_database
  In-Reply-To: <CAKD7CDkcRu8S6i=q8S06VoGyt9jfN5iS_2WvncAALrWwP3XyNg@mail.gmail.com>

* 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