Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fFUkC-0004js-9g for pgsql-sql@arkaria.postgresql.org; Mon, 07 May 2018 01:15:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fFUkA-0006Ll-VX for pgsql-sql@arkaria.postgresql.org; Mon, 07 May 2018 01:15:10 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fFUkA-0006Jq-OX for pgsql-sql@lists.postgresql.org; Mon, 07 May 2018 01:15:10 +0000 Received: from mail.fmed.uba.ar ([157.92.152.1] helo=azteca.fmed.uba.ar) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fFUjZ-00017Y-M6 for pgsql-sql@lists.postgresql.org; Mon, 07 May 2018 01:14:38 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by azteca.fmed.uba.ar (Postfix) with ESMTP id A7BB51F6264C; Sun, 6 May 2018 22:14:27 -0300 (-03) Received: from azteca.fmed.uba.ar ([127.0.0.1]) by localhost (azteca.fmed.uba.ar [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id 56f7xWrWTZRZ; Sun, 6 May 2018 22:14:25 -0300 (-03) Received: from localhost (localhost.localdomain [127.0.0.1]) by azteca.fmed.uba.ar (Postfix) with ESMTP id CCD511F6264E; Sun, 6 May 2018 22:14:25 -0300 (-03) X-Virus-Scanned: amavisd-new at fmed.uba.ar Received: from azteca.fmed.uba.ar ([127.0.0.1]) by localhost (azteca.fmed.uba.ar [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id dyhJ5_gWmwC0; Sun, 6 May 2018 22:14:25 -0300 (-03) Received: from azteca.fmed.uba.ar (azteca.fmed.uba.ar [157.92.152.1]) by azteca.fmed.uba.ar (Postfix) with ESMTP id A5AA91F6264C; Sun, 6 May 2018 22:14:25 -0300 (-03) Date: Mon, 7 May 2018 01:14:25 +0000 (UTC) From: Gerardo Herzig To: Alvin D?az Cc: pgsql-sql@lists.postgresql.org Message-ID: <1639829908.381388.1525655665344.JavaMail.zimbra@fmed.uba.ar> In-Reply-To: <1203421770.381387.1525655659092.JavaMail.zimbra@fmed.uba.ar> References: Subject: Re: Access to table only through functions MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [157.92.152.1] X-Mailer: Zimbra 8.5.1_GA_3056 (ZimbraWebClient - FF59 (Win)/8.5.1_GA_3056) Thread-Topic: Access to table only through functions Thread-Index: AQHT5Yy5UP4dclPF8UW23PjxnUHopYlsneDt List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ----- Mensaje original ----- > De: "Alvin D?az" > 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