Postgres Permissions

Question

I'm having trouble understanding privileges in PostgreSQL.

E.g. I have a web application with the user webapiuser. This user is limited in the sense that it can only action the database through a function of some sort. That means no direct DB CRUD operations, only via functions created by the god postgres user and only functions within its own domain. I've done all sorts of reading with many tutorials point to adding SECURITY DEFINER to each function, but this allows the function to be executed by the function owner on behalf of the user which defeats the purpose in my understanding; I'm trying to lock down this sort of access after all.

I've already created a user with:

CREATE USER webapiuser WITH PASSWORD <password>;

And granted USAGE to all schemas:

GRANT USAGE ON SCHEMA schemaA TO webapiuser;
GRANT USAGE ON SCHEMA schemaB TO webapiuser;
GRANT USAGE ON SCHEMA poublic TO webapiuser;

I've tried adding:

GRANT EXECUTE ON FUNCTION schemaA.func_myfunction() TO webapiuser;

But then I'm hit with permission denied for view view_my_view. The function does select from this view so I guess the GRANT command is working. I get a similar error permission denied for table my_table if I execute a function that performs insert operations. How far down the privileges rabbit hole must I go to perform this seemingly simple task? And if I grant INSERT, UPDATE, DELETE privileges to this user on these tables directly then that defeats the purpose.

Shouldn't granting the user EXECUTE permissions on a function allow it to do anything within the scope of that function?

You either need `SECURITY DEFINER` on the function (and have the owner of the function be a user with the necessary privileges on the used objects), or you need to grant those privileges to the user who is calling the function. There's no way around this. — Bergi, Feb 06 '20 at 19:51
"*no direct DB CRUD operations*" - what exactly do you want the `webapiuser` prevent to do? What is your threat model, which things do you want to protect? — Bergi, Feb 06 '20 at 19:55
@Bergi thanks for your responses. Basically, this `webapiuser` would only ever perform CRUD operations via a function (if in fact that function specifies it). If connected to the database via some sort of access layer, no direct/dynamic SQL should be able to be executed by this user. Is this a misunderstanding of mine? What if I want all DB objects (tables, views, functions) to be created by `postgres` and only want some users to interact with some of them via functions. Also, `webapiuser` should never be able to ALTER, DROP, ADD DB objects (tables, views and functions). — JP Damstra, Feb 06 '20 at 20:12
But what do you gain by restricting the interactions to some functions? Sounds pretty complicated, and the function definitions are hard to maintain (see https://stackoverflow.com/q/1473624/1048572, https://softwareengineering.stackexchange.com/q/194446/66652, https://softwareengineering.stackexchange.com/q/158534/66652). Allowing direct SQL seems much simpler and is definitely more flexible. — Bergi, Feb 06 '20 at 20:23
"*`webapiuser` should never be able to ALTER, DROP, ADD DB objects*" - those are DDL statements, which have their separate privileges anyway. The user won't gain those when you only grant CRUD. — Bergi, Feb 06 '20 at 20:23

score 1 · Accepted Answer · edited Feb 06 '20 at 22:48

Let me start by saying that it surprises me how many people think that it is the best way to use functions for data modification. Apart from the fact that PL/pgSQL function cache plans (which you can also have with prepared statements), I do not see the point.

You should on no account use superuser privileges for anything unless you absolutely have to, that is unnecessary and a security problem.

To describe my suggestion, take three (normal!) users a, b and c:

a is the owner of the schema appschema and the database objects in that schema, that is, a was used to create them. a grants all necessary rights on the objects to b, but none to c.
b owns a set of SECURITY DEFINER functions that perform the data modifications on the tables in appschema. b revokes the EXECUTE privilege on these functions from PUBLIC and grants it to c only.

It is very important that all these functions are defined with SET search_path = appschema.
c is the user that is used by the application. Except for the EXECUTE privilege on b's function, that user has no permissions beyond the right to connect to the database.

Now when c executes any of the functions, they will run with bs user context, that is, they can perform all operations that b can.

Aha! I understand. Thank you for the description using the three users `a`, `b` and `c`. Following you explanation, I have gone and done some fiddling and have been able to create the desired privileges. All seems well. — JP Damstra, Feb 06 '20 at 22:30

Postgres Permissions

1 Answers1