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

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: How easy is it to lose permissions in 'public' schema?
Date: 2022-04-11 23:10:27
Message-ID: cfb9204f-5f46-5078-294b-a4a090f2bf7e@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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've run those grants specifically naming public and all is well. Do I
need to add that to the installer script?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-04-12 00:28:06 Re: How easy is it to lose permissions in 'public' schema?
Previous Message Zheng Li 2022-04-11 17:36:42 Re: Support logical replication of DDLs