DMS EDBMS DATABASE

To review the schema spatially the frontend application is the best option.

Background

Roles

To show all the users and roles:

SELECT
rolname,
rolsuper AS is_superuser,
rolcreaterole AS can_create_role,
rolcreatedb AS can_create_db,
rolcanlogin AS can_login
FROM
    pg_roles;

Admin

Users assigned as ‘admin’ will have access all privileges on all tables in GIS and public schema.

Warning

Be careful when connected as admin !!!! Make backups before any major changes are made.

CREATE ROLE xxx_db_admin;

GRANT ALL PRIVILEGES ON SCHEMA gis TO "xxx_db_admin";
GRANT ALL PRIVILEGES ON SCHEMA public TO "xxx_db_admin";

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx_db_admin;
GRANT SELECT ON ALL TABLES IN SCHEMA gis TO xxx_db_admin;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx_db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA gis TO xxx_db_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA gis GRANT ALL PRIVILEGES ON FUNCTIONS TO "xxx_db_admin";
ALTER DEFAULT PRIVILEGES IN SCHEMA gis GRANT ALL PRIVILEGES ON SEQUENCES TO "xxx_db_admin";
ALTER DEFAULT PRIVILEGES IN SCHEMA gis GRANT ALL PRIVILEGES ON TABLES TO "xxx_db_admin";

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO "xxx_db_admin";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO "xxx_db_admin";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO "xxx_db_admin";

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "inf_db_admin";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA gis TO "inf_db_admin";

User

Users assigned as ‘user’ will have select only privileges on all tables in GIS and public schema.

CREATE ROLE xxx_db_user;

GRANT USAGE ON SCHEMA public TO "xxx_db_user";
GRANT USAGE ON SCHEMA gis TO "xxx_db_user";

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx_db_user;
GRANT SELECT ON ALL TABLES IN SCHEMA gis TO xxx_db_user;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "xxx_db_user";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA gis TO "xxx_db_user";

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "xxx_db_user";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA gis TO "xxx_db_user";

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "xxx_db_user";
ALTER DEFAULT PRIVILEGES IN SCHEMA gis GRANT SELECT ON TABLES TO "xxx_db_user";

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx_db_user;

To check the users has the correct privileges:

SELECT has_table_privilege('jimmy', 'dh_collars', 'SELECT');

Hosting and redundancy

The database server can be hosted on any machine that can run Postgresql (v16).

Data Mining Solutions’ hosting option includes a ‘WAL’ backup replication onto another local server.

It also includes automated daily backups, and weekly offsite copies of the backup files (Each week the latest version will be kept and daily backups for that week will be deleted)

In combination this provides a comprehensive level of data redundancy, incase of a serious data integrity breach.

Security

Access to specific databases are controlled using IP addresses, usernames and encrypted passwords.

/etc/postgresql/16/main/pg_hba.conf

Warning

We should switch from MD5 to SCRAM-SHA-256 for better protection.

Schemas

The are three schemas in the database

Public

Lookups Corporate Assays Downhole Surface Samples Lithology QAQC Views Materialized views Default procedures

GIS

Meta

Building the backend

Database migration

Critical processing database functions

Converting assay values to consistent units:

CREATE OR REPLACE FUNCTION public.convert_text_value_to_numeric(
    text_value text)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    BDL_CASES TEXT[] := ARRAY['n.d.', 'n/d', 'n.d', 'nd', '<lod', '-lod', '< lod', 'lod', 'l.o.d.', 'l.o.d', 'x', 'xx', 'xxx', 'bdl', 'bd', '< bdl', '<bdl', 'b.d.l.'];
    NS_CASES TEXT[] := ARRAY['d.i.p.', 'i.s.', 'i/s', 'is', 'l.n.r.', 'lld', 'lnr', 'missing', 'n/a', 'n/l', 'n/r', 'n/s', 'na', 'nl', 'nr', 'ns', 'nsa', 'nss', 'snr'];
BEGIN
    text_value := REPLACE(text_value, '<', '-');
    text_value := REPLACE(text_value, '>', '');

    IF LOWER(TRIM(text_value)) = ANY(BDL_CASES) THEN
        RETURN -997;
    ELSIF LOWER(TRIM(text_value)) = ANY(NS_CASES) THEN
        RETURN -998;
    ELSE
        -- Attempt to convert the text to numeric
        RETURN text_value::NUMERIC;
    END IF;
EXCEPTION
    WHEN others THEN
        -- In case of any exception (like invalid text format), return NULL or handle as needed
        RETURN -999;
END;
$BODY$;

Converting assay values to consistent units:

CREATE OR REPLACE FUNCTION public.convert_value_due_unit(
    old_unit text,
    new_unit text)
    RETURNS real
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    RETURN CASE
        WHEN old_unit = 'PPM' AND new_unit = 'PPM' THEN value
        WHEN old_unit = 'PPB' AND new_unit = 'PPB' THEN value
        WHEN old_unit = 'PCT' AND new_unit = 'PCT' THEN value
        WHEN old_unit = 'PPT' AND new_unit = 'PPT' THEN value

        WHEN old_unit = 'PPM' AND new_unit = 'PPB' THEN value * 1000
        WHEN old_unit = 'PPB' AND new_unit = 'PPM' THEN value / 1000

        WHEN old_unit = 'PPM' AND new_unit = 'PCT' THEN value / 10000
        WHEN old_unit = 'PCT' AND new_unit = 'PPM' THEN value * 10000

        WHEN old_unit = 'PPM' AND new_unit = 'PPT' THEN value / 1000000
        WHEN old_unit = 'PPT' AND new_unit = 'PPM' THEN value * 1000000

        WHEN old_unit = 'PPB' AND new_unit = 'PCT' THEN value / 10000000
        WHEN old_unit = 'PCT' AND new_unit = 'PPB' THEN value * 10000000

        WHEN old_unit = 'PPB' AND new_unit = 'PPT' THEN value / 1000000
        WHEN old_unit = 'PPT' AND new_unit = 'PPB' THEN value * 1000000

        WHEN old_unit = 'PCT' AND new_unit = 'PPT' THEN value / 10000000000
        WHEN old_unit = 'PPT' AND new_unit = 'PCT' THEN value * 10000000000

        ELSE value
    END;
END;
$BODY$;

Warning

Todo change the processing such that the preference units are used rather than just ppm for all.