Thinh Lam
34 lines
1.4 KiB
SQL
34 lines
1.4 KiB
SQL
-- Policy-sourced admin rows: safe to drop when email leaves AUTH_ADMIN_EMAILS (app reconciliation).
|
|
-- Apply on existing DBs: docker exec -i initiative-postgres psql -U initiative -d initiatives < be0/migrations/007_user_roles_email_policy_admin.sql
|
|
-- Fresh docker-compose init: add this file as docker-entrypoint-initdb.d/07_*.sql
|
|
|
|
ALTER TABLE user_roles ADD COLUMN IF NOT EXISTS admin_from_email_policy BOOLEAN NOT NULL DEFAULT FALSE;
|
|
|
|
COMMENT ON COLUMN user_roles.admin_from_email_policy IS
|
|
'TRUE when admin was granted by email allow-list (AUTH_ADMIN_EMAILS). Reconciliation may DELETE this row if the user email is no longer in the list. FALSE preserves manually granted admin (future / exceptional).';
|
|
|
|
-- One-time cleanup: remove admin for addresses not in the default institutional allow-list
|
|
-- (must match default in auth_api._DEFAULT_POLICY_ADMIN_EMAILS when AUTH_ADMIN_EMAILS is unset).
|
|
DELETE FROM user_roles ur
|
|
USING users u
|
|
WHERE ur.user_id = u.id
|
|
AND ur.role::text = 'admin'
|
|
AND lower(u.email::text) NOT IN (
|
|
'thaontt@ump.edu.vn',
|
|
'nltanh@ump.edu.vn',
|
|
'ldbaochau@ump.edu.vn',
|
|
'htchuong@ump.edu.vn'
|
|
);
|
|
|
|
UPDATE user_roles ur
|
|
SET admin_from_email_policy = TRUE
|
|
FROM users u
|
|
WHERE ur.user_id = u.id
|
|
AND ur.role::text = 'admin'
|
|
AND lower(u.email::text) IN (
|
|
'thaontt@ump.edu.vn',
|
|
'nltanh@ump.edu.vn',
|
|
'ldbaochau@ump.edu.vn',
|
|
'htchuong@ump.edu.vn'
|
|
);
|