Re: prevent users from SELECT-ing from pg_roles/pg_database
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, wrote:
> Andreas Joseph Krogh 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
>
>
>
Re: prevent users from SELECT-ing from pg_roles/pg_database
Andreas Joseph Krogh 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
Re: prevent users from SELECT-ing from pg_roles/pg_database
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, 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
> andr...@visena.com
> www.visena.com
>
>
prevent users from SELECT-ing from pg_roles/pg_database
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
andr...@visena.com
www.visena.com
Re: PG 12.2 ERROR: cannot freeze committed xmax
Thanks Ron. I did find one bug fix commit mentioning this error but it was saying that was due to the interaction of two other commits that at least the first one was after the cut of pg 12.2. so I have dismissed that as my deployment issue. I was looking for more evidence to push for a pg minor upgrade, the issue is for my context any upgrades requires a big test effort. so I was looking for more solid evidence. On Thu, May 23, 2024 at 7:17 PM Ron Johnson wrote: > On Thu, May 23, 2024 at 9:41 AM bruno da silva > wrote: > >> Hello, >> I have a deployment with PG 12.2 reporting ERROR: cannot freeze >> committed xmax >> using Red Hat Enterprise Linux 8.9. >> >> What is the recommended to find any bug fixes that the version 12.2 had >> that could have caused this error. >> > > https://www.postgresql.org/docs/release/ > > You're missing *four years* of bug fixes. > > Could this error be caused by OS/Hardware related issues? >> > > Four years of bug fixes is more likely the answer. > > -- Bruno Vieira da Silva
Re: Json table/column design question
On Thu, 2024-05-23 at 11:06 -0500, Skorpeo Skorpeo wrote: > I see people are big fans of json here. PostgreSQL's JSON support is great. But way too often people use it in the wrong way. Yours, Laurenz Albe
Re: Strange issue with unique index
On Thu, 2024-05-23 at 22:18 -0400, Tom Lane wrote: > writes: > > I've run into a strange issue with a unique index that I'm struggling to > > understand. I've extracted the basic info to reproduce this below. > > ... > > This will now block until session 2 is complete. I don't understand why this > > would block. I do know it's that unique index causing the issue, but I need > > the unique index in place. > > No, it's not about the unique index. It's about the foreign key > constraint --- if you remove that, there is no blockage. The reason > why that's happening is that the insertions of dependent child rows > acquire row locks on the FK-referenced tuple, to prevent that row from > going away before the insertions commit. So when you then decide to > UPDATE the referenced row, that blocks on the other session's row lock. > > I had an idea that we were bright enough to not block if the UPDATE > doesn't change the column(s) involved in the FK, but either I'm wrong > or that's not working in this example for some reason. It might be > that the fact that session 1 itself also holds such a row lock is > complicating matters. No, the problem is the unique constraint on "checksum". If you update a column that is part of a unique or primary key constraint, the row lock taken will be FOR UPDATE, not FOR NO KEY UPDATE. And the FOR UPDATE lock conflicts with the FOR KEY SHARE lock taken by the INSERT on the referenced row. Without the unique constraint, there would be no lock. Yours, Laurenz Albe
RE: Strange issue with unique index
writes: >> I've run into a strange issue with a unique index that I'm struggling >> to understand. I've extracted the basic info to reproduce this below. >> ... >> This will now block until session 2 is complete. I don't understand >> why this would block. I do know it's that unique index causing the >> issue, but I need the unique index in place. >No, it's not about the unique index. It's about the foreign key constraint --- if you remove that, there is no blockage. The reason why that's happening is that the insertions of >dependent child rows acquire row locks on the FK-referenced tuple, to prevent that row from going away before the insertions commit. So when you then decide to UPDATE >the referenced row, that blocks on the other session's row lock. >You can make things a little better, at the cost of more overhead, by declaring the FK as DEFERRABLE INITIALLY DEFERRED. Thanks for the swift response. No concern using a Deferred FK here, because it's not a hight TPS area. And it does work tx. What is just interesting is that this does not happen with that unique index in place. If I run that scenario with a normal FK and without the unique index there is also no blocking happening. So it does look like PG is smart enough in the normal flow, but not with the unique index in place. In my real world table I could also work around it by making the unique index filtered to exclude this type of update (There is another flag on the table). This scenario does just bring up questions in other parts of our system, because we have a few that has this structure of parent/child with unique index on parent and updates on both levels. Regards Riaan Stander
