CODEWITHSUNDEEP
postgresqlprivileges
Lem Ko
Lem Ko

Reputation: 423

postgresql privileges Ensuring inserts are only done through functions

Let's say I have a table persons which contains only a name(varchar) and a user client.

I'd like that the only way for client to insert to persons is through the function:

CREATE OR REPLACE FUNCTION add_a_person(a_name varying character)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO persons VALUES(a_name);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

So, I don't want to grant client insert privileges on persons and only give execute privilege for add_a_person. But without doing so, I'd get a permission denied because of the use of insert inside the function.

I have not found a way to this in the postgres documentation about granting privileges. Is there a way to do this?

Upvotes: 4

Views: 2476

Answers (3)

ngreen
ngreen

Reputation: 1759

This is a bit simplistic, but assuming are running 9.2 or later, this is an example of how to check for a single permitted function doing an insert:

CREATE TABLE my_table (col1 text, col2 integer, col3 timestamp);

CREATE FUNCTION my_table_insert_function(col1 text, col2 integer) RETURNS integer AS $$
BEGIN
  INSERT INTO my_table VALUES (col1, col2, current_timestamp);
  RETURN 1;
END $$ LANGUAGE plpgsql;

CREATE FUNCTION my_table_insert_trigger_function() RETURNS trigger AS $$
DECLARE
  stack text;
  fn integer;
BEGIN
  RAISE EXCEPTION 'secured';
EXCEPTION WHEN OTHERS THEN
  BEGIN
    GET STACKED DIAGNOSTICS stack = PG_EXCEPTION_CONTEXT;
    fn := position('my_table_insert_function' in stack);
    IF (fn <= 0) THEN
      RAISE EXCEPTION 'Expecting insert from my_table_insert_function'
        USING HINT = 'Use function to insert data';
    END IF;
    RETURN new;
  END;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER my_table_insert_trigger BEFORE INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE my_table_insert_trigger_function();

And a quick example of usage:

INSERT INTO my_table VALUES ('test one', 1, current_timestamp); -- FAILS
SELECT my_table_insert_function('test one', 1); -- SUCCEEDS

You'll want to peek into the stack in more detail if you want your code to be more robust, secure, etc. Checks for multiple functions are possible, of course, but involve more work. Splitting the stack into multiple lines and parsing it can be fairly involved, so you'll probably want some helper functions if things get more complex.

This is just a proof of concept, but it does what it claims. I would expect this code to be fairly slow given the use of exception handling and stack inspection, so don't use it in performance-critical parts of your application. It's not likely to be suitable for cases where DML statements are frequent, but if security is more important than performance, go for it.

Upvotes: 2

Andrew
Andrew

Reputation: 1147

Matthew's answer is correct in that a SECURITY DEFINER will allow the function to run with the privileges of a different user. Documentation for this is at http://www.postgresql.org/docs/9.1/static/sql-createfunction.html

Why are you trying to implement security this way? If you want to enforce some logic on the inserts, then I would strongly recommend doing it with constraints. http://www.postgresql.org/docs/9.1/static/ddl-constraints.html

If you want substantially higher levels of logic than can be reasonably implemented in constraints, I would suggest looking into building a business logic layer between your presentation layer and the data storage layer. You will find that scalability demands this pretty much instantly.

If your goal is to defend against SQL injection then you have found a way that might work, but that will create a heck of a lot of work for you. Worse, it leads to huge volumes of really mindless code that all has to be kept in sync across schema changes. This is pretty rough if you're trying to do anything agile. Consider instead using a programming framework that takes advantage of PREPARE / EXECUTE, which is pretty much all of them at this point. http://www.postgresql.org/docs/9.0/static/sql-prepare.html

Upvotes: 0

Matthew Wood
Matthew Wood

Reputation: 16417

You can define the function with SECURITY DEFINER. This will allow the function to run for the restricted user as if they had the higher privileges of the function's creator (which needs to be able to insert into the table).

The last line of the definition would look like this:

LANGUAGE plpgsql VOLATILE COST 100 SECURITY DEFINER;

Upvotes: 5

Related Questions