MySQL & Postgres Enhancements (#5)

* remove mysql login script and add password script

* move excess mysql password query to new script

* add db admin folders

* add postgres

* add mongo admins script

* Commit from GitHub Actions (Ruff)

* update tests for mysql and postgres

* update tests for mysql and postgres

---------

Co-authored-by: github-actions <41898282+github-actions[bot]@users.noreply.github.com>

12 files changed, 296 insertions, 6 deletions

diff --git a/databases/administrators/mssql_admins.sql b/databases/administrators/microsoft-sql/mssql_admins.sql
index 278fafc..278fafc 100644
--- a/databases/administrators/mssql_admins.sql
+++ b/databases/administrators/microsoft-sql/mssql_admins.sql
diff --git a/databases/administrators/mongo/README.org b/databases/administrators/mongo/README.org
new file mode 100644
index 0000000..689d37d
--- /dev/null
+++ b/databases/administrators/mongo/README.org
@@ -0,0 +1,104 @@
+#+title: MongoDB Scripts

+

+* =admins.py=

+

+Dependency:

+

+#+begin_src shell

+pip install pymongo

+#+end_src

+

+#+begin_src python

+python ./admins.py

+#+end_src

+

+Example output:

+

+#+begin_src json

+[

+  {

+    "_id": "admin.admin",

+    "user": "admin",

+    "db": "admin",

+    "roles": [

+      {

+        "role": "userAdminAnyDatabase",

+        "db": "admin"

+      },

+      {

+        "role": "readWriteAnyDatabase",

+        "db": "admin"

+      },

+      {

+        "role": "dbAdminAnyDatabase",

+        "db": "admin"

+      },

+      {

+        "role": "clusterAdmin",

+        "db": "admin"

+      }

+    ],

+    "credentials": {

+      "SCRAM-SHA-1": {

+        "iterationCount": 10000,

+        "salt": "abc123",

+        "storedKey": "storedKeyHash",

+        "serverKey": "serverKeyHash"

+      },

+      "SCRAM-SHA-256": {

+        "iterationCount": 15000,

+        "salt": "def456",

+        "storedKey": "storedKeyHash256",

+        "serverKey": "serverKeyHash256"

+      }

+    }

+  },

+  {

+    "_id": "test.user1",

+    "user": "user1",

+    "db": "test",

+    "roles": [

+      {

+        "role": "readWrite",

+        "db": "test"

+      }

+    ],

+    "credentials": {

+      "SCRAM-SHA-1": {

+        "iterationCount": 10000,

+        "salt": "ghi789",

+        "storedKey": "storedKeyHashUser1",

+        "serverKey": "serverKeyHashUser1"

+      }

+    }

+  },

+  {

+    "_id": "test.ldapUser",

+    "user": "ldapUser",

+    "db": "test",

+    "roles": [

+      {

+        "role": "read",

+        "db": "test"

+      }

+    ],

+    "userSource": "ldap"

+  },

+  {

+    "_id": "admin.x509User",

+    "user": "x509User",

+    "db": "$external",

+    "roles": [

+      {

+        "role": "readWrite",

+        "db": "admin"

+      }

+    ],

+    "credentials": {

+      "MONGODB-X509": {

+        "subject": "CN=x509User,OU=OrgUnit,O=Org,L=City,ST=State,C=Country"

+      }

+    }

+  }

+]

+#+end_src

diff --git a/databases/administrators/mongo/admins.py b/databases/administrators/mongo/admins.py
new file mode 100644
index 0000000..e844cbc
--- /dev/null
+++ b/databases/administrators/mongo/admins.py
@@ -0,0 +1,16 @@
+from pymongo import MongoClient

+

+# Connect to the MongoDB server

+client = MongoClient("mongodb://localhost:27017/")

+

+# Select the 'admin' database

+db = client.admin

+

+# Query the 'system.users' collection

+users = db.system.users.find(

+    {}, {"user": 1, "db": 1, "roles": 1, "credentials": 1, "userSource": 1}

+)

+

+# Print the results in a pretty format

+for user in users:

+    print(user)

diff --git a/databases/administrators/mysql/README.org b/databases/administrators/mysql/README.org
new file mode 100644
index 0000000..82ae540
--- /dev/null
+++ b/databases/administrators/mysql/README.org
@@ -0,0 +1,108 @@
+#+title: MySQL Admins
+
+* =mysql_admins.sql=
+
+#+begin_src sql
+SELECT * FROM information_schema.user_privileges;
+#+end_src
+
+#+begin_src
+MySQL [(none)]> SELECT * FROM information_schema.user_privileges;
++--------------------------------+---------------+---------------------------------+--------------+
+| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE                  | IS_GRANTABLE |
++--------------------------------+---------------+---------------------------------+--------------+
+| 'mysql.infoschema'@'localhost' | def           | SELECT                          | NO           |
+| 'mysql.infoschema'@'localhost' | def           | AUDIT_ABORT_EXEMPT              | NO           |
+| 'mysql.infoschema'@'localhost' | def           | FIREWALL_EXEMPT                 | NO           |
+| 'mysql.infoschema'@'localhost' | def           | SYSTEM_USER                     | NO           |
+| 'mysql.session'@'localhost'    | def           | SHUTDOWN                        | NO           |
+| 'mysql.session'@'localhost'    | def           | SUPER                           | NO           |
+| 'mysql.session'@'localhost'    | def           | AUDIT_ABORT_EXEMPT              | NO           |
+| 'mysql.session'@'localhost'    | def           | AUTHENTICATION_POLICY_ADMIN     | NO           |
+| 'mysql.session'@'localhost'    | def           | BACKUP_ADMIN                    | NO           |
+| 'mysql.session'@'localhost'    | def           | CLONE_ADMIN                     | NO           |
+| 'mysql.session'@'localhost'    | def           | CONNECTION_ADMIN                | NO           |
+| 'mysql.session'@'localhost'    | def           | FIREWALL_EXEMPT                 | NO           |
+| 'mysql.session'@'localhost'    | def           | PERSIST_RO_VARIABLES_ADMIN      | NO           |
+| 'mysql.session'@'localhost'    | def           | SESSION_VARIABLES_ADMIN         | NO           |
+| 'mysql.session'@'localhost'    | def           | SYSTEM_USER                     | NO           |
+| 'mysql.session'@'localhost'    | def           | SYSTEM_VARIABLES_ADMIN          | NO           |
+| 'mysql.sys'@'localhost'        | def           | USAGE                           | NO           |
+| 'mysql.sys'@'localhost'        | def           | AUDIT_ABORT_EXEMPT              | NO           |
+| 'mysql.sys'@'localhost'        | def           | FIREWALL_EXEMPT                 | NO           |
+| 'mysql.sys'@'localhost'        | def           | SYSTEM_USER                     | NO           |
+| 'root'@'localhost'             | def           | SELECT                          | YES          |
+| 'root'@'localhost'             | def           | INSERT                          | YES          |
+| 'root'@'localhost'             | def           | UPDATE                          | YES          |
+| 'root'@'localhost'             | def           | DELETE                          | YES          |
+| 'root'@'localhost'             | def           | CREATE                          | YES          |
+| 'root'@'localhost'             | def           | DROP                            | YES          |
+| 'root'@'localhost'             | def           | RELOAD                          | YES          |
+| 'root'@'localhost'             | def           | SHUTDOWN                        | YES          |
+| 'root'@'localhost'             | def           | PROCESS                         | YES          |
+| 'root'@'localhost'             | def           | FILE                            | YES          |
+| 'root'@'localhost'             | def           | REFERENCES                      | YES          |
+| 'root'@'localhost'             | def           | INDEX                           | YES          |
+| 'root'@'localhost'             | def           | ALTER                           | YES          |
+| 'root'@'localhost'             | def           | SHOW DATABASES                  | YES          |
+| 'root'@'localhost'             | def           | SUPER                           | YES          |
+| 'root'@'localhost'             | def           | CREATE TEMPORARY TABLES         | YES          |
+| 'root'@'localhost'             | def           | LOCK TABLES                     | YES          |
+| 'root'@'localhost'             | def           | EXECUTE                         | YES          |
+| 'root'@'localhost'             | def           | REPLICATION SLAVE               | YES          |
+| 'root'@'localhost'             | def           | REPLICATION CLIENT              | YES          |
+| 'root'@'localhost'             | def           | CREATE VIEW                     | YES          |
+| 'root'@'localhost'             | def           | SHOW VIEW                       | YES          |
+| 'root'@'localhost'             | def           | CREATE ROUTINE                  | YES          |
+| 'root'@'localhost'             | def           | ALTER ROUTINE                   | YES          |
+| 'root'@'localhost'             | def           | CREATE USER                     | YES          |
+| 'root'@'localhost'             | def           | EVENT                           | YES          |
+| 'root'@'localhost'             | def           | TRIGGER                         | YES          |
+| 'root'@'localhost'             | def           | CREATE TABLESPACE               | YES          |
+| 'root'@'localhost'             | def           | CREATE ROLE                     | YES          |
+| 'root'@'localhost'             | def           | DROP ROLE                       | YES          |
+| 'root'@'localhost'             | def           | ALLOW_NONEXISTENT_DEFINER       | YES          |
+| 'root'@'localhost'             | def           | APPLICATION_PASSWORD_ADMIN      | YES          |
+| 'root'@'localhost'             | def           | AUDIT_ABORT_EXEMPT              | YES          |
+| 'root'@'localhost'             | def           | AUDIT_ADMIN                     | YES          |
+| 'root'@'localhost'             | def           | AUTHENTICATION_POLICY_ADMIN     | YES          |
+| 'root'@'localhost'             | def           | BACKUP_ADMIN                    | YES          |
+| 'root'@'localhost'             | def           | BINLOG_ADMIN                    | YES          |
+| 'root'@'localhost'             | def           | BINLOG_ENCRYPTION_ADMIN         | YES          |
+| 'root'@'localhost'             | def           | CLONE_ADMIN                     | YES          |
+| 'root'@'localhost'             | def           | CONNECTION_ADMIN                | YES          |
+| 'root'@'localhost'             | def           | CREATE_SPATIAL_REFERENCE_SYSTEM | YES          |
+| 'root'@'localhost'             | def           | ENCRYPTION_KEY_ADMIN            | YES          |
+| 'root'@'localhost'             | def           | FIREWALL_EXEMPT                 | YES          |
+| 'root'@'localhost'             | def           | FLUSH_OPTIMIZER_COSTS           | YES          |
+| 'root'@'localhost'             | def           | FLUSH_PRIVILEGES                | YES          |
+| 'root'@'localhost'             | def           | FLUSH_STATUS                    | YES          |
+| 'root'@'localhost'             | def           | FLUSH_TABLES                    | YES          |
+| 'root'@'localhost'             | def           | FLUSH_USER_RESOURCES            | YES          |
+| 'root'@'localhost'             | def           | GROUP_REPLICATION_ADMIN         | YES          |
+| 'root'@'localhost'             | def           | GROUP_REPLICATION_STREAM        | YES          |
+| 'root'@'localhost'             | def           | INNODB_REDO_LOG_ARCHIVE         | YES          |
+| 'root'@'localhost'             | def           | INNODB_REDO_LOG_ENABLE          | YES          |
+| 'root'@'localhost'             | def           | OPTIMIZE_LOCAL_TABLE            | YES          |
+| 'root'@'localhost'             | def           | PASSWORDLESS_USER_ADMIN         | YES          |
+| 'root'@'localhost'             | def           | PERSIST_RO_VARIABLES_ADMIN      | YES          |
+| 'root'@'localhost'             | def           | REPLICATION_APPLIER             | YES          |
+| 'root'@'localhost'             | def           | REPLICATION_SLAVE_ADMIN         | YES          |
+| 'root'@'localhost'             | def           | RESOURCE_GROUP_ADMIN            | YES          |
+| 'root'@'localhost'             | def           | RESOURCE_GROUP_USER             | YES          |
+| 'root'@'localhost'             | def           | ROLE_ADMIN                      | YES          |
+| 'root'@'localhost'             | def           | SENSITIVE_VARIABLES_OBSERVER    | YES          |
+| 'root'@'localhost'             | def           | SERVICE_CONNECTION_ADMIN        | YES          |
+| 'root'@'localhost'             | def           | SESSION_VARIABLES_ADMIN         | YES          |
+| 'root'@'localhost'             | def           | SET_ANY_DEFINER                 | YES          |
+| 'root'@'localhost'             | def           | SHOW_ROUTINE                    | YES          |
+| 'root'@'localhost'             | def           | SYSTEM_USER                     | YES          |
+| 'root'@'localhost'             | def           | SYSTEM_VARIABLES_ADMIN          | YES          |
+| 'root'@'localhost'             | def           | TABLE_ENCRYPTION_ADMIN          | YES          |
+| 'root'@'localhost'             | def           | TELEMETRY_LOG_ADMIN             | YES          |
+| 'root'@'localhost'             | def           | TRANSACTION_GTID_TAG            | YES          |
+| 'root'@'localhost'             | def           | XA_RECOVER_ADMIN                | YES          |
+| 'cmc'@'%'                      | def           | USAGE                           | NO           |
++--------------------------------+---------------+---------------------------------+--------------+
+92 rows in set (0.001 sec)
+#+end_src
diff --git a/databases/administrators/mysql/mysql_admins.sql b/databases/administrators/mysql/mysql_admins.sql
new file mode 100644
index 0000000..9115ec5
--- /dev/null
+++ b/databases/administrators/mysql/mysql_admins.sql
@@ -0,0 +1 @@
+SELECT * FROM information_schema.user_privileges;
diff --git a/databases/administrators/mysql_admins_alt.sql b/databases/administrators/mysql/mysql_admins_alt.sql
index ac855f4..9552ee2 100644
--- a/databases/administrators/mysql_admins_alt.sql
+++ b/databases/administrators/mysql/mysql_admins_alt.sql
@@ -12,7 +12,3 @@ WHERE db = @db_name;
 -- Column Permissions
 SELECT ... FROM mysql.columns_priv
 WHERE db = @db_name;
-
--- Password Configuration
-SHOW GLOBAL VARIABLES LIKE 'validate_password%';
-SHOW VARIABLES LIKE 'validate_password%';
diff --git a/databases/administrators/mysql_admins.sh b/databases/administrators/mysql_admins.sh
deleted file mode 100644
index 6faa2c9..0000000
--- a/databases/administrators/mysql_admins.sh
+++ /dev/null
@@ -1 +0,0 @@
-mysql -u root -p
diff --git a/databases/administrators/mysql_admins.sql b/databases/administrators/mysql_admins.sql
deleted file mode 100644
index 9efa567..0000000
--- a/databases/administrators/mysql_admins.sql
+++ /dev/null
@@ -1 +0,0 @@
-SELECT ** FROM information_schema.user_privileges;
diff --git a/databases/administrators/oracle_admins.sql b/databases/administrators/oracle/oracle_admins.sql
index bac5934..bac5934 100644
--- a/databases/administrators/oracle_admins.sql
+++ b/databases/administrators/oracle/oracle_admins.sql
diff --git a/databases/administrators/oracle_admins_alt.sql b/databases/administrators/oracle/oracle_admins_alt.sql
index 4486829..4486829 100644
--- a/databases/administrators/oracle_admins_alt.sql
+++ b/databases/administrators/oracle/oracle_admins_alt.sql
diff --git a/databases/administrators/postgres/README.org b/databases/administrators/postgres/README.org
new file mode 100644
index 0000000..fe361de
--- /dev/null
+++ b/databases/administrators/postgres/README.org
@@ -0,0 +1,45 @@
+#+title: Postgres Admins
+
+* =admins.sql=
+
+#+begin_src sql
+SELECT 
+    r.rolname AS role_name,
+    r.rolsuper AS is_superuser,
+    r.rolinherit AS inherits_privileges,
+    r.rolcreaterole AS can_create_roles,
+    r.rolcreatedb AS can_create_db,
+    r.rolcanlogin AS can_login,
+    r.rolreplication AS can_replication,
+    r.rolconnlimit AS connection_limit,
+    r.rolvaliduntil AS valid_until,
+    ARRAY(
+        SELECT b.rolname
+        FROM pg_auth_members m
+        JOIN pg_roles b ON (m.roleid = b.oid)
+        WHERE m.member = r.oid
+    ) AS member_of
+FROM pg_roles r;
+#+end_src
+
+#+begin_src
+| role_name                   | is_superuser | inherits_privileges | can_create_roles | can_create_db | can_login | can_replication | connection_limit | valid_until            | member_of                                                    |
+|-----------------------------+--------------+---------------------+------------------+---------------+-----------+-----------------+------------------+------------------------+--------------------------------------------------------------|
+| cmc                         | true         | true                | true             | true          | true      | true            |               -1 |                        | {}                                                           |
+| pg_database_owner           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_read_all_data            | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_write_all_data           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_monitor                  | false        | true                | false            | false         | false     | false           |               -1 |                        | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} |
+| pg_read_all_settings        | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_read_all_stats           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_stat_scan_tables         | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_read_server_files        | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_write_server_files       | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_execute_server_program   | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_signal_backend           | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_checkpoint               | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_maintain                 | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_use_reserved_connections | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| pg_create_subscription      | false        | true                | false            | false         | false     | false           |               -1 |                        | {}                                                           |
+| testuser                    | false        | true                | false            | false         | true      | false           |               -1 | 2025-12-31 00:00:00-06 | {}                                                           |
+#+end_src
diff --git a/databases/administrators/postgres/admins.sql b/databases/administrators/postgres/admins.sql
new file mode 100644
index 0000000..6f9d320
--- /dev/null
+++ b/databases/administrators/postgres/admins.sql
@@ -0,0 +1,22 @@
+-- References:

+--           : https://www.postgresql.org/docs/current/user-manag.html

+--           : https://www.postgresql.org/docs/current/view-pg-roles.html

+--           : https://www.postgresql.org/docs/current/catalog-pg-auth-members.html

+

+SELECT 

+    r.rolname AS role_name,

+    r.rolsuper AS is_superuser,

+    r.rolinherit AS inherits_privileges,

+    r.rolcreaterole AS can_create_roles,

+    r.rolcreatedb AS can_create_db,

+    r.rolcanlogin AS can_login,

+    r.rolreplication AS can_replication,

+    r.rolconnlimit AS connection_limit,

+    r.rolvaliduntil AS valid_until,

+    ARRAY(

+        SELECT b.rolname

+        FROM pg_auth_members m

+        JOIN pg_roles b ON (m.roleid = b.oid)

+        WHERE m.member = r.oid

+    ) AS member_of

+FROM pg_roles r;
\ No newline at end of file