On Fri, Aug 14, 2020 at 02:50:32PM -0400, Tom Lane wrote: > However, in itself this can only fix references that are resolved during > execution of the extension script. I don't see a good way to use the > idea to make earthdistance's SQL functions fully secure. It won't do > to write, say, > > CREATE FUNCTION ll_to_earth(float8, float8) > ... > AS 'SELECT @extschema(cube)@.cube(...)'; > > because this will not survive somebody doing "ALTER EXTENSION cube SET > SCHEMA schema3". I don't have a proposal for what to do about that.
Another challenge is verifying that the body qualified everything. Via a simple matter of programming, CREATE EXTENSION could verify that CREATE-time code observes schema qualification rules. That would not extend to function bodies. > Admittedly, we already disclaim security if you run queries with a > search_path that contains any untrusted schemas ... but it would be > nice if extensions could be written that (in themselves) are safe > regardless. Yes. Even when safety is not a concern, it's a quality problem for the functions to error out when search_path lacks some schema. As you know, we get recurring reports about that, e.g. https://www.postgresql.org/message-id/flat/16534-69f25077c45f34a5%40postgresql.org > Peter E's proposal for parsing SQL function bodies at > creation time could perhaps fix this for SQL functions, but we still > have the issue for other PLs. Yes. The SQL-specific feature could do enough to let a future version of earthdistance be trusted. > 2. [...] lookup_agg_function() allows > inexact argument type matches for an aggregate's support functions, > so it could be possible to capture a reference if the intended support > function doesn't exactly match the aggregate's declared input and > transition data types. Should CREATE AGGREGATE support "FINALFUNC = foo(sometype)" input to constrain the lookup? (It does accept the syntax, but "sometype" is unused and need not even denote an extant type.) > 3. As Christoph Berg noted, the fixes in some extension update scripts > mean that plpgsql has to be installed while those scripts run. How > much do we care, and if we do, what should we do about it? I propose not caring at all. Since we have dump/reload of "REVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC", extensions requiring plpgsql are fine. (It could be a problem in a "superuser = false" extension, but core isn't doing those.) Even saddling plpgsql with a pin dependency would be fine. > 4. I noticed while testing that hstore--1.0--1.1.sql is completely > useless nowadays, so it might as well get dropped. It fails with a > syntax error in every still-supported server version, since "=>" is > no longer a legal operator name. There's no way to load hstore 1.0 > into a modern server because of that, either. The chance of this getting reported from the field has been dropping for several years. It's negligible now. Thanks, nm
