cansjt commented on issue #17374:
URL: https://github.com/apache/airflow/issues/17374#issuecomment-891177075
FYI here is the SQL script I used to move things around:
```sql
TRUNCATE TABLE
airflow.ab_permission,
airflow.ab_view_menu,
airflow.ab_user_role,
airflow.ab_role,
airflow.ab_user,
airflow.ab_register_user,
airflow.ab_permission_view_role,
airflow.ab_permission_view ;
INSERT INTO airflow.ab_permission (name) SELECT name FROM
public.ab_permission ;
INSERT INTO airflow.ab_view_menu (name) SELECT name FROM public.ab_view_menu
;
INSERT INTO airflow.ab_role (name) SELECT name FROM public.ab_role ;
INSERT INTO airflow.ab_user (first_name,
last_name,
username,
password,
active,
email,
last_login,
login_count,
fail_login_count,
created_on,
changed_on,
created_by_fk,
changed_by_fk)
SELECT
first_name,
last_name,
username,
password,
active,
email,
last_login,
login_count,
fail_login_count,
created_on,
changed_on,
created_by_fk,
changed_by_fk
FROM public.ab_user ;
INSERT INTO airflow.ab_register_user (first_name,
last_name,
username,
password,
email,
registration_date,
registration_hash)
SELECT
first_name,
last_name,
username,
password,
email,
registration_date,
registration_hash
FROM public.ab_register_user ;
INSERT INTO airflow.ab_user_role (user_id, role_id)
SELECT
au.id AS user_id,
ar.id AS role_id
FROM
public.ab_user_role AS pur
JOIN public.ab_user AS pu ON pur.user_id = pu.id
JOIN public.ab_role AS pr ON pur.role_id = pr.id
JOIN airflow.ab_user AS au ON pu.username = au.username
JOIN airflow.ab_role AS ar ON pr.name = ar.name
;
INSERT INTO airflow.ab_permission_view (permission_id, view_menu_id)
SELECT
ap.id AS permission_id,
avm.id AS view_menu_id
FROM
public.ab_permission_view AS ppv
JOIN public.ab_permission AS pp ON ppv.permission_id = pp.id
JOIN public.ab_view_menu AS pvm ON ppv.view_menu_id = pvm.id
JOIN airflow.ab_view_menu AS avm ON pvm.name = avm.name
JOIN airflow.ab_permission AS ap ON ap.name = pp.name
;
INSERT INTO airflow.ab_permission_view_role (permission_view_id, role_id)
SELECT
pv_map.schema_permission_view_id AS permission_view_id,
ar.id AS role_id
FROM
public.ab_permission_view_role AS ppvr
JOIN (SELECT
ppv.id AS public_permission_view_id,
apv.id AS schema_permission_view_id
FROM
public.ab_permission_view AS ppv
JOIN public.ab_permission AS pp ON ppv.permission_id =
pp.id
JOIN public.ab_view_menu AS pvm ON ppv.view_menu_id =
pvm.id
JOIN airflow.ab_view_menu AS avm ON pvm.name =
avm.name
JOIN airflow.ab_permission AS ap ON ap.name =
pp.name
JOIN airflow.ab_permission_view AS apv
ON apv.view_menu_id = avm.id AND apv.permission_id =
ap.id
) AS pv_map ON ppvr.permission_view_id =
pv_map.public_permission_view_id
JOIN public.ab_role AS pr ON ppvr.role_id
= pr.id
JOIN airflow.ab_role AS ar ON pr.name
= ar.name
;
```
Ensures, in the most portable way I could think of, that there will be no
issues with ID (or sequences or whatever).
Controlled things went okay with these ones. They should not return any row,
if everything went fine:
```
SELECT
ar.name,
avm.name,
ap.name
FROM
airflow.ab_permission_view_role AS apvr
JOIN airflow.ab_permission_view AS apv ON apv.id =
apvr.permission_view_id
JOIN airflow.ab_view_menu AS avm ON avm.id = apv.view_menu_id
JOIN airflow.ab_role AS ar ON ar.id = apvr.role_id
JOIN airflow.ab_permission AS ap ON ap.id = apv.permission_id
EXCEPT
SELECT
pr.name,
pvm.name,
pp.name
FROM
public.ab_permission_view_role AS ppvr
JOIN public.ab_permission_view AS ppv ON ppv.id =
ppvr.permission_view_id
JOIN public.ab_view_menu AS pvm ON pvm.id = ppv.view_menu_id
JOIN public.ab_role AS pr ON pr.id = ppvr.role_id
JOIN public.ab_permission AS pp ON pp.id = ppv.permission_id
;
SELECT
au.username,
ar.name
FROM
airflow.ab_user_role AS aur
JOIN airflow.ab_user AS au ON aur.user_id = au.id
JOIN airflow.ab_role AS ar ON ar.id = aur.role_id
EXCEPT
SELECT
pu.username,
pr.name
FROM
public.ab_user_role AS pur
JOIN public.ab_user AS pu ON pur.user_id = pu.id
JOIN public.ab_role AS pr ON pr.id = pur.role_id
;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
