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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
My grant/revoke architecture is fine, you mean about costly cpu/ram queries?
2013/4/9 Tom Lane <tgl@sss.pgh.pa.us>
Show quoted text
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 orsomething
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general