Feedback-ID: i76984098:Fastmail
Message-ID: <70e957e6-1067-4849-82a4-01cc2a9aa8cd@aklaver.com>
Date: Mon, 23 Dec 2024 12:06:37 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Ver 15.X and restriction for schema=public
To: Bharani SV-forum <esteembsv-forum@yahoo.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <0558ddd4d71641bdb41fa49b2425f73c@safrangroup.com>
 <1061066336.5835157.1733316137292@mail.yahoo.com>
 <a779ef21-f04d-4a51-83d3-51632b0fb3fa@aklaver.com>
 <1918413683.2239751.1733944364448@mail.yahoo.com>
 <037a8338-3434-47d4-aaad-ef186d4d3250@aklaver.com>
 <101139100.4053671.1734383960480@mail.yahoo.com>
 <dece41b2-44bd-4b0c-9b3e-57b0adfaf1bb@aklaver.com>
 <1875422740.4095620.1734388259706@mail.yahoo.com>
 <aa16f097-fadd-439c-a58e-e2229d92bc5b@aklaver.com>
 <1528119631.4096246.1734390318281@mail.yahoo.com>
 <2033286177.1733661.1734651404850@mail.yahoo.com>
 <8833d8c7-3713-4d6f-9eab-35270e769564@aklaver.com>
 <1392047062.5689697.1734710997053@mail.yahoo.com>
 <adc3d09b-440c-48c7-9d72-0f247f27feff@aklaver.com>
 <783439164.5695290.1734713539262@mail.yahoo.com>
 <928427177.4710979.1734722745840@mail.yahoo.com>
 <f37731a5-80bf-45d8-b699-587652f34b00@aklaver.com>
 <2036867199.5856152.1734735063013@mail.yahoo.com>
 <1694853482.6517504.1734981981968@mail.yahoo.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <1694853482.6517504.1734981981968@mail.yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/70e957e6-1067-4849-82a4-01cc2a9aa8cd%40aklaver.com>
Precedence: bulk


On 12/23/24 11:26 AM, Bharani SV-forum wrote:
> Team
> I am in the process of upgrading EC2-PGS ver 13.X to 15.X
> I am aware  since ver 14.X, we have restriction in the usage of 
> schema=public and the DBA need to grant exclusive priv for the tagged db 
> user's.

Are you referring to this?:

https://www.postgresql.org/docs/15/release-15.html

"Remove PUBLIC creation permission on the public schema (Noah Misch) §

The new default is one of the secure schema usage patterns that Section 
5.9.6 has recommended since the security release for CVE-2018-1058. The 
change applies to new database clusters and to newly-created databases 
in existing clusters. Upgrading a cluster or restoring a database dump 
will preserve public's existing permissions.

For existing databases, especially those having multiple users, consider 
revoking CREATE permission on the public schema to adopt this new 
default. For new databases having no need to defend against insider 
threats, granting CREATE permission will yield the behavior of prior 
releases.
"


> 
> Assume i want to enforce it,
> Can i retag all the object tables/indexex/packages/procedures/functions 
> etc tagged under schema =public to a newly created schema e.g = 
> *schemaname = allowallusr *and grant respective priv's.
> 
> Whether it will resolve the issue, as application time need time to 
> validated all the use case for testing the objects which is present 
> under schema=public and ported to new schema= allowallusr .
> 
> Any suggestions or best practise

-- 
Adrian Klaver
adrian.klaver@aklaver.com