* Ken Tanzer (ken.tanzer@gmail.com) wrote:
>> You realize that some information (like roles/users) is shared
>> cluster-wide and isn't limited to a specific database, right? That's
>> usually where web-hosting folks trip up first..
>>
> I think it's fair to say I realize it, but am perhaps not drawing the
> appropriate conclusions as to what risk this might involve? Please tell
> me why I should care...
Eh, it's just that the users usually complain about seeing other
people's roles and databases and whatnot.. Also, it means you can't
grant 'create role' to anyone, so users can't create or drop their own
roles (an admin will have to handle that) when you have a single shared
cluster environment.
>> Have you looked at what those functions are..? \copy is used to copy a
>> file on the filesystem into the database; \i allows a user to run SQL
>> commands from a file on the filesystem, etc, etc.
> Yes I'm quite familiar with those functions. If I didn't have a
> boatload of scripts depending on "\i", I probably wouldn't care much
> about giving users access to psql in the first place.
What's the issue with port-forwarding (or using SSL/Certificates/etc)
connections to the database and having them run psql locally, if they're
so inclined? Or they could use ODBC to connect from MS Access or
something along those lines.. Depends on your userbase, of course.
>> If you're ok with them having access to the filesystem, what is the issue with giving them
>> a shell?
> It seems to me that executing programs is a whole level of danger above
> and beyond access to the filesystem.
You can mount /home (and any other filesystems they have write access to)
with noexec and then lock down what binaries they have rights to execute
on the other filesystems.. If you use a virtual system solution like I
was mentioning earlier, you could still force their shell to be psql
but be able to worry less over them using \!... Also realize that
there are things like the PAGER being executed by psql when queries end
up returning more than a pager worth of data. Depending on the pager,
it may provide a ! or !-like command.. Guess you could tell your users
"sorry, you can't have a PAGER" and hack that out of psql too.. :)
Thanks,
Stephen