Skip to content

2.27.0.0-b465

2.27.0.0-b465: [#28235] YSQL: Use dump_role_checks in ysql_dumpall for YB profiles
@OlegLoginov OlegLoginov tagged this 19 Aug 12:30 
Summary:
In the tools `ysql_dumpall` apply the flag `--dump_role_checks`
to the statement `ALTER ROLE` after `CREATE PROFILE`.
New implementation:
```
CREATE PROFILE profile1 LIMIT FAILED_LOGIN_ATTEMPTS 2;
...
SELECT EXISTS(SELECT 1 FROM pg_roles WHERE rolname = 'role1') AS role_exists \gset
\if :role_exists
    ALTER ROLE role1 PROFILE profile1;
    UPDATE pg_catalog.pg_yb_role_profile
    SET rolprfstatus = 'l',
        rolprffailedloginattempts = 4
    WHERE rolprfrole = (SELECT oid FROM pg_authid WHERE rolname = 'role1'
      AND rolprfprofile = (SELECT oid FROM pg_yb_profile WHERE prfname = 'profile1');
\else
    \echo 'Skipping alter role due to missing role:' role1
\endif
```

Note: `use_roles`is NOT used for global objects output by `ysql_dumpall`.

Jira: DB-17899

Test Plan:
./yb_build.sh --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithDumpRoleChecks
./yb_build.sh --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithYbMetadata
./yb_build.sh --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithoutYbMetadata
./yb_build.sh --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllRoleProfiles

Other backup tests:
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testBackupRestoreRoles
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testBackupRolesWithDumpRoleChecks
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testBackupRolesWithoutDumpRoleChecks
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testBackupRolesWithoutUseRoles
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testBackupRolesWithoutRestoreRoles

Reviewers: mihnea, sanketh

Reviewed By: sanketh

Subscribers: yql

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