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

p\g
grant all on all tables in schema base, bulk, sgstemplate to sgstemplate\p\g
--where sgstemplate is sedded to the real deal by the installer

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);

On 4/11/22 16:10, Rob Sargent wrote:
> I'm using postgres 14 in a database recently brought over from postgres 
> 10.  (I did not do the bringing over.)
> 
> In my set-up, I partition datasets by schema, create a role per schema 
> and part of that is this explicit permission granting (from superuser):
> 
>     p\g
>     grant all on all tables in schema base, bulk, sgstemplate to
>     sgstemplate\p\g
>     --where sgstemplate is sedded to the real deal by the installer
> 
> there is no explicit 'public' in that set-up and here-to-fore the new 
> grantee has been able to see into public stuff just fine, in particular 
> to public functions which rely on getting grantee's version of tables 
> (replicated in sister schema).
> 
> 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:

"
Next Steps: How Can I Protect My Databases?

Do not allow users to create new objects in the public schema

As a superuser, run the following command in all of your databases:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Running REVOKE CREATE ON SCHEMA public FROM PUBLIC; prevents all 
non-superusers from creating objects in the public schema. This setting 
will protect a PostgreSQL database from the problem described in 
CVE-2018-1058.

Once this command is run, certain operations could fail within your 
database. For example, a non-superuser will not be able to create tables 
or functions anymore with the public schema, which may affect how a user 
manages application schema migrations.

Note that the REVOKE command is more powerful than running DROP SCHEMA 
public; as pg_dump does not preserve the public schema removal.

After running this command, you should strongly consider auditing your 
public schema to see if any users have created functions that have names 
similar to ones in the pg_catalog. From the command-line tool (e.g. 
psql), you can see a list of functions available in the public schema by 
running:

\df public.*

To see a full list of functions defined In the pg_catalog schema, please 
run:

\df pg_catalog.*

"

Probably should take a look at what permissions the functions in public 
have?

> 
> 
> I've run those grants specifically naming public and all is well. Do I 
> need to add that to the installer script?
> 
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.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.

            regards, tom lane

On 4/11/22 17:34, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.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


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/11/22 17:34, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.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