agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedAccess to table only through functions
3+ messages / 2 participants
[nested] [flat]
* Access to table only through functions
@ 2018-05-06 22:51 Alvin D?az <alvin.rd@live.com>
0 siblings, 1 reply; 3+ messages in thread
From: Alvin D?az @ 2018-05-06 22:51 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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.
Sent from my iPhone
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Access to table only through functions
@ 2018-05-07 01:14 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Alvin D?az <alvin.rd@live.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gerardo Herzig @ 2018-05-07 01:14 UTC (permalink / raw)
To: Alvin D?az <alvin.rd@live.com>; +Cc: pgsql-sql@lists.postgresql.org
----- 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Access to table only through functions
@ 2018-05-07 01:56 Alvin D?az <alvin.rd@live.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 3+ messages in thread
From: Alvin D?az @ 2018-05-07 01:56 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-05-07 01:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-05-06 22:51 Access to table only through functions Alvin D?az <alvin.rd@live.com>
2018-05-07 01:14 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2018-05-07 01:56 ` Alvin D?az <alvin.rd@live.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox