Re: Index build temp files

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Does the user running CREATE INDEX have CREATE permission on those
> tablespaces? (A quick way to double check is to try to SET
> temp_tablespaces to that value explicitly.) The code will silently
> ignore any temp tablespaces you don't have such permission for.

Alright, this isn't quite as open-and-shut as it may have originally
seemed. We're apparently cacheing the temp tablespaces which should be
used, even across set role's and security definer functions, which I
would argue isn't correct.

Attached are two test scripts and results from them. The gist of it is
this:

With the first script, a privileged user creates a temp table and this
table ends up in a tablespace which that user has access to. In the
same session, the user drops privileges using a 'set role' to a less
privileged user (who doesn't have access to the same tablespaces) and
then tries to create a temporary table- boom: permission denied error.

In the second script, an unprivileged user creates a temp table, which
goes into the default tablespace (this is fine- there are other temp
tablespaces, but this user doesn't have access to them), but then calls
a SECURITY DEFINER function, which runs as a privileged user who DOES
have access to the temp tablespaces defined by default. What ends up
happening, however, is that the temporary table created during the
security definer function ends up going into the default tablespace
instead. If the security definer function calls 'set temp_tablespaces',
before creating the temp table, it'll go into the correct tablespace but
after the security definer function ends, if the regular user tries to
create a temporary table they'll get a permission denied error.

I've not gone and looked at any of the code behind this yet (hopefully
will be able to soon). It seems like we need to modify the cacheing
behavior of the temp tablespace code to account for the role that is
currently active. This can't work quite like search_path since we want
to reuse the same tablespace, if possible, for the duration of a
session, per the docs.

Thoughts?

Thanks,

Stephen