PostgreSQL - DB user should only be allowed to call functions

Question

Currently I'm using PostgreSQL for my application. Since I am trying to put every SQL that contains a transaction (i.e. insert, update, delete) in a function, I stumbled upon this problem:

Is it possible that a database user may only be allowed to call functions and Select-Statements while he can not call SQL-Statements which contains a transaction? By "call functions" I mean any function. Regardless if it contains a transaction or not.

I already tried to create a user which can only call functions and Select-Statements. But I always end up with an error, when calling functions which contains transactions. For what I understand a dbuser needs write permissions if a he calls a function which uses an insert, update or delete statement.

Am I missing something? Is this scenario really not possible? Security-wise this would be really great because you pretty much prevent SQL-injection in the first place.

Answer 1

There is no "privilege on SELECT". All you need is the privilege to EXECUTE functions. Relevant function can run with SECURITY DEFINER to inherit all privileges of the owner. To restrict possible privilege escalation to a minimum a priori, make a daemon role own relevant functions with only the necessary privileges - not a superuser!

Recipe

As superuser ...

Create a non-superuser role myuser.

CREATE ROLE myuser PASSWORD ...;

Create a group role mygroup and make myuser member in it.

CREATE ROLE mygroup;
GRANT mygroup TO myuser;

You may want to add more users just like myuser later.

Do not grant any privileges at all to myuser.
Only grant these to mygroup:

• GRANT CONNECT ON DATABASE mydb TO mygroup;
• GRANT USAGE ON SCHEMA public TO mygroup;
• GRANT EXECUTE ON FUNCTION foo() TO mygroup;

Remove all privileges for public that myuser shouldn't have.

REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;

There may be more. I quote the manual:

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.

Create a daemon role to own relevant functions.

CREATE ROLE mydaemon;

Grant only privileges necessary to execute these functions to mydaemon, (including EXECUTE ON FUNCTION to allow another function to be called). Again, you can use group roles to bundle privileges and grant them to mydaemon

GRANT bundle1 TO mydaemon;

In addition you can use DEFAULT PRIVILEGES to automatically grant certain privileges for future objects to a bundle or the daemon directly:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES    TO bundle1;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE  ON SEQUENCES TO bundle1;

This applies only to the role it is executed for. Per the documentation:

If FOR ROLE is omitted, the current role is assumed.

To also cover pre-existing objects in the schema (see rob's comment):

GRANT SELECT ON ALL TABLES    IN SCHEMA public TO bundle1;
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO bundle1;

Make mydaemon own relevant functions. Could look like this:

CREATE OR REPLACE FUNCTION foo()
  ...
SECURITY DEFINER SET search_path = myschema, pg_temp;

ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT  EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT  EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..

###Note
Due to this bug in the current version 1.16.1 of pgAdmin the necessary command

REVOKE EXECUTE ON FUNCTION foo() FROM public;

is missing in the reverse engineered DDL script. Remember to add it, when recreating.
This bug is fixed in the current version pgAdmin 1.18.1.