PostgreSQL - the database user should be allowed only call functions

Question

I am currently using PostgreSQL for my application. Since I am trying to put every SQL that contains a transaction (e.g. insert, update, delete) in a function, I came across this problem:

Is it possible for a database user to be allowed to only call functions and Select-Statement until he can invoke SQL statements containing a transaction? By “calling functions” is meant any function. Regardless of whether it contains a transaction or not.

I already tried to create a user that can only call functions and Select-Statement. But I always get an error when calling functions that contain transactions. As far as I understand, dbuser needs write permissions if it calls a function that uses the insert, update, or delete statement.

Am I missing something? Is this scenario really impossible? In security, that would be really cool, because you largely prevent SQL injection in the first place.

Answer 1

No " SELECT privilege". All you need is a privilege for EXECUTE functions. The corresponding function can work with SECURITY DEFINER to inherit all privileges of the owner. To limit the possible escalation of privileges to a minimum a priori, make the role of the corresponding daemon your own corresponding functions only with the necessary privileges - not the superuser!

Recipe

As superuser ...

Create the myuser role myuser .

 CREATE ROLE myuser PASSWORD ...; 

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

 CREATE ROLE mygroup; GRANT mygroup TO myuser; 

You might want to add more users, for example, like myuser later.

Do not grant any privileges at all on myuser .
Provide only 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 should not have.

 REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public; 

There could be more. I quote the manual:

PostgreSQL provides default privileges for some types of public objects. By default, PUBLIC does not grant privileges on tables, columns, schemas, or table spaces. For other types, the default privileges granted by PUBLIC are: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The owner of the object can, of course, REVOKE as the default and explicitly granted privileges. (For maximum security, issuing REVOKE in the same transaction that creates the object; that is, it is not a window in which another user can use the object.) In addition, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.

Create a Demon Role to have the corresponding functions.

 CREATE ROLE mydaemon; 

Grant only the privileges necessary to perform these functions in mydaemon (including EXECUTE ON FUNCTION to allow another function to be called). Again, you can use group roles to bind 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 package or 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 for which it is performed. In the documentation:

If FOR ROLE omitted, the current role is assumed.

Also to cover existing objects in the scheme (see the comment about the failure ):

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

Make mydaemon your own matching features. It might 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 error in the current version 1.16.1 pgAdmin the necessary command

 REVOKE EXECUTE ON FUNCTION foo() FROM public; 

missing in reverse engineering DDL script. Remember to add it when you recreate.
This error is fixed in the current version of pgAdmin 1.18.1.