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
itslogical representation, preventing any dependency on database structure and vastly increasing portability and
stabilityover time.
Therefore, you might even consider restricting _any_ access to the private schema, enforcing your aforementioned API.
-Klaus
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Alvin Díaz
Sent: Saturday, October 14, 2017 3:37 PM
To: pgsql-sql@postgresql.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
businesslogic is in the DB so I’m trying to find a way to prevent developers can insert directly on tables instead o
directsinsert 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
becauseI was thinking in create functions whit security definer with a user with permission to write in the tables then
createa user for developers allowing then just read all tables and execute the functions.
Best regards.
Alvin Diaz.
Sent from a vastly overpriced smartphone
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql