refactoring datbase roles for radius

4 files changed, 90 insertions, 13 deletions

diff --git a/notes/projects/radius/sql/ippool-database.sql b/notes/projects/radius/sql/ippool-database.sql
index 660e676..1a4f4de 100644
--- a/notes/projects/radius/sql/ippool-database.sql
+++ b/notes/projects/radius/sql/ippool-database.sql
@@ -13,19 +13,8 @@ SET client_min_messages TO WARNING;
 \set ON_ERROR_STOP on
 
 
-CREATE ROLE radius_ippool
-  WITH              NOSUPERUSER
-                    NOCREATEDB
-                    NOCREATEROLE
-                    NOINHERIT
-                    NOLOGIN
-                    NOREPLICATION
-                    NOBYPASSRLS
-                    ADMIN           admin;
-
-
 CREATE DATABASE radius_ippool
-  WITH              OWNER           = radius_ippool
+   WITH             OWNER           = radius
                     ENCODING        = "UTF8"
                     LC_COLLATE      = "en_US.UTF-8"
                     LC_CTYPE        = "en_US.UTF-8";
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index f0b259d..b948e58 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -7,11 +7,42 @@
  */
 
 
-SET ROLE radius_ippool;
+SET ROLE radius;
 SET client_min_messages TO WARNING;
 \set ON_ERROR_STOP on
 
 
+-- //////////////////
+-- //              //
+-- //  Privileges  //
+-- //              //
+-- //////////////////
+
+
+-- GRANT               CONNECT
+--    ON DATABASE      radius_ippool
+--    TO               radius_user;
+
+
+GRANT               USAGE
+   ON SCHEMA        public
+   TO               radius_user;
+
+
+ALTER DEFAULT PRIVILEGES
+   IN SCHEMA        public
+   GRANT            SELECT
+      ON            TABLES
+      TO            radius_user;
+
+
+ALTER DEFAULT PRIVILEGES
+   IN SCHEMA        public
+   GRANT            EXECUTE
+      ON            FUNCTIONS
+      TO            radius_admin;
+
+
 -- //////////////
 -- //          //
 -- //  Tables  //
@@ -212,6 +243,7 @@ CREATE OR REPLACE FUNCTION ip_add_network
 )
    RETURNS          INTEGER
    LANGUAGE         plpgsql
+   SECURITY         DEFINER
    AS               $$
 DECLARE
    ipAddress        INET;
@@ -276,6 +308,7 @@ CREATE OR REPLACE FUNCTION ip_assign
 )
    RETURNS          BOOLEAN
    LANGUAGE         plpgsql
+   SECURITY         DEFINER
    AS               $$
 DECLARE
 BEGIN
@@ -416,6 +449,7 @@ CREATE OR REPLACE FUNCTION nas_add
 )
    RETURNS          INTEGER
    LANGUAGE         plpgsql
+   SECURITY         DEFINER
    AS               $$
 DECLARE
    nasRegionId      INTEGER;
@@ -492,6 +526,7 @@ CREATE OR REPLACE FUNCTION netblock_add
 )
    RETURNS          INTEGER
    LANGUAGE         plpgsql
+   SECURITY         DEFINER
    AS               $$
 DECLARE
    netRegionId      INTEGER      DEFAULT NULL;
@@ -676,6 +711,7 @@ CREATE OR REPLACE FUNCTION pool_add
 )
    RETURNS          INTEGER
    LANGUAGE         plpgsql
+   SECURITY         DEFINER
    AS               $$
 DECLARE
    returnId         INTEGER;
@@ -721,6 +757,7 @@ CREATE OR REPLACE FUNCTION region_add
 )
    RETURNS          INTEGER
    LANGUAGE         plpgsql
+   SECURITY         DEFINER
    AS               $$
 DECLARE
    returnId         INTEGER;
diff --git a/notes/projects/radius/sql/ippool.md b/notes/projects/radius/sql/ippool.md
index 72633f5..d5c1223 100644
--- a/notes/projects/radius/sql/ippool.md
+++ b/notes/projects/radius/sql/ippool.md
@@ -17,11 +17,16 @@ Files
    * ippool-schema.sql   - Creates database schema (tables, indexes, etc)
    * ippool-demo.sql     - Loads demo data
    * ippool-queries.sql  - Common or useful queries
+   * radius-roles.sql    - Common database roles for RADIUS
 
 
 Initializing New Database
 -------------------------
 
+ * Create base database roles for RADIUS, not not already created:
+
+      psql -h raddbtest.prv.acsalaska.net -f radius-roles.sql
+
  * Create database and load schema:
 
       psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql
diff --git a/notes/projects/radius/sql/radius-roles.sql b/notes/projects/radius/sql/radius-roles.sql
new file mode 100644
index 0000000..c609c6f
--- /dev/null
+++ b/notes/projects/radius/sql/radius-roles.sql
@@ -0,0 +1,46 @@
+/*
+ *  IP Engineering RADIUS Servers
+ *
+ *  radius-roles.sql - creates base radius roles
+ *
+ *  Usage:  psql -f radius-roles.sql
+ *
+ */
+
+
+SET ROLE wheel;
+SET client_min_messages TO WARNING;
+\set ON_ERROR_STOP on
+
+
+CREATE ROLE radius
+   WITH             NOSUPERUSER
+                    NOCREATEDB
+                    NOCREATEROLE
+                    NOINHERIT
+                    NOLOGIN
+                    NOREPLICATION
+                    ADMIN wheel;
+
+
+CREATE ROLE radius_admin
+   WITH             NOSUPERUSER
+                    NOCREATEDB
+                    NOCREATEROLE
+                    INHERIT
+                    NOLOGIN
+                    NOREPLICATION;
+
+
+CREATE ROLE radius_user
+   WITH             NOSUPERUSER
+                    NOCREATEDB
+                    NOCREATEROLE
+                    INHERIT
+                    NOLOGIN
+                    NOREPLICATION
+                    NOBYPASSRLS
+                    ADMIN radius_admin;
+
+
+/* end of sql */