CODEWITHSUNDEEP
postgresqlprivileges
JP Damstra
JP Damstra

Reputation: 613

Postgres Permissions

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?

Upvotes: 1

Views: 1022

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248215

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.

Upvotes: 1

Related Questions