Re: Index build temp files

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> The real question is how necessary is it for unprivileged code to set
> temp_tablespaces *for itself*. I doubt that that's all that critical.
> I suspect the variable is hardly used in the field at all, given that
> it's been there since 8.3 and nobody noticed these issues until
> Stephen started poking at it.

I'd like to outline a bit of how we got here and what caused us (at
$work) to discover the issue. Perhaps that will shed some light on the
usefulness of the parameter and how it's used in the field.

We don't currently explicitly set 'temp_tablespaces' anywhere except in
the postgresql.conf.

We have a pretty large ETL process which logs in as an ETL user and
loads the data and then calls a bunch of plpgsql code to process it.
The ETL user just has rights to load the data and we specifically don't
want the ETL user to be able to create objects in the temp tablespaces.
The temp tablespaces, in our view, are intended to exclusively be *only*
temporary objects which don't need to be backed up, etc. We can't
enforce that currently but we would like to. The plpgsql code runs as
security definer and as a more privileged role which does have rights
to the temp tablespaces and we expect that to happen. This more
privileged role certainly is *not* superuser and we'd want to keep it
that way. I can see how we could have a function to call before
creating any objects as this user which runs as superuser to set
temp_tablespaces and then to un-set it later when we exit the function,
but that's a maintenance headache and really feels more complicated
than it should be. Even if it didn't require superuser, going through
and littering the code with places where we have to set temp_tablespace
wouldn't be fun.

Being able to, as a superuser, set the variable for this user to use
these temp tablespaces and another user to use other temp tablespaces
seems like it would work, but it would need to correctly update
temp_tablespaces across security definer and set role's to do what we're
looking for. It's not clear from the proposal being put forth as to if
that would happen or not.

On another system, we actually have things reversed, where we have
temp_tablespaces set in postgresql.conf, but most of the users on the
system have access to those temp_tablespaces and we expect/want them to
use the ones we set, to keep them from filling up the disk that the
default tablespace is on with large queries. Being able to *restrict*
them from using the main tablespace for temporary objects could be nice
there, though we haven't run into any issues (they're not terribly
savvy wrt PG, though I could see that changing if we ended up showing
them how to set temp_tablespaces for themselves for some reason..). We
have run into issues where they've created non-temporary objects in the
temporary tablespaces before though (they're familiar with being able to
specify a tablespace in a 'create table' command or, worse really,
through pgAdmin that a lot of these users use). Generally we can get
away with "well, don't do that", but I do suspect it's only a matter of
time until something happens and someone loses data.

As relates to my initial complaint, I'll be honest, I've not used temp
tablespaces all that much previously. My initial thought was that
'create index's temp files simply didn't respect temp_tablespaces and
so I was just trying to quickly create a test case to post with. I do
agree with Simon that it'd be nice if the user was made aware somehow
about permissions issues regarding temp_tablespaces, presuming we keep
the permission system. Also, for my part, I fully expected something
more like 'create temporary tablespace' rather than a postgresql.conf
setting that tried to act like search_path, but not really because it
does a round-robin on a per-session basis instead of a linear search,
explicitly has the default tablespace added to it (which I don't
consider to be anything like how search_path and pg_catalog work- you
don't create objects in pg_catalog) and isn't kept track of the way
search_path is wrt set role and security definer functions. Indeed,
having it behave the way it does makes me worried that we'd need to deal
with it for security definer functions in the same way that we do search
path and that has never felt like a terribly good situation to me.

Apologies for having a bunch of complaints rather than solutions. I
continue to wonder about simply tracking the role that the temp
tablespace look-up was done as and re-doing that lookup if the role has
changed. That would keep much of the existing semantics (where they
make sense) and the permissions system. Debate about adding other flags
or grants on tablespaces to be denoted as for-temp-objects-only could be
an independent feature proposal.

Thanks,

Stephen