[GENERAL] 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?
Re: [GENERAL] Disallow SET command in a postgresql server
Fabio Rueda Carrascosa avances...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow SET command in a postgresql server
My grant/revoke architecture is fine, you mean about costly cpu/ram queries? 2013/4/9 Tom Lane t...@sss.pgh.pa.us Fabio Rueda Carrascosa avances...@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
Re: [GENERAL] Disallow SET command in a postgresql server
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow SET command in a postgresql server
On Tue, Apr 9, 2013 at 10:57 AM, Fabio Rueda Carrascosa avances...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow SET command in a postgresql server
On Tue, Apr 9, 2013 at 11:13 AM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Apr 9, 2013 at 10:57 AM, Fabio Rueda Carrascosa avances...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow SET command in a postgresql server
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,1); 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
