Block/allow (specific port) access by country using UFW (Uncomplicated Firewall)

Very clear UFW tutorial:

https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-14-04

Download a text file with all the IP ranges for a given specific country (select CIDR as format):

https://www.ip2location.com/free/visitor-blocker

Save the (text) file and edit it to remove the first lines (the ones that are comments). After that issue a command like the following (the “insert 1” is to insert the new rules at the top of ufw rules list):

while read line; do sudo ufw insert 1 deny from $line to any port 22; done < bad_country.txt

 

Advertisements
Block/allow (specific port) access by country using UFW (Uncomplicated Firewall)

Some useful RSYNC parameter

1) Sinchronize only files younger/older than “x” days (31 ij the following example):

find /path/to/folder -type f -mtime -31 > /tmp/rsyncfiles

or

find /path/to/folder -type f -mtime +31 > /tmp/rsyncfiles

then

rsync -Ravh --files-from=/tmp/rsyncfiles . /target/folder

2) Ignore existing files

rsync --ignore-existing -raz /source/folder/ /target/folder/

3) Update the remote only if a newer version is on the local filesystem

rsync --update -raz /source/folder/ /target/folder/
Some useful RSYNC parameter

Some example of useful PostgreSQL/PostGIS queries

List database triggers:

SELECT * FROM information_schema.triggers;

List tables owned by a specific user:

SELECT *
FROM pg_tables t
WHERE t.tableowner = 'username';

Change permissions on all tables in the same schema:

REVOKE ALL ON ALL TABLES IN SCHEMA schemaname FROM username;

Loop schema names and run a whatever query:

CREATE OR REPLACE FUNCTION change_all()
 RETURNS VOID AS
 $$
 DECLARE rec RECORD;
 BEGIN
-- Get all the schemas
 FOR rec IN
 SELECT DISTINCT schemaname
 FROM pg_catalog.pg_tables
 -- You can exclude the schema which you don't want to drop by adding another condition here
 WHERE schemaname not like 'pg_catalog'
 LOOP
 EXECUTE 'GRANT ALL ON SCHEMA ' || rec.schemaname || ' TO username';
 END LOOP;
 RETURN;
 END;
 $$ LANGUAGE plpgsql;

SELECT change_all();

Add a sequence to an (editable) view:

ALTER VIEW schema.tablename ALTER gid SET DEFAULT nextval('schema.tablename_gid_seq');
Function/trigger to get/save the (database) username when doing an INSERT or UPDATE

CREATE OR REPLACE FUNCTION adduser()
 RETURNS trigger AS
 $BODY$
 BEGIN
 NEW.column_to_be_filled_with_username = session_user;
 RETURN NEW;
 END;
 $BODY$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS data_adduser ON schema.tablename;
 CREATE TRIGGER data_adduser BEFORE UPDATE ON schema.tablename
 FOR EACH ROW
 WHEN (OLD.edited_column IS DISTINCT FROM NEW.edited_column)
 EXECUTE PROCEDURE adduser();

A very simple but effective way of keeping history of changes in a table (backup table structure identical to the one being backed up, plus some other columns to save some additional info):

CREATE OR REPLACE FUNCTION backup_row()
 RETURNS trigger AS
$BODY$
 BEGIN
INSERT INTO backup_table (col1, col2, editor, col3, username, edit_data, geom) 
VALUES (OLD.col1, OLD.col2, OLD.col3, session_user, current_timestamp, OLD.geom);
 RETURN NEW;
 END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION backup_row()
 OWNER TO username;


CREATE TRIGGER backup_row
 BEFORE UPDATE OR DELETE
 ON schema.tablenmae
 FOR EACH ROW
 EXECUTE PROCEDURE backup_row();

Function to add some attribute/geometry validation (see more here: http://twiav-tt.blogspot.pt/2012/07/postgis-trigger-function-retrieve.html)

CREATE OR REPLACE FUNCTION update_observation()
 RETURNS trigger AS
$BODY$
 DECLARE
 vf boolean;
 BEGIN
 -- Check the description of the observation
 IF NEW.description IS NULL THEN
 RAISE EXCEPTION 'Description cannot be empty';
 END IF;

 -- Check the geometry is completely contained within the boundaries of a polygon layer
vf := (SELECT ST_Within(NEW.geom, geom) FROM boundaries WHERE ST_Within(NEW.geom, geom));
 IF vf IS NOT TRUE THEN
 RAISE EXCEPTION 'The new geometry must be completely within the boundaries of the limits layer';
 END IF;

RETURN NEW;
 END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION update_observation()
 OWNER TO username;

A more advanced example on validation and keeping history of changes: http://www.opengis.ch/2018/01/08/postgresql-back-end-solution-for-quality-assurance-and-data-archive/

Some example of useful PostgreSQL/PostGIS queries