[lug] postgres question

[P. Scott DeVos]

rm at fabula.de wrote:
> On Thu, Nov 13, 2003 at 08:09:59AM -0500, Hugh Brown wrote:
> 
>>[...]
>>is tedious.  I falsely assumed that 
>>
>>GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL .....
>>
>>meant that I could do a grant on DATABASE dbname and have all
>>tables,views,sequences, etc. automatically get their privileges
>>updated.  Postgres is version 7.3.2 (built from the redhat rpms for rh9
>>I believe).
> 
> 
> 
> Yes, that's a nasty bit of postgres' security implementation.
> An, unfortunately, it's not possible to replace the relation
> name with a subquery (the query parser will grok on "GRANT ALL 
> ON (SELECT relname FROM pg_class ...").

By "grok" to you mean "fully understand" a' la Heinlein? Or do you mean 
fail?

> 
> Tell me if you find a workarround, i was bitten by that myself
> a few times.
> 
>   Ralf Mattes
> 

Here is the solution I use.  It grants only select and insert on tables 
that end in 'log' and all on everything else.  You will need to modify 
it to handle your sequences--I set all mine up in a logical way so that 
I can use a bit of code like this to do my grants.

The following code is copyright 2003 P. Scott DeVos and is licensed to 
you under the BSD license which is widely published.  No warranty is 
offered or implied.

create or replace function grant_privileges(text) returns bool as'
	declare
		action		text;
		tbl_name	text;
		tbl_len		integer;
		seq_name	text;
		sel_act		text;
		tbl_info 	record;
		the_user 	alias for $1;
	begin
		sel_act := ''select tablename from pg_tables '' ||
				''where schemaname = ''''public'''';'';
		raise debug ''%'',sel_act;
		for tbl_info in execute sel_act loop

			tbl_name = tbl_info.tablename;
			tbl_len = char_length(tbl_name);

			if substring(tbl_name from tbl_len-2 for 3)
				= ''log'' then
				action := ''grant select,insert on '' ||
					quote_ident(tbl_name) ||
					'' to '' || the_user;
			else
				action := ''grant all on '' ||
					quote_ident(tbl_name) ||
					'' to '' || the_user;
			end if;
			raise debug ''%'',action;
			execute action;

			if substring(tbl_name from tbl_len-2 for 3)
				 = ''log'' then
				seq_name := tbl_name || ''_log_id_seq'';
			else
				seq_name := tbl_name || ''_record_id_seq'';
			end if;
			action := ''grant update on '' ||
				quote_ident(seq_name) ||
				'' to '' || the_user;
			raise debug ''%'',action;
			execute action;

		end loop;

		return true;
	end;
' language 'plpgsql';

select grant_privileges();




-- 
P. Scott DeVos