Re: How easy is it to lose permissions in 'public' schema?

> On Apr 11, 2022, at 6:51 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 4/11/22 17:34, Tom Lane wrote:
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>>>> On 4/11/22 16:10, Rob Sargent wrote:
>>>>> I've just bumped into this.
>>>>>
>>>>> barnard=> select public.genome_threshold_mono('a'::text,'b'::text);
>>>>> ERROR: permission denied for schema public
>>>>> LINE 1: select public.genome_threshold_mono('a'::text,'b'::text);
>>>>>
>>>>> I know I haven't intentionally removed 'public' from grantee's purview
>>>>> and short of the code block above not actually getting run, any guesses
>>>>> as to how access to 'public' got removed from grantee?
>>> I'm going to say someone read this:
>>> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
>>> And did something along the line of this:
>>> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
>> Note that that only recommends removing CREATE, though, not USAGE
>> which is what Rob seems to be lacking.
>
> Yeah that is why I threw in the 'And did something along the line of this' and the 'Probably should take a look at what permissions the functions in public have?'. I'm guessing someone saw the release notes for 10.3(https://www.postgresql.org/docs/10/release-10-3.html) and the comments on the mailing list and got proactive.
>
>> regards, tom lane
>
Gentlemen,thank you.

Something similar to as described is a definite possibility during the ‘bringing over’. Same time one of the brought over dbs was imported twice without constraints etc. I love being looked after.
Cheers,
rjs

>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com