From a9a0e43de5eb84b32124371157a96634d5c8857e Mon Sep 17 00:00:00 2001
From: Kevin Day <thekevinday@gmail.com>
Date: Sat, 1 Apr 2017 01:05:11 -0500
Subject: [PATCH] Update: rework access control to be automatic based on is_*
 roles in the t_users table

Instead of trying to use select queries on every statement based on a users role, have the database automatical grant/revoke roles based on column settings in the s_tables.t_users table.
u_public and r_public have been renamed to be u_reservation_public and r_reservation_public to keep all roles self-contained.
name_machine fields should now only allow word characters.
---
 .../sql/reservation/reservation-associations.sql   |   1 +
 database/sql/reservation/reservation-dates.sql     |   3 +-
 database/sql/reservation/reservation-fields.sql    |   3 +-
 database/sql/reservation/reservation-files.sql     |   3 +-
 database/sql/reservation/reservation-first.sql     |  92 ++---
 database/sql/reservation/reservation-groups.sql    |   9 +-
 database/sql/reservation/reservation-last.sql      |  23 +-
 database/sql/reservation/reservation-legal.sql     |   3 +-
 .../sql/reservation/reservation-log_problems.sql   |   2 +-
 database/sql/reservation/reservation-log_types.sql |   6 +-
 database/sql/reservation/reservation-log_users.sql |  10 +-
 database/sql/reservation/reservation-main.sql      |   2 +-
 database/sql/reservation/reservation-paths.sql     |   8 +-
 database/sql/reservation/reservation-requests.sql  |  12 +-
 .../sql/reservation/reservation-statistics.sql     |   6 +-
 database/sql/reservation/reservation-types.sql     |  11 +-
 database/sql/reservation/reservation-users.sql     | 372 ++++++++++++++++++++-
 documentation/access_roles.txt                     |  18 +-
 examples/test.php                                  |  26 +-
 program/reservation/index.php                      |   6 +-
 program/reservation/reservation_paths.php          |   2 +-
 21 files changed, 468 insertions(+), 150 deletions(-)

