Is there a way to prevent flood insert from user in PosgreSQL? Is there some kind rate limit?

Question

As a part of SQL injection prevention, I have revoked rights on DELETE and UPDATE for the user using the connection. In that way, an attacker cannot harm the integrity of the data even if the bad code allows SQL injection.

Now only left is INSERT. E.g. an attacker can flood insert a particular table, crating a dirty database or taking it down with flood INSERT, potentially taking down the HDD and the server where PostgreSQL is running. All DDL and DCL are already revoked for that user.

So, my question is: is it possible to prevent flood insert, rate-limiting specific connection / session / execution, attempting insert of more than 1 row per 5-10 seconds during the mentioned.

By flood insert I mean something like this:

insert into cars (id, name)
select id, name from cars

-- or if we imagine that id is auto-number.
insert into cars (name)
select name from cars

where user could execute a duplicating of the entire table with one simple line, doubling it with each next execution.

In multiple places, I have read as protection to give the right to function/procedure but to take rights on INSERT/DELETE/UPDATE, but I am not sure how to do it, as as soon you REVOKE rights on INSERT, the procedure will not work either as it is running by the same user.

So I am not quite sure how you prevent Script/Query execution, but you grant function/procedure execution?

If this is possible, can someone give me a practical example? How would you do something like this?

Answer in line of "limit the available RAM for insert" is NOT acceptable.

Thank you,

Answer 1

You have some contradicting requirements between your comment:

I need number of rows inserted in single statement limit

and your question:

rate-limiting specific connection / session / execution attempting insert of more than 1 row per 5-10 seconds

The "rate limit" can't be done without external tools, but the "in single statement limit" part can be achieved with a statement level trigger.

The function checks for the number of rows inserted:

create function limit_insert()
 returns trigger
as
$$
declare
  l_count bigint;
begin
  select count(*)
    into l_count
  from inserted;
  if l_count > 10 then
    raise 'Too many rows inserted!';
  end if;
  return null;
end;
$$
language plpgsql;

And the trigger definition would be:

create trigger limit_insert_rows_trigger
  after insert on the_table
  referencing new table as inserted
  for each statement execute function limit_insert();

Note that the trigger function can be used for any table.