Обсуждение: Disallow SET command in a postgresql server
Im planning to publish my postgresql server to a few untrusted clients.
I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all.
Is it possible?
Fabio Rueda Carrascosa <avances123@gmail.com> writes: > Im planning to publish my postgresql server to a few untrusted clients. > I dont want them to modify any runtime setting, like work_mem or something > risky to my server. In general I assume the pg_catalog schema is public but > I don't want to allow updating pg_settings at all. If you're allowing untrustworthy users to execute arbitrary SQL, preventing them from using SET would not make very much difference in how much trouble they can cause. You're wasting your time worrying about this. regards, tom lane
My grant/revoke architecture is fine, you mean about costly cpu/ram queries?
2013/4/9 Tom Lane <tgl@sss.pgh.pa.us>
If you're allowing untrustworthy users to execute arbitrary SQL,Fabio Rueda Carrascosa <avances123@gmail.com> writes:
> Im planning to publish my postgresql server to a few untrusted clients.
> I dont want them to modify any runtime setting, like work_mem or something
> risky to my server. In general I assume the pg_catalog schema is public but
> I don't want to allow updating pg_settings at all.
preventing them from using SET would not make very much difference
in how much trouble they can cause. You're wasting your time worrying
about this.
regards, tom lane
Fabio Rueda Carrascosa escribió: > My grant/revoke architecture is fine, you mean about costly cpu/ram queries? Sure. The SQL dialect supported by Postgres is Turing-complete, so people can write statements that consume arbitrary amounts of RAM and diskspace, and run for arbitrary amounts of time -- regardless of work_mem and other settings. (Actually, this was true even before the dialect got to be Turing-complete). Please don't top-post. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Apr 9, 2013 at 10:57 AM, Fabio Rueda Carrascosa <avances123@gmail.com> wrote: > My grant/revoke architecture is fine, you mean about costly cpu/ram queries? it has nothing to do with grant/revoke. There are multiple trivial things a user can do to DOS you server. You can prevent a lot of them, but it's definitely whack-a-mole. If you don't believe me, try logging into schemaverse in the next few moments. I just took it down. It will come up shortly. The only way I will advise opening up database to untrusted user is through pgbouncer (modified to allow only v3 parameterized queries that match a whitelist). merlin
On Tue, Apr 9, 2013 at 11:13 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Tue, Apr 9, 2013 at 10:57 AM, Fabio Rueda Carrascosa > <avances123@gmail.com> wrote: >> My grant/revoke architecture is fine, you mean about costly cpu/ram queries? > > it has nothing to do with grant/revoke. There are multiple trivial > things a user can do to DOS you server. You can prevent a lot of > them, but it's definitely whack-a-mole. If you don't believe me, try > logging into schemaverse in the next few moments. I just took it > down. It will come up shortly. schemaverse (one of the neatest things on the internet) is now functioning! merlin
On 04/09/2013 09:06 AM, Alvaro Herrera wrote: > > Fabio Rueda Carrascosa escribió: >> My grant/revoke architecture is fine, you mean about costly cpu/ram queries? > > Sure. The SQL dialect supported by Postgres is Turing-complete, so > people can write statements that consume arbitrary amounts of RAM and > diskspace, and run for arbitrary amounts of time -- regardless of > work_mem and other settings. (Actually, this was true even before the > dialect got to be Turing-complete). A simple example that can crush your machine if you aren't careful: select generate_series(1,1000000000000); Now run it on 4 connections. Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579