Обсуждение: Re: [PATCH] pg_permissions

On 11.03.21 08:00, Joel Jacobson wrote:
> Do we prefer "pg_permissions" or "pg_privileges"?

pg_privileges would be better.  "Permissions" is not an SQL term.

I would be happy to review this patch, but a look through the email leaves me
thinking it may still be waiting on a C implementation of pg_get_acl(). Is that
right? And perhaps a view rename to pg_privileges, following Peter's comment?

On Fri, Feb 25, 2022, at 22:12, Chapman Flack wrote:
> I would be happy to review this patch, but a look through the email leaves me
> thinking it may still be waiting on a C implementation of pg_get_acl(). Is that
> right?

Not sure.

> And perhaps a view rename to pg_privileges, following Peter's comment?

+1

/Joel

On 02/26/22 03:27, Joel Jacobson wrote:
> On Fri, Feb 25, 2022, at 22:12, Chapman Flack wrote:
>> I would be happy to review this patch, but a look through the email leaves me
>> thinking it may still be waiting on a C implementation of pg_get_acl(). Is that
>> right?
> 
> Not sure.

It looked to me as if the -hackers messages of 25 and 26 March 2021 had
found a consensus that a pg_get_acl() function would be a good thing,
with the views to be implemented over that.

I'm just not seeing any later patch that adds such a function.

Regards,
-Chap

On Thu, Mar 10, 2022, at 22:02, Chapman Flack wrote:
> It looked to me as if the -hackers messages of 25 and 26 March 2021 had
> found a consensus that a pg_get_acl() function would be a good thing,
> with the views to be implemented over that.
>
> I'm just not seeing any later patch that adds such a function.

My apologies for late reply. Here it comes.

Recap: This patch is about adding two new system views: pg_privileges and pg_ownerships.

Changes since patch 0005 from 2021-03-25:

- Implement SQL-callable pg_get_acl()
This is a stripped down version of AlterObjectOwner_internal() from alter.c.

- Rename pg_permissions -> pg_privileges

- Use pg_shdepend + pg_get_acl() in pg_privileges, to avoid slow UNION ALL.

- Fix indentation of the new system views to be consistent with the other views.

- Add documentation of pg_get_acl() to func.sgml

- Move documentation of system views from catalogs.sgml to system-views.sgml

- Much smaller patch, thanks to getting rid of the long UNION ALL view definition:
 1 file changed, 195 insertions(+), 460 deletions(-)

/Joel

On Thu, Jun 13, 2024, at 00:14, Joel Jacobson wrote:
> Changes since patch 0005 from 2021-03-25:
> * 0006-pg_privileges-and-pg_ownerships.patch

- Also much faster now thanks to pg_get_acl():

Test with 100000 tables:

SELECT COUNT(*) FROM pg_permissions_union_all;
Time: 1466.504 ms (00:01.467)
Time: 1435.520 ms (00:01.436)
Time: 1459.396 ms (00:01.459)

SELECT COUNT(*) FROM pg_privileges;
Time: 292.257 ms
Time: 288.406 ms
Time: 294.831 ms

Hmm, strange, the commitfest system didn't pick up the email with patch 0006 for some reason,
with message id 0c5a6b79-408c-4910-9b2e-4aa9a7b30f3a@app.fastmail.com

It's rebased to latest HEAD, so not sure why.

Maybe it got confused when I quickly afterwards sent a new email without a patch?

Here is a new attempt, file content unchanged, just named to 0007 and added "pg_get_acl" to the name.

On Thu, Jun 13, 2024, at 04:00, Joel Jacobson wrote:
> On Thu, Jun 13, 2024, at 00:14, Joel Jacobson wrote:
>> Changes since patch 0005 from 2021-03-25:
>> * 0006-pg_privileges-and-pg_ownerships.patch
>
> - Also much faster now thanks to pg_get_acl():
>
> Test with 100000 tables:
>
> SELECT COUNT(*) FROM pg_permissions_union_all;
> Time: 1466.504 ms (00:01.467)
> Time: 1435.520 ms (00:01.436)
> Time: 1459.396 ms (00:01.459)
>
> SELECT COUNT(*) FROM pg_privileges;
> Time: 292.257 ms
> Time: 288.406 ms
> Time: 294.831 ms

-- 
Kind regards,

Joel