Skip to content

2.29.0.0-b85

2.29.0.0-b85: [#28977] YSQL: Add upgrade check for roles with yb prefix
@fizaaluthra fizaaluthra tagged this 22 Oct 15:47 
Summary:
The yb_ prefix is reserved for system roles in YugabyteDB. During major
version upgrades, pg_dumpall skips dumping roles with this prefix (except
yb_superuser, which is created by YBA/YBM).

This revision adds a preflight check in pg_upgrade that fails if any user-defined
roles (OID >= 16384) with the yb_ prefix exist, except for the special
yb_superuser role. Users must drop or rename these roles before upgrade.

Jira: DB-18721

Test Plan:
./yb_build.sh release --cxx-test integration-tests_ysql_major_upgrade_check-test --gtest_filter  YsqlMajorUpgradeCheckTest.PgUpgradeChecks

./yb_build.sh release --cxx-test integration-tests_ysql_major_upgrade_check-test --gtest_filter  YsqlMajorUpgradeCheckTest.YbPrefixRoles

Manual test for new preflight check that checks for role with the "yb_" prefix:

Failed check:
```
ysqlsh (11.2-YB-2024.2.3.0-b0)
Type "help" for help.

yugabyte=# CREATE ROLE yb_test_role;
CREATE ROLE
yugabyte=# CREATE ROLE yb_test_role2;
CREATE ROLE
yugabyte=# CREATE ROLE normal_role;
CREATE ROLE
```

```
$ ./build/latest/postgres_build/src/bin/pg_upgrade/pg_upgrade --check -U yugabyte --old-host 127.0.0.1 --old-port 5433 --old-datadir ~/yugabyte-data/node-1/disk-1/pg_data_11
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking attributes of the 'yugabyte' user                  ok
Checking for all 3 system databases                         ok
Checking database connection settings                       ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for removed "abstime" data type in user tables     ok
Checking for removed "reltime" data type in user tables     ok
Checking for removed "tinterval" data type in user tables   ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid indexes                                ok
Checking installed extensions                               ok
Checking for roles starting with "yb_"                      fatal

Roles with "yb_" prefix:
  yb_test_role
  yb_test_role2

Your installation contains roles starting with "yb_".
The "yb_" prefix is reserved for system use and these roles must be
dropped or renamed before upgrading. A list of the problematic roles
is printed above and in the file:
    /Users/fizaaluthra/yugabyte-data/node-1/disk-1/pg_data_11/pg_upgrade_output.d/20251021T114213.147/roles_with_yb_prefix.txt

Failure, exiting
```

Successful check:
```
ysqlsh (11.2-YB-2024.2.3.0-b0)
Type "help" for help.

yugabyte=# DROP ROLE yb_test_role;
DROP ROLE
yugabyte=# DROP ROLE yb_test_role2;
DROP ROLE
```

```
$ ./build/latest/postgres_build/src/bin/pg_upgrade/pg_upgrade --check -U yugabyte --old-host 127.0.0.1 --old-port 5433 --old-datadir ~/yugabyte-data/node-1/disk-1/pg_data_11
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking attributes of the 'yugabyte' user                  ok
Checking for all 3 system databases                         ok
Checking database connection settings                       ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for removed "abstime" data type in user tables     ok
Checking for removed "reltime" data type in user tables     ok
Checking for removed "tinterval" data type in user tables   ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid indexes                                ok
Checking installed extensions                               ok
Checking for roles starting with "yb_"                      ok

*Clusters are compatible*
```

Reviewers: hsunder

Reviewed By: hsunder

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D47645
Assets 2
Loading