diff --git a/database/sql/reservation/reservation-associations.sql b/database/sql/reservation/reservation-associations.sql
index de81074..b33ace5 100644
--- a/database/sql/reservation/reservation-associations.sql
+++ b/database/sql/reservation/reservation-associations.sql
@@ -48,6 +48,7 @@ create table s_tables.t_associations (
   constraint cu_associations_name_machine unique (name_machine),
 
   constraint cc_associations_id check (id > 0),
+  constraint cc_associations_name_machine check (name_machine ~ '\w+'),
 
   constraint cf_associations_manager foreign key (id_manager) references s_tables.t_users (id) on delete restrict on update cascade,
   constraint cf_associations_creator foreign key (id_creator) references s_tables.t_users (id) on delete restrict on update cascade,
diff --git a/database/sql/reservation/reservation-dates.sql b/database/sql/reservation/reservation-dates.sql
index 102c042..cc016e4 100644
--- a/database/sql/reservation/reservation-dates.sql
+++ b/database/sql/reservation/reservation-dates.sql
@@ -30,7 +30,8 @@ create table s_tables.t_date_contexts (
   constraint cu_date_contexts_id unique (id),
   constraint cu_date_contexts_name_machine unique (name_machine),
 
-  constraint cc_date_contexts_id check (id >= 0)
+  constraint cc_date_contexts_id check (id >= 0),
+  constraint cc_date_contexts_name_machine check (name_machine ~ '\w+')
 );
 
 create sequence s_tables.se_date_contexts_id owned by s_tables.t_date_contexts.id;
diff --git a/database/sql/reservation/reservation-fields.sql b/database/sql/reservation/reservation-fields.sql
index a8ca736..035569a 100644
--- a/database/sql/reservation/reservation-fields.sql
+++ b/database/sql/reservation/reservation-fields.sql
@@ -30,7 +30,8 @@ create table s_tables.t_field_affiliations (
   constraint cu_field_affiliations_id unique (id),
   constraint cu_field_affiliations_name_machine unique (name_machine),
 
-  constraint cc_field_affiliations_id check (id > 0)
+  constraint cc_field_affiliations_id check (id > 0),
+  constraint cc_field_affiliations_name_machine check (name_machine ~ '\w+')
 );
 
 create sequence s_tables.se_field_affiliations_id owned by s_tables.t_field_affiliations.id;
diff --git a/database/sql/reservation/reservation-files.sql b/database/sql/reservation/reservation-files.sql
index b3c8bbc..953ddf3 100644
--- a/database/sql/reservation/reservation-files.sql
+++ b/database/sql/reservation/reservation-files.sql
@@ -40,6 +40,7 @@ create table s_tables.t_files (
   constraint cu_files_field_path unique (field_path),
 
   constraint cc_files_id check (id > 0),
+  constraint cc_files_name_machine check (name_machine ~ '\w+'),
 
   constraint cf_files_id_creator foreign key (id_creator) references s_tables.t_users (id) on delete cascade on update cascade,
   constraint cf_files_id_creator_session foreign key (id_creator_session) references s_tables.t_users (id) on delete cascade on update cascade,
@@ -79,7 +80,7 @@ create view public.v_files with (security_barrier=true) as
   select id, id_type, NULL::bigint as id_group, name_machine, name_human, NULL::bool as is_private, NULL::bool as date_created, NULL::bool as date_changed from s_tables.t_files
   where not is_deleted and not is_locked and not is_private;
 
-grant select on public.v_path_types to r_reservation, r_public, r_reservation_system;
+grant select on public.v_path_types to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_files_date_changed_deleted_or_locked
diff --git a/database/sql/reservation/reservation-first.sql b/database/sql/reservation/reservation-first.sql
index 5ab1e4d..39f5df3 100644
--- a/database/sql/reservation/reservation-first.sql
+++ b/database/sql/reservation/reservation-first.sql
@@ -2,7 +2,7 @@
 /** Things here must be run first and cannot be run a second time due to their nature. **/
 /** For example, tablespaces need only be created 1x and then any database on the system can use them **/
 /** Be sure to replace reservation_ and reservation- with the prefix that is specific to your database. **/
-/** This script creates the database at the bottom. **/
+/** This script creates the database (at or near the end of the script). **/
 
 
 
@@ -19,55 +19,38 @@ create role r_reservation_editor inherit nologin;
 create role r_reservation_drafter inherit nologin;
 create role r_reservation_requester inherit nologin;
 create role r_reservation_system inherit nologin;
-create role r_reservation_revision_requests inherit nologin;
-create role r_reservation_statistics_update inherit nologin;
-create role r_reservation_logger inherit nologin;
-create role r_reservation_groups_handler inherit nologin;
-
-
-grant r_reservation to r_reservation_administer with admin option;
-grant r_reservation to r_reservation_manager with admin option;
-
-grant r_reservation_manager to r_reservation_administer with admin option;
-
-grant r_reservation_auditor to r_reservation_administer with admin option;
-grant r_reservation_auditor to r_reservation_manager with admin option;
-
-grant r_reservation_publisher to r_reservation_administer with admin option;
-grant r_reservation_publisher to r_reservation_manager with admin option;
-
-grant r_reservation_insurer to r_reservation_administer with admin option;
-grant r_reservation_insurer to r_reservation_manager with admin option;
-
-grant r_reservation_financer to r_reservation_administer with admin option;
-grant r_reservation_financer to r_reservation_manager with admin option;
-
-grant r_reservation_reviewer to r_reservation_administer with admin option;
-grant r_reservation_reviewer to r_reservation_manager with admin option;
-
-grant r_reservation_editor to r_reservation_administer with admin option;
-grant r_reservation_editor to r_reservation_manager with admin option;
-
-grant r_reservation_drafter to r_reservation_administer with admin option;
-grant r_reservation_drafter to r_reservation_manager with admin option;
-
-grant r_reservation_requester to r_reservation_administer with admin option;
-grant r_reservation_requester to r_reservation_manager with admin option;
-
-/** This is the role the database should use to connect to to perform system activity **/
-create role u_reservation inherit login;
-
-grant r_reservation to u_reservation;
+create role r_reservation_public inherit nologin;
+
+/* special use roles neither to be assigned directly nor are they added to the t_users table. */
+create role u_reservation_revision_requests inherit nologin;
+create role u_reservation_statistics_update inherit nologin;
+create role u_reservation_logger inherit nologin;
+create role u_reservation_groups_handler inherit nologin;
+create role u_reservation_grant_roles inherit nologin;
+
+
+grant r_reservation to u_reservation_grant_roles with admin option;
+grant r_reservation_administer to u_reservation_grant_roles with admin option;
+grant r_reservation_manager to u_reservation_grant_roles with admin option;
+grant r_reservation_auditor to u_reservation_grant_roles with admin option;
+grant r_reservation_publisher to u_reservation_grant_roles with admin option;
+grant r_reservation_insurer to u_reservation_grant_roles with admin option;
+grant r_reservation_financer to u_reservation_grant_roles with admin option;
+grant r_reservation_reviewer to u_reservation_grant_roles with admin option;
+grant r_reservation_editor to u_reservation_grant_roles with admin option;
+grant r_reservation_drafter to u_reservation_grant_roles with admin option;
+grant r_reservation_requester to u_reservation_grant_roles with admin option;
+grant r_reservation_system to u_reservation_grant_roles with admin option;
+grant r_reservation_public to u_reservation_grant_roles with admin option;
 
 
 /** This is the role and role group the database should use for anonymous traffic. I further suggest setting up pg_hba.conf to allow non-ssl connections (increase performance as public information is still public). **/
 /** If the data is meant to be private, then have the public account use the system user with ssl connections **/
-create role r_public inherit nologin;
-create role u_public inherit login;
+create role u_reservation_public inherit login;
 
-grant r_public to u_public;
 
 /** These are the roles and role group the database should use for system activity (such as executing cron-jobs). **/
+create role u_reservation_system inherit login;
 create role u_reservation_system_administer inherit login;
 create role u_reservation_system_manager inherit login;
 create role u_reservation_system_auditor inherit login;
@@ -80,29 +63,6 @@ create role u_reservation_system_drafter inherit login;
 create role u_reservation_system_requester inherit login;
 create role u_reservation_system_public inherit login;
 
-grant r_reservation_system to u_reservation_system_administer;
-grant r_reservation_system to u_reservation_system_manager;
-grant r_reservation_system to u_reservation_system_auditor;
-grant r_reservation_system to u_reservation_system_publisher;
-grant r_reservation_system to u_reservation_system_insurer;
-grant r_reservation_system to u_reservation_system_financer;
-grant r_reservation_system to u_reservation_system_reviewer;
-grant r_reservation_system to u_reservation_system_editor;
-grant r_reservation_system to u_reservation_system_drafter;
-grant r_reservation_system to u_reservation_system_requester;
-grant r_reservation_system to u_reservation_system_public;
-
-grant r_reservation_administer to u_reservation_system_administer;
-grant r_reservation_manager to u_reservation_system_manager;
-grant r_reservation_auditor to u_reservation_system_auditor;
-grant r_reservation_publisher to u_reservation_system_publisher;
-grant r_reservation_insurer to u_reservation_system_insurer;
-grant r_reservation_financer to u_reservation_system_financer;
-grant r_reservation_reviewer to u_reservation_system_reviewer;
-grant r_reservation_editor to u_reservation_system_editor;
-grant r_reservation_drafter to u_reservation_system_drafter;
-grant r_reservation_requester to u_reservation_system_requester;
-
 
 
 /** Create and connect to the database **/
diff --git a/database/sql/reservation/reservation-groups.sql b/database/sql/reservation/reservation-groups.sql
index 25d79ed..a004ac0 100644
--- a/database/sql/reservation/reservation-groups.sql
+++ b/database/sql/reservation/reservation-groups.sql
@@ -49,6 +49,7 @@ create table s_tables.t_groups (
 
   constraint cc_groups_id check (id > 0),
   constraint cc_groups_id_external check (id_external >= -1),
+  constraint cc_groups_name_machine check (name_machine ~ '\w+'),
 
   constraint cu_groups_id_external unique (id_external),
   constraint cu_groups_name_machine unique (name_machine),
@@ -59,10 +60,10 @@ create table s_tables.t_groups (
 create sequence s_tables.se_groups_id owned by s_tables.t_groups.id;
 alter table s_tables.t_groups alter column id set default nextval('s_tables.se_groups_id'::regclass);
 
-grant select,insert,update on s_tables.t_groups to r_reservation_manager, r_reservation_groups_handler;
+grant select,insert,update on s_tables.t_groups to r_reservation_manager, u_reservation_groups_handler;
 grant select on s_tables.t_groups to r_reservation_auditor;
 grant select,usage on s_tables.se_groups_id to r_reservation_manager;
-grant usage on s_tables.se_groups_id to r_reservation, r_reservation_system, r_reservation_groups_handler;
+grant usage on s_tables.se_groups_id to r_reservation, r_reservation_system, u_reservation_groups_handler;
 
 /* Note: id_sort is only needed when directly validating against id or name_machine because both of those are already an index. */
 create index i_groups_id_sort_a on s_tables.t_groups (id_sort) with (fillfactor = 100) where id_sort = 97;
@@ -125,7 +126,7 @@ create function s_administers.f_groups_group_user_insert() returns trigger secur
   end;
 $$ language plpgsql;
 
-alter function s_administers.f_groups_group_user_insert () owner to r_reservation_groups_handler;
+alter function s_administers.f_groups_group_user_insert () owner to u_reservation_groups_handler;
 
 create function s_administers.f_groups_group_user_update() returns trigger security definer as $$
   begin
@@ -141,7 +142,7 @@ create function s_administers.f_groups_group_user_update() returns trigger secur
   end;
 $$ language plpgsql;
 
-alter function s_administers.f_groups_group_user_update () owner to r_reservation_groups_handler;
+alter function s_administers.f_groups_group_user_update () owner to u_reservation_groups_handler;
 
 
 create trigger tr_groups_group_user_insert
diff --git a/database/sql/reservation/reservation-last.sql b/database/sql/reservation/reservation-last.sql
index ec489ea..a1cbd68 100644
--- a/database/sql/reservation/reservation-last.sql
+++ b/database/sql/reservation/reservation-last.sql
@@ -355,17 +355,18 @@ alter sequence s_tables.se_users_id restart;
 
 
 /*** create hard-coded/internal user ids ***/
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (3, 'u_system_administer', (null, 'System', null, 'Administer', null, 'System (Administer)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (4, 'u_system_manager', (null, 'System', null, 'Manager', null, 'System (Manager)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (5, 'u_system_auditor', (null, 'System', null, 'Auditor', null, 'System (Auditor)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (6, 'u_system_publisher', (null, 'System', null, 'Publisher', null, 'System (Publisher)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (7, 'u_system_insurer', (null, 'System', null, 'Insurer', null, 'System (Insurer)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (8, 'u_system_financer', (null, 'System', null, 'Financer', null, 'System (Financer)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (9, 'u_system_reviewer', (null, 'System', null, 'Reviewer', null, 'System (Reviewer)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (10, 'u_system_editor', (null, 'System', null, 'Editor', null, 'System (Editor)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (11, 'u_system_drafter', (null, 'System', null, 'Drafter', null, 'System (Drafter)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (12, 'u_system_requester', (null, 'System', null, 'Requester', null, 'System (Requester)'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_public) values (13, 'u_system_public', (null, 'System', null, 'Public', null, 'System (Public)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_administer) values (3, 'u_reservation_system_administer', (null, 'System', null, 'Administer', null, 'System (Administer)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_manager) values (4, 'u_reservation_system_manager', (null, 'System', null, 'Manager', null, 'System (Manager)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_auditor) values (5, 'u_reservation_system_auditor', (null, 'System', null, 'Auditor', null, 'System (Auditor)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_publisher) values (6, 'u_reservation_system_publisher', (null, 'System', null, 'Publisher', null, 'System (Publisher)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_insurer) values (7, 'u_reservation_system_insurer', (null, 'System', null, 'Insurer', null, 'System (Insurer)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_financer) values (8, 'u_reservation_system_financer', (null, 'System', null, 'Financer', null, 'System (Financer)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_reviewer) values (9, 'u_reservation_system_reviewer', (null, 'System', null, 'Reviewer', null, 'System (Reviewer)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_editor) values (10, 'u_reservation_system_editor', (null, 'System', null, 'Editor', null, 'System (Editor)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_drafter) values (11, 'u_reservation_system_drafter', (null, 'System', null, 'Drafter', null, 'System (Drafter)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_requester) values (12, 'u_reservation_system_requester', (null, 'System', null, 'Requester', null, 'System (Requester)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system, is_public) values (13, 'u_reservation_system_public', (null, 'System', null, 'Public', null, 'System (Public)'), false, true, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (14, 'u_reservation_system', (null, 'System', null, 'System', null, 'System'), false, true);
 
 
 
diff --git a/database/sql/reservation/reservation-legal.sql b/database/sql/reservation/reservation-legal.sql
index d445cf2..61d9b0e 100644
--- a/database/sql/reservation/reservation-legal.sql
+++ b/database/sql/reservation/reservation-legal.sql
@@ -30,7 +30,8 @@ create table s_tables.t_legal_types (
   constraint cu_legal_types_id unique (id),
   constraint cu_legal_types_name_machine unique (name_machine),
 
-  constraint cc_legal_types_id check (id > -1)
+  constraint cc_legal_types_id check (id > -1),
+  constraint cc_legal_name_machine check (name_machine ~ '\w+')
 );
 
 create sequence s_tables.se_legal_types_id owned by s_tables.t_legal_types.id;
diff --git a/database/sql/reservation/reservation-log_problems.sql b/database/sql/reservation/reservation-log_problems.sql
index 545812e..ff5b427 100644
--- a/database/sql/reservation/reservation-log_problems.sql
+++ b/database/sql/reservation/reservation-log_problems.sql
@@ -98,7 +98,7 @@ create function s_tables.f_log_problems_users_delete() returns trigger security
   end;
 $$ language plpgsql;
 
-alter function s_tables.f_log_problems_users_delete () owner to r_reservation_logger;
+alter function s_tables.f_log_problems_users_delete () owner to u_reservation_logger;
 
 create trigger tr_log_problems_users_delete
   after delete on s_tables.t_log_problems_users
diff --git a/database/sql/reservation/reservation-log_types.sql b/database/sql/reservation/reservation-log_types.sql
index 737fb34..f306dcd 100644
--- a/database/sql/reservation/reservation-log_types.sql
+++ b/database/sql/reservation/reservation-log_types.sql
@@ -49,7 +49,7 @@ create view public.v_log_types with (security_barrier=true) as
   select id, name_machine, name_human from s_tables.t_log_types
   where not is_deleted and not is_locked;
 
-grant select on public.v_log_types to r_reservation, r_public, r_reservation_system;
+grant select on public.v_log_types to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_log_types_date_changed_deleted_or_locked
@@ -90,7 +90,7 @@ grant select,usage on s_tables.se_log_type_http_status_codes_id to r_reservation
 create view public.v_log_type_http_status_codes with (security_barrier=true) as
   select id, name_machine, name_human from s_tables.t_log_type_http_status_codes;
 
-grant select on public.v_log_type_http_status_codes to r_reservation, r_public, r_reservation_system;
+grant select on public.v_log_type_http_status_codes to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_log_type_http_status_codes_date_changed_deleted_or_locked
@@ -131,7 +131,7 @@ create view s_users.v_log_type_severity_levels with (security_barrier=true) as
   select id, name_machine, name_human from s_tables.t_log_type_severity_levels
   where not is_deleted;
 
-grant select on s_users.v_log_type_severity_levels to r_reservation, r_public, r_reservation_system;
+grant select on s_users.v_log_type_severity_levels to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_log_type_severity_levels_date_changed_deleted_or_locked
diff --git a/database/sql/reservation/reservation-log_users.sql b/database/sql/reservation/reservation-log_users.sql
index f2061da..c03371a 100644
--- a/database/sql/reservation/reservation-log_users.sql
+++ b/database/sql/reservation/reservation-log_users.sql
@@ -43,7 +43,7 @@ alter table s_tables.t_log_users alter column id set default nextval('s_tables.s
 
 grant select on s_tables.t_log_users to r_reservation_manager, r_reservation_auditor;
 grant select,usage on s_tables.se_log_users_id to r_reservation_administer;
-grant usage on s_tables.se_log_users_id to r_reservation, r_public, r_reservation_system;
+grant usage on s_tables.se_log_users_id to r_reservation, r_reservation_public, r_reservation_system;
 
 create index i_log_users_type_php on s_tables.t_log_users (id)
   where log_type = 1;
@@ -134,10 +134,10 @@ grant insert on s_users.v_log_users_self_insert to r_reservation, r_reservation_
 /** public users should be able to insert, but should never be able to view the logs that they insert. **/
 create view public.v_log_users_self_insert with (security_barrier=true) as
   select log_title, log_type, log_severity, log_details, request_client, response_code from s_tables.t_log_users
-    where 'r_public' in (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_public')
+    where 'r_reservation_public' in (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_reservation_public')
     with check option;
 
-grant insert on public.v_log_users_self_insert to r_public;
+grant insert on public.v_log_users_self_insert to r_reservation_public;
 
 
 create trigger tr_log_users_enforce_user_and_session_ids
@@ -175,7 +175,7 @@ alter table s_tables.t_log_user_activity alter column id set default nextval('s_
 
 grant select on s_tables.t_log_user_activity to r_reservation_manager, r_reservation_auditor;
 grant select,usage on s_tables.se_log_user_activity_id to r_reservation_administer;
-grant usage on s_tables.se_log_user_activity_id to r_reservation, r_public, r_reservation_system;
+grant usage on s_tables.se_log_user_activity_id to r_reservation, r_reservation_public, r_reservation_system;
 
 create index i_log_user_activity_response_code_4xx on s_tables.t_log_user_activity (id)
   where response_code >= 400 and response_code < 500;
@@ -225,7 +225,7 @@ create view public.v_log_user_activity_self_insert with (security_barrier=true)
     where id_user in (select id from public.v_users_locked_not_self)
     with check option;
 
-grant insert on public.v_log_user_activity_self_insert to r_public;
+grant insert on public.v_log_user_activity_self_insert to r_reservation_public;
 
 
 create trigger tr_log_user_activity_enforce_user_and_session_ids
diff --git a/database/sql/reservation/reservation-main.sql b/database/sql/reservation/reservation-main.sql
index e42827e..d1fded8 100644
--- a/database/sql/reservation/reservation-main.sql
+++ b/database/sql/reservation/reservation-main.sql
@@ -41,7 +41,7 @@ grant usage on schema s_drafters to r_reservation_drafter;
 grant usage on schema s_requesters to r_reservation_requester;
 grant usage on schema s_users to r_reservation;
 
-grant usage on schema s_tables to r_reservation_revision_requests, r_reservation_statistics_update, r_reservation_logger, r_reservation_groups_handler;
+grant usage on schema s_tables to u_reservation_revision_requests, u_reservation_statistics_update, u_reservation_logger, u_reservation_groups_handler;
 
 
 /** Composite Types **/
diff --git a/database/sql/reservation/reservation-paths.sql b/database/sql/reservation/reservation-paths.sql
index 8537cfc..a827a72 100644
--- a/database/sql/reservation/reservation-paths.sql
+++ b/database/sql/reservation/reservation-paths.sql
@@ -30,7 +30,8 @@ create table s_tables.t_path_types (
 
   constraint cu_path_types_name_machine unique (name_machine),
 
-  constraint cc_path_types_id check (id > -1)
+  constraint cc_path_types_id check (id > -1),
+  constraint cc_path_types_name_machine check (name_machine ~ '\w+')
 );
 
 create sequence s_tables.se_path_types_id owned by s_tables.t_path_types.id;
@@ -58,7 +59,7 @@ create view public.v_path_types with (security_barrier=true) as
   select id, name_machine, name_human, FALSE as is_locked, NULL::timestamp as date_created, NULL::timestamp as date_changed from s_tables.t_path_types
   where not is_deleted and not is_locked;
 
-grant select on public.v_path_types to r_reservation, r_public, r_reservation_system;
+grant select on public.v_path_types to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_path_types_date_changed_deleted_or_locked
@@ -97,6 +98,7 @@ create table s_tables.t_paths (
   constraint cu_paths_field_path unique (field_path),
 
   constraint cc_paths_id check (id > 0),
+  constraint cc_paths_name_machine check (name_machine ~ '\w+'),
 
   constraint cf_paths_id_creator foreign key (id_creator) references s_tables.t_users (id) on delete cascade on update cascade,
   constraint cf_paths_id_creator_session foreign key (id_creator_session) references s_tables.t_users (id) on delete cascade on update cascade,
@@ -136,7 +138,7 @@ create view public.v_paths with (security_barrier=true) as
   select id, id_type, NULL::bigint as id_group, name_machine, name_human, NULL::bool as is_private, NULL::bool as date_created, NULL::bool as date_changed from s_tables.t_paths
   where not is_deleted and not is_locked and not is_private;
 
-grant select on public.v_path_types to r_reservation, r_public, r_reservation_system;
+grant select on public.v_path_types to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_paths_date_changed_deleted_or_locked
diff --git a/database/sql/reservation/reservation-requests.sql b/database/sql/reservation/reservation-requests.sql
index 00b8cb7..b9e3e5a 100644
--- a/database/sql/reservation/reservation-requests.sql
+++ b/database/sql/reservation/reservation-requests.sql
@@ -30,7 +30,8 @@ create table s_tables.t_request_types (
   constraint cu_request_types_id unique (id),
   constraint cu_request_types_name_machine unique (name_machine),
 
-  constraint cc_request_types_id check (id >= 0)
+  constraint cc_request_types_id check (id >= 0),
+  constraint cc_request_types_name_machine check (name_machine ~ '\w+')
 );
 
 create sequence s_tables.se_request_types_id owned by s_tables.t_request_types.id;
@@ -148,6 +149,7 @@ create table s_tables.t_requests (
   constraint cc_requests_id check (id > 0),
   constraint cc_requests_id_revision check (id_revision > -1),
   constraint cc_requests_approved check ((is_approved and not is_denied) or (not is_approved and is_denied)),
+  constraint cc_requests_name_machine check (name_machine ~ '\w+'),
 
   constraint cf_requests_id_creator foreign key (id_creator) references s_tables.t_users (id) on delete restrict on update cascade,
   constraint cf_requests_id_creator_session foreign key (id_creator_session) references s_tables.t_users (id) on delete restrict on update cascade,
@@ -391,7 +393,7 @@ create table s_tables.t_request_revisions_original (
   constraint cf_request_revisions_original_association foreign key (id_association) references s_tables.t_associations (id) on delete restrict on update cascade
 );
 
-grant select,insert on s_tables.t_request_revisions_original to r_reservation_administer, r_reservation_revision_requests;
+grant select,insert on s_tables.t_request_revisions_original to r_reservation_administer, u_reservation_revision_requests;
 grant select on s_tables.t_request_revisions_original to r_reservation_manager, r_reservation_auditor;
 
 
@@ -419,7 +421,7 @@ create function s_tables.f_request_revisions_original_record_revision() returns
   end;
 $$ language plpgsql;
 
-alter function s_tables.f_request_revisions_original_record_revision () owner to r_reservation_revision_requests;
+alter function s_tables.f_request_revisions_original_record_revision () owner to u_reservation_revision_requests;
 
 create trigger tr_requests_save_original_revision
   after insert on s_tables.t_requests
@@ -508,7 +510,7 @@ create table s_tables.t_request_revisions (
   constraint cf_request_revisions_association foreign key (id_association) references s_tables.t_associations (id) on delete restrict on update cascade
 );
 
-grant select,insert on s_tables.t_request_revisions to r_reservation_administer, r_reservation_revision_requests;
+grant select,insert on s_tables.t_request_revisions to r_reservation_administer, u_reservation_revision_requests;
 grant select on s_tables.t_request_revisions to r_reservation_manager, r_reservation_auditor;
 
 
@@ -851,7 +853,7 @@ create function s_tables.f_request_revisions_record_revision() returns trigger s
   end;
 $$ language plpgsql;
 
-alter function s_tables.f_request_revisions_record_revision () owner to r_reservation_revision_requests;
+alter function s_tables.f_request_revisions_record_revision () owner to u_reservation_revision_requests;
 
 create trigger tr_requests_save_revision
   after insert on s_tables.t_request_revisions
diff --git a/database/sql/reservation/reservation-statistics.sql b/database/sql/reservation/reservation-statistics.sql
index d5d9306..195f435 100644
--- a/database/sql/reservation/reservation-statistics.sql
+++ b/database/sql/reservation/reservation-statistics.sql
@@ -29,7 +29,7 @@ create table s_tables.t_statistics_http_status_codes (
   constraint cf_statistics_http_status_codes_code foreign key (code) references s_tables.t_log_type_http_status_codes (id) on delete restrict on update cascade
 );
 
-grant select,insert,update on s_tables.t_statistics_http_status_codes to r_reservation_manager, r_reservation_statistics_update;
+grant select,insert,update on s_tables.t_statistics_http_status_codes to r_reservation_manager, u_reservation_statistics_update;
 grant select on s_tables.t_statistics_http_status_codes to r_reservation_auditor;
 
 
@@ -50,7 +50,7 @@ create function s_tables.f_statistics_http_status_codes_insert() returns trigger
   end;
 $$ language plpgsql;
 
-alter function s_tables.f_statistics_http_status_codes_insert () owner to r_reservation_statistics_update;
+alter function s_tables.f_statistics_http_status_codes_insert () owner to u_reservation_statistics_update;
 
 create trigger tr_log_user_activity_insert_statistics_http_status_codes
   after insert on s_tables.t_log_user_activity
@@ -98,7 +98,7 @@ create view public.v_statistics_request_path with (security_barrier=true) as
   select path, count from s_tables.t_statistics_request_path
     with check option;
 
-grant select,insert,update on public.v_statistics_request_path to r_public;
+grant select,insert,update on public.v_statistics_request_path to r_reservation_public;
 
 
 /** create an auto-update trigger **/
diff --git a/database/sql/reservation/reservation-types.sql b/database/sql/reservation/reservation-types.sql
index 1ae0d8c..d3b05ee 100644
--- a/database/sql/reservation/reservation-types.sql
+++ b/database/sql/reservation/reservation-types.sql
@@ -32,7 +32,8 @@ create table s_tables.t_types_mime_categorys (
 
   constraint cu_types_mime_categorys_name_machine unique (name_machine),
 
-  constraint cc_types_mime_categorys_id check (id > -1)
+  constraint cc_types_mime_categorys_id check (id > -1),
+  constraint cc_types_mime_categorys_name_machine check (name_machine ~ '\w+')
 );
 
 grant select,insert,update on s_tables.t_types_mime_categorys to r_reservation_administer;
@@ -41,7 +42,7 @@ create view public.v_types_mime_categorys with (security_barrier=true) as
   select id, name_machine, name_human, is_locked from s_tables.t_types_mime_categorys
   where not is_deleted;
 
-grant select on public.v_types_mime_categorys to r_reservation, r_public, r_reservation_system;
+grant select on public.v_types_mime_categorys to r_reservation, r_reservation_public, r_reservation_system;
 
 grant select,insert,update on s_tables.t_types_mime_categorys to r_reservation_administer;
 
@@ -50,7 +51,7 @@ create view public.v_types_mime_categorys_locked_not with (security_barrier=true
   select id, name_machine, name_human, field_category from s_tables.t_types_mime_categorys
   where not is_deleted and not is_locked;
 
-grant select on public.v_types_mime_categorys_locked_not to r_reservation, r_public, r_reservation_system;
+grant select on public.v_types_mime_categorys_locked_not to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_types_mime_categorys_date_changed_deleted_or_locked
@@ -91,13 +92,13 @@ create view public.v_types_mime_types with (security_barrier=true) as
   select id, id_category, name_machine, name_human, field_extension, field_mime, is_locked from s_tables.t_types_mime_types
   where not is_deleted;
 
-grant select on public.v_types_mime_types to r_reservation, r_public, r_reservation_system;
+grant select on public.v_types_mime_types to r_reservation, r_reservation_public, r_reservation_system;
 
 create view public.v_types_mime_types_locked_not with (security_barrier=true) as
   select id, id_category, name_machine, name_human, field_extension, field_mime, is_locked from s_tables.t_types_mime_types
   where not is_deleted and not is_locked;
 
-grant select on public.v_types_mime_types to r_reservation, r_public, r_reservation_system;
+grant select on public.v_types_mime_types to r_reservation, r_reservation_public, r_reservation_system;
 
 
 create trigger tr_types_mime_types_date_changed_deleted_or_locked
diff --git a/database/sql/reservation/reservation-users.sql b/database/sql/reservation/reservation-users.sql
index 32ece05..ca68edf 100644
--- a/database/sql/reservation/reservation-users.sql
+++ b/database/sql/reservation/reservation-users.sql
@@ -12,6 +12,7 @@ set datestyle to us;
 
 
 /** Users **/
+/* Note: is_public and is_private have two different contexts, is_public refers to the r_reservation_public role and is_private refers to making certain user fields public/private within the system to a certain degree. */
 create table s_tables.t_users (
   id bigint not null,
   id_external bigint,
@@ -123,11 +124,11 @@ create view s_users.v_users_self_session with (security_barrier=true) as
 grant select on s_users.v_users_self_session to r_reservation, r_reservation_system;
 
 create view public.v_users_self_session with (security_barrier=true) as
-  with postgres_roles as (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_public')
+  with postgres_roles as (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_reservation_public')
   select id, id_external, id_sort, name_machine, name_human, address_email, is_administer, is_manager, is_auditor, is_publisher, is_insurer, is_financer, is_reviewer, is_editor, is_drafter, is_requester, is_system, is_public, is_locked, is_private, can_manage_roles, date_created, date_changed, date_synced, date_locked, settings from s_tables.t_users
-    where not is_deleted and (name_machine)::text = (session_user)::text and 'r_public' in (select * from postgres_roles);
+    where not is_deleted and (name_machine)::text = (session_user)::text and 'r_reservation_public' in (select * from postgres_roles);
 
-grant select on public.v_users_self_session to r_public;
+grant select on public.v_users_self_session to r_reservation_public;
 
 create view s_users.v_users_locked_not_self with (security_barrier=true) as
   select id, id_external, id_sort, name_machine, name_human, address_email, is_administer, is_manager, is_auditor, is_publisher, is_insurer, is_financer, is_reviewer, is_editor, is_drafter, is_requester, is_system, is_public, is_locked, is_private, can_manage_roles, date_created, date_changed, date_synced, date_locked, settings from s_tables.t_users
@@ -136,11 +137,11 @@ create view s_users.v_users_locked_not_self with (security_barrier=true) as
 grant select on s_users.v_users_locked_not_self to r_reservation, r_reservation_system;
 
 create view public.v_users_locked_not_self with (security_barrier=true) as
-  with postgres_roles as (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_public')
+  with postgres_roles as (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_reservation_public')
   select id, id_external, id_sort, name_machine, name_human, address_email, is_administer, is_manager, is_auditor, is_publisher, is_insurer, is_financer, is_reviewer, is_editor, is_drafter, is_requester, is_system, is_public, is_locked, is_private, can_manage_roles, date_created, date_changed, date_synced, date_locked, settings from s_tables.t_users
-    where not is_deleted and not is_locked and (name_machine)::text = (current_user)::text and 'r_public' in (select * from postgres_roles);
+    where not is_deleted and not is_locked and (name_machine)::text = (current_user)::text and 'r_reservation_public' in (select * from postgres_roles);
 
-grant select on public.v_users_locked_not_self to r_public;
+grant select on public.v_users_locked_not_self to r_reservation_public;
 
 create view s_users.v_users_can_manage_roles with (security_barrier=true) as
   with this_user_can_manage_roles as (select id from s_users.v_users_locked_not_self where can_manage_roles)
@@ -169,7 +170,7 @@ create view public.v_users_self with (security_barrier=true) as
   select id, id_external, id_sort, name_machine, name_human, address_email, is_administer, is_manager, is_auditor, is_publisher, is_insurer, is_financer, is_reviewer, is_editor, is_drafter, is_requester, is_system, is_public, is_locked, is_private, date_created, date_changed, date_synced, date_locked, settings from s_tables.t_users
     where not is_deleted and id = 1;
 
-grant select on public.v_users_self to r_public, r_reservation, r_reservation_system;
+grant select on public.v_users_self to r_reservation_public, r_reservation, r_reservation_system;
 
 
 /*** provide public user information ***/
@@ -177,7 +178,7 @@ create view public.v_users with (security_barrier=true) as
   select id, null::bigint as id_external, id_sort, name_machine, name_human, null::public.ct_email as address_email, null::bool as is_administer, null::bool as is_manager, null::bool as is_auditor, null::bool as is_publisher, null::bool as is_insurer, null::bool as is_financer, null::bool as is_reviewer, null::bool as is_editor, null::bool as is_drafter, null::bool as is_requester, is_system, is_public, null::bool as is_locked, null::bool as is_private, null::bool as can_manage_roles, null::timestamp as date_created, null::timestamp as date_changed, null::timestamp as date_synced, null::timestamp as date_locked, null::json as settings from s_tables.t_users
     where (not is_deleted and not is_private) or (not is_deleted and (name_machine)::text = (current_user)::text);
 
-grant select on public.v_users to r_reservation, r_public, r_reservation_system;
+grant select on public.v_users to r_reservation, r_reservation_public, r_reservation_system;
 
 
 /*** provide e-mail address as public information only if it is explicitly allowed ***/
@@ -185,7 +186,7 @@ create view public.v_users_email with (security_barrier=true) as
   select id, null::bigint as id_external, id_sort, name_machine, name_human, address_email, null::bool as is_administer, null::bool as is_manager, null::bool as is_auditor, null::bool as is_publisher, null::bool as is_insurer, null::bool as is_financer, null::bool as is_reviewer, null::bool as is_editor, null::bool as is_drafter, null::bool as is_requester, is_system, is_public, null::bool as is_locked, null::bool as is_private, null::bool as can_manage_roles, null::timestamp as date_created, null::timestamp as date_changed, null::timestamp as date_synced, null::timestamp as date_locked, null::json as settings from s_tables.t_users
     where (not is_deleted and not is_private and not (address_email).private) or (not is_deleted and (name_machine)::text = (current_user)::text);
 
-grant select on public.v_users_email to r_reservation, r_public, r_reservation_system;
+grant select on public.v_users_email to r_reservation, r_reservation_public, r_reservation_system;
 
 
 /*** provide managers with the ability to modify accounts ***/
@@ -304,8 +305,347 @@ create function s_administers.f_users_update_actions() returns trigger as $$
 $$ language plpgsql;
 
 
+/* attempt to auto-manage postgresql reservation roles with the reservation database user roles. */
+/* user ids 1 and 2 are explicitly reserved for anonymous/public and the database postgresql accounts. */
+/* postgresql does not seem to support variables for the user with grant and revoke, therefore the execute statement is used to perform the query. */
+/* @fixme: the name_machine must be forcibly sanitized to be alphanumeric, -, or _ in all cases. */
+create function s_administers.f_users_insert_as_administer() returns trigger security definer as $$
+  declare
+    name_machine constant text default quote_ident(new.name_machine);
+  begin
+    if (new.id = 1 or new.id = 2) then
+      return null;
+    end if;
+
+    set client_min_messages to error;
+
+    if (new.is_locked or new.is_deleted) then
+      if (new.is_deleted) then
+        execute 'revoke r_reservation from ' || name_machine;
+        execute 'revoke r_reservation_system from ' || name_machine;
+        execute 'revoke r_reservation_public from ' || name_machine;
+      elseif (new.is_public) then
+        execute 'grant r_reservation_public to ' || name_machine;
+      elseif (new.is_system) then
+        execute 'grant r_reservation_system to ' || name_machine;
+      elseif (new.is_requester or new.is_drafter or new.is_editor or new.is_reviewer or new.is_financer or new.is_insurer or new.is_publisher or new.is_auditor or new.is_manager or new.is_administer) then
+        execute 'grant r_reservation to ' || name_machine;
+      end if;
+
+      execute 'revoke r_reservation_administer from ' || name_machine;
+      execute 'revoke r_reservation_manager from ' || name_machine;
+      execute 'revoke r_reservation_auditor from ' || name_machine;
+      execute 'revoke r_reservation_publisher from ' || name_machine;
+      execute 'revoke r_reservation_financer from ' || name_machine;
+      execute 'revoke r_reservation_insurer from ' || name_machine;
+      execute 'revoke r_reservation_reviewer from ' || name_machine;
+      execute 'revoke r_reservation_editor from ' || name_machine;
+      execute 'revoke r_reservation_drafter from ' || name_machine;
+      execute 'revoke r_reservation_requester from ' || name_machine;
+    elseif (new.is_public) then
+      execute 'grant r_reservation_public to ' || name_machine;
+      execute 'revoke r_reservation from ' || name_machine;
+
+      if (new.is_system) then
+        execute 'grant r_reservation_system to ' || name_machine;
+      else
+        execute 'revoke r_reservation_system from ' || name_machine;
+      end if;
+
+      execute 'revoke r_reservation_administer from ' || name_machine;
+      execute 'revoke r_reservation_manager from ' || name_machine;
+      execute 'revoke r_reservation_auditor from ' || name_machine;
+      execute 'revoke r_reservation_publisher from ' || name_machine;
+      execute 'revoke r_reservation_financer from ' || name_machine;
+      execute 'revoke r_reservation_insurer from ' || name_machine;
+      execute 'revoke r_reservation_reviewer from ' || name_machine;
+      execute 'revoke r_reservation_editor from ' || name_machine;
+      execute 'revoke r_reservation_drafter from ' || name_machine;
+      execute 'revoke r_reservation_requester from ' || name_machine;
+    else
+      if (new.is_system) then
+        execute 'grant r_reservation_system to ' || name_machine;
+
+        execute 'revoke r_reservation from ' || name_machine;
+        execute 'revoke r_reservation_public from ' || name_machine;
+      elseif (new.is_requester or new.is_drafter or new.is_editor or new.is_reviewer or new.is_financer or new.is_insurer or new.is_publisher or new.is_auditor or new.is_manager or new.is_administer) then
+        execute 'grant r_reservation to ' || name_machine;
+
+        execute 'revoke r_reservation_system from ' || name_machine;
+        execute 'revoke r_reservation_public from ' || name_machine;
+      end if;
+
+      if (new.is_administer) then
+        execute 'grant r_reservation_administer to ' || name_machine;
+      end if;
+
+      if (new.is_manager) then
+        execute 'grant r_reservation_manager to ' || name_machine;
+      end if;
+
+      if (new.is_auditor) then
+        execute 'grant r_reservation_auditor to ' || name_machine;
+      end if;
+
+      if (new.is_publisher) then
+        execute 'grant r_reservation_publisher to ' || name_machine;
+      end if;
+
+      if (new.is_insurer) then
+        execute 'grant r_reservation_insurer to ' || name_machine;
+      end if;
+
+      if (new.is_financer) then
+        execute 'grant r_reservation_financer to ' || name_machine;
+      end if;
+
+      if (new.is_reviewer) then
+        execute 'grant r_reservation_reviewer to ' || name_machine;
+      end if;
+
+      if (new.is_editor) then
+        execute 'grant r_reservation_editor to ' || name_machine;
+      end if;
+
+      if (new.is_drafter) then
+        execute 'grant r_reservation_drafter to ' || name_machine;
+      end if;
+
+      if (new.is_requester) then
+        execute 'grant r_reservation_requester to ' || name_machine;
+      end if;
+    end if;
+
+    reset client_min_messages;
+
+    return null;
+  end;
+$$ language plpgsql;
+
+alter function s_administers.f_users_insert_as_administer() owner to u_reservation_grant_roles;
+
+
+create function s_administers.f_users_update_as_administer() returns trigger security definer as $$
+  declare
+    name_machine constant text default quote_ident(new.name_machine);
+  begin
+    if (new.id = 1 or new.id = 2) then
+      return null;
+    end if;
+
+    set client_min_messages to error;
+
+    if (old.is_locked <> new.is_locked or old.is_deleted <> new.is_deleted) then
+      if (old.is_deleted <> new.is_deleted) then
+        if (new.is_deleted) then
+          execute 'revoke r_reservation from ' || name_machine;
+          execute 'revoke r_reservation_system from ' || name_machine;
+          execute 'revoke r_reservation_public from ' || name_machine;
+        else
+          if (new.is_public) then
+            execute 'grant r_reservation_public to ' || name_machine;
+          elseif (new.is_system) then
+            execute 'grant r_reservation_system to ' || name_machine;
+          elseif (new.is_requester or new.is_drafter or new.is_editor or new.is_reviewer or new.is_financer or new.is_insurer or new.is_publisher or new.is_auditor or new.is_manager or new.is_administer) then
+            execute 'grant r_reservation to ' || name_machine;
+          end if;
+        end if;
+      end if;
+
+      if (new.is_locked or new.is_deleted) then
+        execute 'revoke r_reservation_administer from ' || name_machine;
+        execute 'revoke r_reservation_manager from ' || name_machine;
+        execute 'revoke r_reservation_auditor from ' || name_machine;
+        execute 'revoke r_reservation_publisher from ' || name_machine;
+        execute 'revoke r_reservation_financer from ' || name_machine;
+        execute 'revoke r_reservation_insurer from ' || name_machine;
+        execute 'revoke r_reservation_reviewer from ' || name_machine;
+        execute 'revoke r_reservation_editor from ' || name_machine;
+        execute 'revoke r_reservation_drafter from ' || name_machine;
+        execute 'revoke r_reservation_requester from ' || name_machine;
+        execute 'revoke r_reservation_system from ' || name_machine;
+        execute 'revoke r_reservation_public from ' || name_machine;
+      elseif (new.is_public) then
+        execute 'grant r_reservation_public to ' || name_machine;
+
+        if (new.is_system) then
+          execute 'grant r_reservation_system to ' || name_machine;
+        end if;
+      else
+        if (new.is_administer) then
+          execute 'grant r_reservation_administer to ' || name_machine;
+        end if;
+
+        if (new.is_manager) then
+          execute 'grant r_reservation_manager to ' || name_machine;
+        end if;
+
+        if (new.is_auditor) then
+          execute 'grant r_reservation_auditor to ' || name_machine;
+        end if;
+
+        if (new.is_publisher) then
+          execute 'grant r_reservation_publisher to ' || name_machine;
+        end if;
+
+        if (new.is_financer) then
+          execute 'grant r_reservation_financer to ' || name_machine;
+        end if;
+
+        if (new.is_insurer) then
+          execute 'grant r_reservation_insurer to ' || name_machine;
+        end if;
+
+        if (new.is_reviewer) then
+          execute 'grant r_reservation_reviewer to ' || name_machine;
+        end if;
+
+        if (new.is_editor) then
+          execute 'grant r_reservation_editor to ' || name_machine;
+        end if;
+
+        if (new.is_drafter) then
+          execute 'grant r_reservation_drafter to ' || name_machine;
+        end if;
+
+        if (new.is_requester) then
+          execute 'grant r_reservation_requester to ' || name_machine;
+        end if;
+
+        if (new.is_system) then
+          execute 'grant r_reservation_system to ' || name_machine;
+        end if;
+      end if;
+    elseif (old.is_public <> new.is_public and new.is_public) then
+      execute 'grant r_reservation_public to ' || name_machine;
+
+      execute 'revoke r_reservation_administer from ' || name_machine;
+      execute 'revoke r_reservation_manager from ' || name_machine;
+      execute 'revoke r_reservation_auditor from ' || name_machine;
+      execute 'revoke r_reservation_publisher from ' || name_machine;
+      execute 'revoke r_reservation_financer from ' || name_machine;
+      execute 'revoke r_reservation_insurer from ' || name_machine;
+      execute 'revoke r_reservation_reviewer from ' || name_machine;
+      execute 'revoke r_reservation_editor from ' || name_machine;
+      execute 'revoke r_reservation_drafter from ' || name_machine;
+      execute 'revoke r_reservation_requester from ' || name_machine;
+
+      if (old.is_system <> new.is_system) then
+        if (new.is_system) then
+          execute 'grant r_reservation_system to ' || name_machine;
+        else
+          execute 'revoke r_reservation_system from ' || name_machine;
+        end if;
+      end if;
+    else
+      if (old.is_public <> new.is_public) then
+        execute 'revoke r_reservation_public from ' || name_machine;
+      end if;
+
+      if (old.is_system <> new.is_system) then
+        if (new.is_system) then
+          execute 'grant r_reservation_system to ' || name_machine;
+        else
+          execute 'revoke r_reservation_system from ' || name_machine;
+        end if;
+      elseif (not new.is_system) then
+        if (new.is_requester or new.is_drafter or new.is_editor or new.is_reviewer or new.is_financer or new.is_insurer or new.is_publisher or new.is_auditor or new.is_manager or new.is_administer) then
+          execute 'grant r_reservation to ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_administer <> new.is_administer) then
+        if (new.is_administer) then
+          execute 'grant r_reservation_administer to ' || name_machine;
+        else
+          execute 'revoke r_reservation_administer from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_manager <> new.is_manager) then
+        if (new.is_manager) then
+          execute 'grant r_reservation_manager to ' || name_machine;
+        else
+          execute 'revoke r_reservation_manager from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_auditor <> new.is_auditor) then
+        if (new.is_auditor) then
+          execute 'grant r_reservation_auditor to ' || name_machine;
+        else
+          execute 'revoke r_reservation_auditor from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_publisher <> new.is_publisher) then
+        if (new.is_publisher) then
+          execute 'grant r_reservation_publisher to ' || name_machine;
+        else
+          execute 'revoke r_reservation_publisher from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_insurer <> new.is_insurer) then
+        if (new.is_insurer) then
+          execute 'grant r_reservation_insurer to ' || name_machine;
+        else
+          execute 'revoke r_reservation_insurer from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_financer <> new.is_financer) then
+        if (new.is_financer) then
+          execute 'grant r_reservation_financer to ' || name_machine;
+        else
+          execute 'revoke r_reservation_financer from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_reviewer <> new.is_reviewer) then
+        if (new.is_reviewer) then
+          execute 'grant r_reservation_reviewer to ' || name_machine;
+        else
+          execute 'revoke r_reservation_reviewer from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_editor <> new.is_editor) then
+        if (new.is_editor) then
+          execute 'grant r_reservation_editor to ' || name_machine;
+        else
+          execute 'revoke r_reservation_editor from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_drafter <> new.is_drafter) then
+        if (new.is_drafter) then
+          execute 'grant r_reservation_drafter to ' || name_machine;
+        else
+          execute 'revoke r_reservation_drafter from ' || name_machine;
+        end if;
+      end if;
+
+      if (old.is_requester <> new.is_requester) then
+        if (new.is_requester) then
+          execute 'grant r_reservation_requester to ' || name_machine;
+        else
+          execute 'revoke r_reservation_requester from ' || name_machine;
+        end if;
+      end if;
+    end if;
+
+    reset client_min_messages;
+
+    return null;
+  end;
+$$ language plpgsql;
+
+alter function s_administers.f_users_update_as_administer() owner to u_reservation_grant_roles;
+
 create function s_administers.f_users_update_materialized_views() returns trigger security definer as $$
   begin
+
     refresh materialized view s_administers.m_users_date_created_this_day;
     refresh materialized view s_administers.m_users_date_changed_this_day;
     refresh materialized view s_administers.m_users_date_synced_this_day;
@@ -314,7 +654,7 @@ create function s_administers.f_users_update_materialized_views() returns trigge
   end;
 $$ language plpgsql;
 
-alter function s_administers.f_users_update_materialized_views() owner to r_reservation_administer;
+alter function s_administers.f_users_update_as_administer() owner to r_reservation_administer;
 
 create trigger tr_users_insert_actions
   before insert on s_tables.t_users
@@ -324,13 +664,21 @@ create trigger tr_users_update_actions
   before update on s_tables.t_users
     for each row execute procedure s_administers.f_users_update_actions();
 
+create trigger tr_users_insert_as_administer
+  after insert on s_tables.t_users
+    for each row execute procedure s_administers.f_users_insert_as_administer();
+
+create trigger tr_users_update_as_administer
+  after update on s_tables.t_users
+    for each row execute procedure s_administers.f_users_update_as_administer();
+
 create trigger tr_users_update_materialized_views
   after insert or update on s_tables.t_users
     for each statement execute procedure s_administers.f_users_update_materialized_views();
 
 /** Special Cases: manually add the postgresql and public users first before any logging triggers are defined (because some of them depend on this table recursively! **/
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_public) values (1, 'u_public', (null, 'Unknown', null, null, null, 'Unknown'), false, true);
-insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (2, 'postgres', (null, 'Database', null, 'Administer', null, 'Database (Administer)'), false, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_public) values (1, 'u_reservation_public', (null, 'Unknown', null, null, null, 'Unknown'), false, true);
+insert into s_tables.t_users (id, name_machine, name_human, is_private, is_system) values (2, 'postgres', (null, 'Database', null, 'Administer', null, 'Database (Administer)'), true, true);
 
 
 
diff --git a/documentation/access_roles.txt b/documentation/access_roles.txt
index 46fe6d7..8a8e6a9 100644
--- a/documentation/access_roles.txt
+++ b/documentation/access_roles.txt
@@ -54,7 +54,7 @@ The roles:
   - Do not confuse this with "r_(system)_user" (described below), which is considered a special-case user account.
   - All users should be part of this role.
 
-- r_public:
+- r_(system)_public:
   - Provides access to the database but only for information that should be made available to the world.
   - This role has the least amount of access.
   - Should not be assigned to any user with the (system)_users role or any such subset.
@@ -79,27 +79,27 @@ There are five groups:
   - These are for accounts that need login access and are granted access that every user on the system should have access to.
 
 - Public Group:
-  - r_public
+  - r_(system)_public
   - These are for anonymous accounts and other public access entities.
   - Everything this should be able to access is generally considered public information.
 
 
 There are also special user accounts:
-- u_public:
+- u_(system)_public:
   - This account should not require a password to login and essentially represent a public web user.
-  - Some systems are designed to be private and require login, these systems generally provided almost no access for u_public.
+  - Some systems are designed to be private and require login, these systems generally provided almost no access for u_(system)_public.
   - Because there is no password and all data is considered public, ssl is not needed and access to/from the database should have higher performance than other user accounts.
 
-- r_(system):
-  - Replace (system) with the appropriate name for the system, generally the database name (for example, the reservation system, this would be r_reservation).
+- u_(system):
+  - Replace (system) with the appropriate name for the system, generally the database name (for example, the reservation system, this would be u_reservation).
   - This is the account the system uses for general cron jobs and other automated tasks.
   - The access provided here is dependent to the particular system and therefore this may also have or not have a password.
 
-- r_(system)_(????):
-  - Replace (system) with the appropriate name for the system, generally the database name (for example, the reservation system, this would be r_reservation).
+- u_(system)_(????):
+  - Replace (system) with the appropriate name for the system, generally the database name (for example, the reservation system, this would be u_reservation_????).
   - Replace (????) with a custom name specific to the limitation or purpose of the role.
   - Custom database-specific accounts are created to act as a restricted/jailed account to perform certain tasks.
   - These roles are used to prevent users from having direct access to some tables without having to switch to a root user.
   - Postgresql allows views to run as 'security definer', which then grants all such priveleges.
     - By using a restricted/jailed role, any potential damage from accidents or security breaches are reduced.
-  - Example: 'r_reservation_statistics_update' is a role that performs updates on statistics tables in the reservation system database.
+  - Example: 'u_reservation_statistics_update' is a role that performs updates on statistics tables in the reservation system database.
diff --git a/examples/test.php b/examples/test.php
index 669566e..b14620e 100755
--- a/examples/test.php
+++ b/examples/test.php
@@ -297,7 +297,6 @@
               $connected = connect_database($database);
               if ($connected) {
                 set_log_user($database, 'logout');
-                set_log_activity($database, 200);
                 $database->do_disconnect();
               }
               unset($connected);
@@ -393,7 +392,8 @@
 
               ldap($stuff, $session->get_name()->get_value_exact());
               set_log_activity($database);
-              if (!empty($session->get_name()->get_value_exact()) && $session->get_name()->get_value_exact() != 'u_public') {
+
+              if (!empty($session->get_name()->get_value_exact()) && $session->get_name()->get_value_exact() != 'u_reservation_public') {
                 get_database_data($database, $stuff);
 
                 $log = get_log_activity($database);
@@ -441,8 +441,8 @@
           $is_public = FALSE;
           $user_data = array();
 
-          // allow direct login as u_public and assume/require that the u_public account already exists.
-          if ($_POST['login_name'] == 'u_public') {
+          // allow direct login as u_reservation_public and assume/require that the u_reservation_public account already exists.
+          if ($_POST['login_name'] == 'u_reservation_public') {
             $is_public = TRUE;
             $account_exists = FALSE;
           }
@@ -451,7 +451,7 @@
           }
 
           if (!$account_exists) {
-            $session->set_name('u_public');
+            $session->set_name('u_reservation_public');
             $session->set_password(NULL);
 
             if ($is_public === FALSE) {
@@ -462,21 +462,19 @@
             }
 
             #$database->set_persistent(TRUE);
-            assign_database_string($database, 'u_public', NULL);
+            assign_database_string($database, 'u_reservation_public', NULL);
             $connected = connect_database($database);
             if ($connected) {
               if ($is_public === FALSE) {
                 set_log_user($database, 'login_failure', $_POST['login_name'], NULL, 401);
-                set_log_activity($database, 401);
               }
               else {
                 set_log_user($database, 'login');
-                set_log_activity($database, 200);
               }
 
-              $user_data = get_user_data($database, $stuff, 'u_public');
+              $user_data = get_user_data($database, $stuff, 'u_reservation_public');
 
-              $stuff['login'] .= ' - Accessing database as: u_public' . '<br>' . "\n";
+              $stuff['login'] .= ' - Accessing database as: u_reservation_public' . '<br>' . "\n";
               $stuff['login'] .= ' - Your user id is: ' . (isset($user_data['id']) ? $user_data['id'] : 'does not exist') . '<br>' . "\n";
               $stuff['login'] .= '<br>' . "\n";
               $logged_in = TRUE;
@@ -522,7 +520,6 @@
               $cookie->set_max_age(NULL);
 
               set_log_user($database, 'login', NULL, $session_expire);
-              set_log_activity($database, 200);
 
               if ($result instanceof c_base_return_true) {
                 $data = array(
@@ -565,7 +562,8 @@
               }
 
               set_log_activity($database);
-              if (!empty($session->get_name()->get_value_exact()) && $session->get_name()->get_value_exact() != 'u_public') {
+
+              if (!empty($session->get_name()->get_value_exact()) && $session->get_name()->get_value_exact() != 'u_reservation_public') {
                 get_database_data($database, $stuff);
 
                 $log = get_log_activity($database);
@@ -685,7 +683,7 @@
   }
 
   function check_login_access(&$stuff, &$database, $username, $password, $session) {
-    if ($username == 'u_public') return FALSE;
+    if ($username == 'u_reservation_public') return FALSE;
 
     if ($database->is_connected() instanceof c_base_return_true) {
       return TRUE;
@@ -962,7 +960,7 @@
     unset($query_result);
 
     // if the user does not yet exist in the database (and is not the public user), then create it.
-    if (!isset($user_data['id']) && $user_name != 'u_public') {
+    if (!isset($user_data['id']) && $user_name != 'u_reservation_public') {
       if (is_null($ldap_data)) {
         $query_result = $database->do_query('insert into v_users_self_insert (name_human.first, name_human.last, name_human.complete, address_email, id_external) values (null, null, null, null, null)');
         if ($query_result instanceof c_base_return_false) {
diff --git a/program/reservation/index.php b/program/reservation/index.php
index d27fee6..669daec 100644
--- a/program/reservation/index.php
+++ b/program/reservation/index.php
@@ -39,7 +39,7 @@
     $settings['database_host'] = '127.0.0.1';
     $settings['database_port'] = 5432;
     $settings['database_name'] = 'reservation';
-    $settings['database_user'] = 'u_public';
+    $settings['database_user'] = 'u_reservation_public';
     $settings['database_password'] = NULL;
     $settings['database_timeout'] = 4;
     $settings['database_ssl_mode'] = 'require';
@@ -251,8 +251,8 @@
     if (!isset($_SERVER["HTTPS"])) {
       reservation_build_page_require_https($html, $settings, $session);
     }
-    elseif ($settings['database_user'] == 'u_public') {
-      // if the session cookie exists, but the user is still u_public, then the cookie is no longer valid.
+    elseif ($settings['database_user'] == 'u_reservation_public') {
+      // if the session cookie exists, but the user is still u_reservation_public, then the cookie is no longer valid.
       if (empty($session->get_session_id()->get_value_exact())) {
         // check to see if user has filled out the login form.
         if (isset($_SERVER['REQUEST_METHOD']) && $_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['form_id']) && $_POST['form_id'] == 'login_form') {
diff --git a/program/reservation/reservation_paths.php b/program/reservation/reservation_paths.php
index 8014dbc..6ffcf53 100644
--- a/program/reservation/reservation_paths.php
+++ b/program/reservation/reservation_paths.php
@@ -183,7 +183,7 @@ function reservation_attempt_login(&$database, &$settings, &$session) {
   }
 
   // explicitly deny access to internal user accounts
-  if ($_POST['login_form-username'] == 'u_public') {
+  if ($_POST['login_form-username'] == 'u_reservation_public') {
     $problems[] = c_base_form_problem::s_create_error('login_form-username', 'Unable to login, an incorrect user name or password has been specified.');
   }
 
-- 
1.8.3.1