pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: "Dmitry G. Mastrukov" Дмитрий Геннадьевич Мастрюков <dmitry@taurussoft.org>
To: Zot O'Connor <zot@zotconsulting.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Execute permsissions on fuctions
Date: 25 Aug 2001 10:08:58 +0400
Message-ID: <998719739.409.52.camel@flame-in-night> (raw)
In-Reply-To: <3B8550E9.3BC47EA7@zotconsulting.com>
References: <3B8550E9.3BC47EA7@zotconsulting.com>

23 Aug 2001 11:52:25 -0700, Zot O'Connor ____
> Other SQL servers have the concept of stored procedures having different
> permissions.
> 
> For instance a procedure that can update a table.
> 
> Since a web site typically connects as the webuser (or equiv postgres
> user), I do not want to offer update to the webuser.
> 
> The way I have done this elsewhere is to create a stored procedure that
> could update the table, and allow the webuser to update the table.  The
> procedure had perms of a user who could update the table, but the
> webuser could not.
> 
> How can I do this in Postgres?
> 
You can do it indirectly. There is patch for 7.1.2 which adds SET
AUTHORIZATION INVOKER/DEFINER clause to PLPGSQL. Sorry, I don't remember
link, you can find it in -sql or -hackers mailing list archives on June
or I can send it to you. Next step is to create some function like (I
use here array iterator from <src>/contrib/array)

CREATE FUNCTION IS_MEMBER(char(32)) RETURNS bool
AS '
DECLARE 
	group_name ALIAS FOR $1;
	sel INTEGER;
BEGIN
	sel := (SELECT COUNT(*) FROM pg_group WHERE grolist *= (SELECT usesysid
FROM pg_user WHERE usename = current_user) AND CAST(groname AS char(32))
= group_name);

	IF sel > 0
	THEN
		return true;
	ELSE
		return false;
	END IF;
END;
' LANGUAGE 'PLPGSQL';

Now you can do something like

  IF IS_MEMBER(''<some_group>'') != true AND USER != ''<definer>''
  THEN
	return NULL;
  END IF;

  SET AUTHORIZATION DEFINER;

on top of your function.

Such thing works fine for me, but I prefer to have clear GRANT EXECUTE
syntax with CREATE FUNCTION func (....) AUTH INVOKER/DEFINER; ability.
IMHO setuid functions with control of who can execute them are very
helpful in implementing database logic. Standart SELECT/UPDATE/DELETE
for views/tables often can be weak.
When I some time ago asked about GRANT EXECUTE someone (Peter or Tom i
cannot recall) told something like "you feel free to send patch to
implement such behaviour".

Regards,
Dmitry





view thread (4+ messages)  latest in thread

Message-ID: <998719739.409.52.camel@flame-in-night>
Permalink:  ../998719739.409.52.camel@flame-in-night/
Also on:    postgresql.org/message-id/998719739.409.52.camel@flame-in-night

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dmitry@taurussoft.org, zot@zotconsulting.com
  Subject: Re: Execute permsissions on fuctions
  In-Reply-To: <998719739.409.52.camel@flame-in-night>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox