#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Row level permissions issue


    I'm trying to set up row-level permissions in postgresql, by using rules. I do this by creating a view first and then writing insert or update and delete rules in which these operations carried out on the view are transferred to the actual table using the permissions that the creator of the view (the administrator in this case) has.

    It works well, except for one issue - the transfer of default values. It seems as though if you write a rule such as

    create rule... as on insert to view1 do instead insert into table1 (column1,column2...) values (new.column1,new.column2...)

    if say column2 has a default value in table1, and the user is inserting default into view1, the default does not get transferred to table1. What happens instead is that the default gets replaced by the default if any, in view1 (there is none, of course) and that is inserted into table1. So the default value in table1 is in this way ignored.

    Does anyone know of any solution to this problem?

    The absence of inbuilt row-level permissions in postgresql is I think regrettable, but solvable. But I haven't been able to get round the above.

    Many thanks in advance.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0
    Hi there,

    We do something similar... a range of views in the public schema that refer to tables in a more private 'master' schema. Access to rows in the master schema goes via a permissions system. It's all fairly complex but works surprisingly well in most cases except for mass updates or mass deletes, where permissions are checked per-row.

    Without providing the entire system, here is the function 'create_view' that can be run on any table (in the public or master schema), moves it to master if necessary then creates a view, insert, update and delete rules. It deals with default values by looking up info about the table in the information_schema and automatically creates the insert rule with coalesces of the default value for any column that has a default. The function is written in perl and needs the plperl language installed in the database.

    If you or anyone has any updates or suggestions please let me know. If it gets crazy I'll github the project.

    It requires a separate function called has_permission (that checks permissions), permission_trigger (on the master table that auto-updates the permissions table), grant_permission (that grants permission to a row)

    Code:
    CREATE OR REPLACE FUNCTION create_view (character varying,
    					character varying,
    					character varying)   RETURNS boolean
    	LANGUAGE plperl
    	AS $_X$
    	my ($table, $key, $op_key) = @_;
    	my $sql;
    
    	$sql = qq{
    		select * from information_schema.tables where table_name = '$table'
    	};
    	my $rv = spi_exec_query($sql);
    	my $drop_current_view = 0;
    	foreach my $row (@{$rv->{rows}}) {
    		if ($row->{table_schema} eq 'public' && $row->{table_type} eq 'BASE TABLE') {
    			$sql = qq{
    				alter table $table set schema master
    			};
    			my $srv = spi_exec_query($sql);
    		}
    		if ($row->{table_schema} eq 'public' && $row->{table_type} eq 'VIEW') {
    			$drop_current_view = 1;
    		}
    		
    	}
    
    	if ($drop_current_view) {
    		$sql = 	qq{
    			drop view public.$table
    		};
    		my $srv = spi_exec_query($sql);
    	}
    
    	$sql = qq{
    		drop trigger if exists permission_trigger_$key$table on master.$table cascade
    	};
    	my $srv = spi_exec_query($sql);
    
    	$sql = qq{
    		create trigger permission_trigger_$key$table after INSERT or DELETE or UPDATE of $key
    			on master.$table 
    			for each row execute procedure master.permission_trigger('$key')
    	};
    	$srv = spi_exec_query($sql);
    	
    
    	my $key_as_varchar = $key.'::varchar';
    	$sql = qq{
    		create view public.$table as 
    			select master.$table.* from master.$table
    			where true in (select permission_read 
    					from master.permissions 
    					where permission_table = '$table' and
    						permission_field = '$key' and
    						permission_key = $key_as_varchar and
    						(permission_region_id = session_user or
    						 permission_region_id = 'ALL' or
    						 permission_region_id in 
    							(select region_id from master.regions 
    							where region_parentage_tree ilike '%'||session_user||'%')));
    
    	};
    	$srv = spi_exec_query($sql);
    
    	$sql = qq{
    		grant all on public.$table to public
    	};
    	$srv = spi_exec_query($sql);
    
    	$sql = qq{
    		select grant_permission('ALL', '$table', '$key', $key_as_varchar, 'true') from master.$table
    	};
    	$srv = spi_exec_query($sql);
    
    
    	my @old_columns; my @new_columns; my @upds;
    	$sql = qq{
    		select * from information_schema.columns where table_name = '$table' and table_schema = 'master'
    	};
    	$rv = spi_exec_query($sql);
    	foreach my $row (@{$rv->{rows}}) {
    		push(@old_columns, $row->{column_name});
    		my $new = 'new.'.$row->{column_name};
    		$new = "coalesce($new, $row->{column_default})" if ($row->{column_default});
    		warn "column default activated as $new" if ($row->{column_default});
    		push(@new_columns, $new);
    		push(@upds, "$row->{column_name} = new.$row->{column_name}");
    	}
    	my $insquery = "INSERT INTO master.$table (".join(',', @old_columns).") VALUES (" . join(',', @new_columns) . ')';
    	my $updquery = "UPDATE master.$table SET ".join(',', @upds) . " WHERE ($table.$op_key = old.$op_key)";
    
    	$sql = qq {
    		CREATE RULE insert_$table AS ON INSERT TO $table DO INSTEAD 
    			$insquery 
    	};
    	$srv = spi_exec_query($sql);
    	$sql = qq {
    		CREATE RULE update_$table AS ON UPDATE TO $table DO INSTEAD 
    			$updquery and has_permission('update', '$table', '$key', OLD.$key_as_varchar);
    	};
    
    	$srv = spi_exec_query($sql);
    	$sql = qq {
    		CREATE RULE delete_$table AS ON DELETE TO $table DO INSTEAD 
    			DELETE from master.$table where $op_key = OLD.$op_key and
    				has_permission('delete', '$table', '$key', OLD.$key_as_varchar);
    	};
    	$srv = spi_exec_query($sql);
    	return "true";
    
    $_X$;
    
    
    CREATE OR REPLACE FUNCTION create_view (character varying,
    					character varying) RETURNS BOOLEAN
    	as 'select create_view($1, $2, $2);'
    	LANGUAGE SQL;
    Run with
    Code:
    select create_view('tablename', 'primary key column');
    If the primary key and the permission-gating column are the same, or

    Code:
    select create_view('tablename', 'permission column', 'primary key column');
    If there is a different column that the permissions are based off.

IMN logo majestic logo threadwatch logo seochat tools logo