Обсуждение: Disallow SET command in a postgresql server

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

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