20

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
lefthy
  • 303
  • 1
  • 2
  • 5

1 Answers1

37

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 ..
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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! :) – lefthy Apr 09 '13 at 14:40
  • 1
    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 – rob May 20 '17 at 15:44
  • 1
    @rob: I had only covered `future objects` in my recipe. I added pre-existing objects to the mix now to address your point. – Erwin Brandstetter May 20 '17 at 16:53
  • @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? – IamIC Nov 30 '17 at 12:14
  • 1
    @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. – Erwin Brandstetter Nov 30 '17 at 13:44
  • I understand. I suppose one could also assign ownership to the schema in scope (not `public`) and revoke relationship rights as needed. – IamIC Nov 30 '17 at 14:11
  • @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? https://stackoverflow.com/questions/47576019/figuring-out-grant-and-revoke-for-multiple-roles-schemas-in-postgresql-10 – IamIC Nov 30 '17 at 17:15
  • This is a wonderful answer, and meets the needs of a project that I am working on. – JosephDoggie Nov 09 '21 at 19:40