Feedback-ID: i76984098:Fastmail
Message-ID: <058e9aa1-60e8-44db-9a27-003a1c27cb5e@aklaver.com>
Date: Mon, 23 Dec 2024 12:34:34 -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>
 <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>
 <70e957e6-1067-4849-82a4-01cc2a9aa8cd@aklaver.com>
 <650461436.6531774.1734985037118@mail.yahoo.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <650461436.6531774.1734985037118@mail.yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/058e9aa1-60e8-44db-9a27-003a1c27cb5e%40aklaver.com>
Precedence: bulk


On 12/23/24 12:17 PM, Bharani SV-forum wrote:
> Adrian
> TQ. Exactly , you have provided the needed information
> 
> For existing databases, especially those having multiple users,
> - I will consider revoking CREATE permission on the public schema to 
> adopt this new default.

You may want to do some testing before you do that. It may very well be 
that your existing code depends on being able to CREATE objects in the 
public schema using the public role.

> 
> 1) Do i need to revoke "SELECT" access on any tables/indexes etc in 
> schema = public ?
> 2) Do i need to revoke "Execute" access on any procedures/functions  in 
> schema = public ?

Again this is going to need to be tested. I'm betting though that your 
present code depends on these privileges existing.

Just remember the change was for:

"Remove PUBLIC creation permission on the public schema"

where PUBLIC is a built in role. I would suggest reading:

https://www.postgresql.org/docs/current/ddl-priv.html

for more information on what that means.

> 
> 
> I agree with your suggestion - - For new databases having no need to 
> defend against insider threats, granting CREATE permission will yield 
> the behavior of prior releases.

It is not my suggestion, what I quoted comes from the documentation.

> 
> Can you pl seed more information with qsn#1 and #2
> 
> On Monday, December 23, 2024 at 03:06:56 PM EST, Adrian Klaver 
> <adrian.klaver@aklaver.com> wrote:
> 
> 
> 
> 
> 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 
> <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 <mailto:adrian.klaver@aklaver.com>
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com