RE: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications

As suggested, tried creating the below RLS policy on the pg_database table. But seems it does not check for the connect
permission.

Below are the script executed as superuser

CREATE DATABASE testrls
    WITH
    OWNER = postgres
    ENCODING = 'UTF8';

REVOKE ALL ON DATABASE testrls FROM public;

ALTER TABLE pg_catalog.pg_database ENABLE ROW LEVEL SECURITY;

CREATE POLICY connect_permission_policy
ON pg_catalog.pg_database
FOR SELECT
USING (has_database_privilege(current_user, datname, 'CONNECT'));


Connected as user1 (user does not have connect permission) and executed the below query


SELECT (has_database_privilege(current_user, datname, 'CONNECT')), datname FROM pg_catalog.pg_database;

But the result still returns back with the database 'testrls'.

Results as below

"has_database_privilege","datname"
True,"template1"
True,"template0"
True,"postgres"
False,"testrls"


Could you please check the above queries and let me know if anything needs to be changed in order to make it work

Thanks,
Bash

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 07 September 2024 16:29
To: Joe Conway <mail@joeconway.com>
Cc: Christophe Pettus <xof@thebuild.com>; Basha <Basha@maxcontact.com>; PostgreSQL Bug List
<pgsql-bugs@lists.postgresql.org>
Subject: Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table
Modifications

Joe Conway <mail@joeconway.com> writes:
> I think Tom's suggestion regarding RLS on system catalogs was one
> approach bandied about in the past, but unless I am mistaken we never
> committed the patch to allow RLS on system catalogs (or did I miss
> that?)

I experimented before proposing that, and we will let you create RLS policies on system catalogs (if
allow_system_table_mods= on). 
Of course the system itself will ignore those, but I don't see why they would not work for user-issued queries.

                        regards, tom lane
MaxContact is a trading style of Trivoni Software Limited. Registration Number: England 09816677. Registered Office:
CityView House, 5 Union Street, Ardwick, Manchester M12 4JD. This e-mail and any files transmitted with it are
confidentialand intended solely for the use of the individual or entity to whom it is addressed. Any views or options
presentedare solely those of the author and do not necessarily represent those of Trivoni Software Limited. Internet
communicationsare not secure and therefore Trivoni Software Limited does not accept legal responsibility for the
contentsof this message. If you are not the intended recipient, you are hereby notified that you have received this
e-mailin error and that any use, disclosure, dissemination, forwarding, printing, or copying of this e-mail is strictly
prohibited.Trivoni Software Limited will not be liable for direct, special, indirect or consequential damage arising
fromalterations of the contents of this message by a third party or as a result of any VIRUS being passed on. Any
pricingdetails or other offers delivered via e-mail are not binding. If appropriate, an official purchase order
quotationconfirming pricing and bearing an authorisation signature will be provided via Docusign on request. If you
havereceived this e-mail in error, please notify the sender immediately and delete the e-mail without taking any copies
orforwarding it elsewhere.