Thread: Access to table only through functions
I am working in a software but I am using and approach in which business logic is in the database this way programmers onlyhave to worry about what functions they have to call to perform some action or wich view query to get certain data butfor this work perfectly I need to prevent developers to realize direct CRUD operations against the tables so is therea way that I could just allow a db user have permission to execute functions but not to perform actions on tables Example: 1- table_user(id int, name text) 2- function_save_user(id int, name int)....... You can insert using the function but not writing direct insert statement. Sent from my iPhone
----- Mensaje original ----- > De: "Alvin D?az" <alvin.rd@live.com> > Para: pgsql-sql@lists.postgresql.org > Enviados: Domingo, 6 de Mayo 2018 19:51:09 > Asunto: Access to table only through functions > I am working in a software but I am using and approach in which business logic > is in the database this way programmers only have to worry about what functions > they have to call to perform some action or wich view query to get certain data > but for this work perfectly I need to prevent developers to realize direct CRUD > operations against the tables so is there a way that I could just allow a db > user have permission to execute functions but not to perform actions on tables > > Example: > > 1- table_user(id int, name text) > 2- function_save_user(id int, name int)....... > > You can insert using the function but not writing direct insert statement. > What you want is the "security definer" option when create functions. See https://www.postgresql.org/docs/current/static/sql-createfunction.html HTH Gerardo
Thanks for your answer this fix my problem. Sent from my iPhone > On May 6, 2018, at 9:14 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: > > > > ----- Mensaje original ----- >> De: "Alvin D?az" <alvin.rd@live.com> >> Para: pgsql-sql@lists.postgresql.org >> Enviados: Domingo, 6 de Mayo 2018 19:51:09 >> Asunto: Access to table only through functions > >> I am working in a software but I am using and approach in which business logic >> is in the database this way programmers only have to worry about what functions >> they have to call to perform some action or wich view query to get certain data >> but for this work perfectly I need to prevent developers to realize direct CRUD >> operations against the tables so is there a way that I could just allow a db >> user have permission to execute functions but not to perform actions on tables >> >> Example: >> >> 1- table_user(id int, name text) >> 2- function_save_user(id int, name int)....... >> >> You can insert using the function but not writing direct insert statement. >> > What you want is the "security definer" option when create functions. See > https://www.postgresql.org/docs/current/static/sql-createfunction.html > > HTH > Gerardo