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.

Icecold

It will not prevent SQL injections. – Adebayo

Functions do not "contain transactions". They are embedded in a transaction themselves automatically. – Rustication

Answer 1

There is no "privilege on SELECT". All you need is the privilege to EXECUTE functions. Function can also be declared with SECURITY DEFINER to inherit all privileges of the owner. To keep possible privilege escalation at a minimum, make a daemon role with only the necessary privileges own functions in question, 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;  -- NOLOGIN ?
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;

Revoke all privileges from 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. The manual:

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 ..

Rustication

Thank you very much for this detailed answer. I was not aware that something like SECURITY DEFINER exist since I'm fairly new to functions in SQL. Thank you! :) – Icecold

not sure if I missed a step by I also needed to grant access to the mydaemon for TABLES (I did not use the bundle1 group). `GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mydaemonf – Bannerman

@rob: I had only covered future objects in my recipe. I added pre-existing objects to the mix now to address your point. – Rustication

@ErwinBrandstetter what would happen if the SECURITY DEFINER function were owned by postgres and end users were not able to log on with postgres? Would such be OK? – Underestimate

@IamIC; postgres being a superuser, any such function has unlimited privileges. Be very sure that 1: the function only does what it's supposed to; 2: execution is restricted to competent roles; 3: there is no security problem in the code that might be exploited. Rather, don't do it at all in security-sensitive DBs. Whether anybody can log in with the postgres role or not, is not relevant to this issue. – Rustication

I understand. I suppose one could also assign ownership to the schema in scope (not public) and revoke relationship rights as needed. – Underestimate

@ErwinBrandstetter, sorry to ask here... I posted a related question and no one has answered it. If you have some time and are feeling a little brave, would you mind taking a look at it please? #47576519 – Underestimate

This is a wonderful answer, and meets the needs of a project that I am working on. – Annabellannabella