Changeset: 018c3b8aefa4 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/018c3b8aefa4
Added Files:
sql/test/BugTracker-2026/Tests/7782-roles-view.test
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/10_sys_schema_extension.sql
sql/test/BugTracker-2026/Tests/All
Branch: Dec2025
Log Message:
Fix #7782 correct definition of system view sys.roles.
diffs (128 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -5263,6 +5263,39 @@ sql_update_dec2025(Client c, mvc *sql, s
return err;
}
+static str
+sql_update_dec2025_sp1(Client c, mvc *sql, sql_schema *s)
+{
+ char *err = NULL;
+ res_table *output = NULL;
+ BAT *b;
+
+ /* 10_sys_schema_extension.sql */
+ /* correct definition of view sys.roles */
+ static const char query1[] = "select id from sys._tables where name =
'roles' and schema_id = 2000"
+ " and query = 'create view sys.roles as select id, name,
grantor from sys.auths a where a.name not in (select u.name from
sys.db_user_info u);';";
+ err = SQLstatementIntern(c, query1, "update", true, false, &output);
+ if (err == MAL_SUCCEED && (b = BBPquickdesc(output->cols[0].b)) &&
BATcount(b) == 1) {
+ static const char stmt1[] =
+ "DROP VIEW sys.roles CASCADE;\n"
+ "CREATE VIEW sys.roles AS SELECT id, name, grantor FROM
sys.auths;\n"
+ "GRANT SELECT ON sys.roles TO PUBLIC;\n"
+ "UPDATE sys._tables SET system = true WHERE not system
and schema_id = 2000 and name = 'roles';\n";
+ sql_table *t;
+ if ((t = mvc_bind_table(sql, s, "roles")) != NULL)
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ printf("Running database upgrade commands:\n%s\n", stmt1);
+ fflush(stdout);
+ err = SQLstatementIntern(c, stmt1, "update", true, false, NULL);
+ }
+ if (output != NULL) {
+ res_table_destroy(output);
+ output = NULL;
+ }
+
+ return err;
+}
+
int
SQLupgrades(Client c, mvc *m)
{
@@ -5346,6 +5379,11 @@ SQLupgrades(Client c, mvc *m)
goto handle_error;
}
+ if ((err = sql_update_dec2025_sp1(c, m, s)) != NULL) {
+ TRC_CRITICAL(SQL_PARSER, "%s\n", err);
+ goto handle_error;
+ }
+
return 0;
handle_error:
diff --git a/sql/scripts/10_sys_schema_extension.sql
b/sql/scripts/10_sys_schema_extension.sql
--- a/sql/scripts/10_sys_schema_extension.sql
+++ b/sql/scripts/10_sys_schema_extension.sql
@@ -505,8 +505,7 @@ GRANT SELECT ON sys.privilege_codes TO P
-- Utility views to list the defined roles and users.
--- Note: sys.auths contains both users and roles as the names must be distinct.
-CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE
a.name NOT IN (SELECT u.name FROM sys.db_user_info u);
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
GRANT SELECT ON sys.roles TO PUBLIC;
CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path,
max_memory, max_workers, optimizer, default_role FROM sys.db_user_info;
GRANT SELECT ON sys.users TO PUBLIC;
diff --git a/sql/test/BugTracker-2026/Tests/7782-roles-view.test
b/sql/test/BugTracker-2026/Tests/7782-roles-view.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7782-roles-view.test
@@ -0,0 +1,48 @@
+-- create a user WITHOUT a default role
+statement ok
+create user a with password 'A' name 'Aa'
+
+-- check the users
+query TT
+select name, fullname from sys.users where name = 'a'
+----
+a
+Aa
+
+statement error Role 'a' already exists
+create role a
+
+-- Apparently the create user a implicitly also creates a role with the same
name
+-- check the roles
+query T
+select name from sys.roles where name = 'a'
+----
+a
+
+-- check the auths
+query T
+select name from sys.auths where name = 'a'
+----
+a
+
+
+-- cleanup
+statement ok
+alter user "a" set schema "sys"
+
+statement ok
+drop schema "a" cascade
+
+statement ok
+drop user "a"
+
+-- check the users
+query TT
+select name, fullname from sys.users where name = 'a'
+----
+
+-- check the roles
+query T
+select name from sys.roles where name = 'a'
+----
+
diff --git a/sql/test/BugTracker-2026/Tests/All
b/sql/test/BugTracker-2026/Tests/All
--- a/sql/test/BugTracker-2026/Tests/All
+++ b/sql/test/BugTracker-2026/Tests/All
@@ -1,2 +1,3 @@
7780-unnest-slow
+7782-roles-view
7783-deleted-row
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
