Re: Only insert into tables trough functions

I usually do as you set forth, with a slight extension:
1. place the actual storage layout into a second private schema
2. grant DML access to the private schema to a private role only
3. define the functions to execute as that role in a public schema
This also allows to enforce a database-API comprised of those functions, decoupling actual storage layout (schema) from its logical representation, preventing any dependency on database structure and vastly increasing portability and stability over time.
Therefore, you might even consider restricting _any_ access to the private schema, enforcing your aforementioned API.
-Klaus

From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Alvin Díaz
Sent: Saturday, October 14, 2017 3:37 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Only insert into tables trough functions

Hi community.

I’m working in a project as architect so I’m focused in control as much as possible from the database in other words business logic is in the DB so I’m trying to find a way to prevent developers can insert directly on tables instead o directs insert against data table the should be force to use the prebuilt functions.

If I implement this scheme I make sure that developers follow business logic then I want to listen some suggestions because I was thinking in create functions whit security definer with a user with permission to write in the tables then create a user for developers allowing then just read all tables and execute the functions.

Best regards.
Alvin Diaz.

Sent from a vastly overpriced smartphone