* Tom Lane (t...@sss.pgh.pa.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
signature.asc
Description: Digital signature