MySQLやPostgreSQLに特定のデータベースなどを移行する時に、予めユーザー情報を別途取得して流し込んでおく必要があります。
そのな時に、サクッと情報を取得する方法を改めてシンプルにメモしておきます。

MySQLにおけるユーザー一覧と権限の確認

    存在するユーザーは,mysql.userテーブルを参照すれば確認可能

root@localhost [mysql]> select user,host,Super_priv,account_locked from user;
+------------------+----------------+------------+----------------+
| user             | host           | Super_priv | account_locked |
+------------------+----------------+------------+----------------+
| app_user         | %              | N          | N              |
| ssl_user         | 127.0.0.1      | N          | N              |
| mysql.infoschema | localhost      | N          | Y              |
| mysql.session    | localhost      | Y          | Y              |
| mysql.sys        | localhost      | N          | Y              |
| root             | localhost      | Y          | N              |
+------------------+----------------+------------+----------------+
7 rows in set (0.01 sec)

特定のユーザーだけを移行したい場合は、SHOW GRANTSで特定ユーザー情報のみに設定された権限を確認して、

    アカウントを作成後に移行先のデータベースにて権限を付与してあげればOK。

root@localhost [mysql]> show grants for root@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.00 sec)

root@localhost [mysql]> show grants for app_user@'%'\G
*************************** 1. row ***************************
Grants for app_user@%: GRANT USAGE ON *.* TO `app_user`@`%`
*************************** 2. row ***************************
Grants for app_user@%: GRANT ALL PRIVILEGES ON `APP_DB`.* TO `app_user`@`%`
2 rows in set (0.00 sec)

root@localhost [mysql]> 

WORKBENCHで勿論、確認する事が可能です。

PostgreSQLにおけるユーザー一覧と権限の確認

    特定データベース移行前にユーザー(ROLE)がいないとエラーになるので、予めROLE情報をダンプして流し込んでおいてあげてください。

postgres=# \du
                                                          ロール一覧
     ロール名     |                                   属性                                   |          所属グループ          
------------------+--------------------------------------------------------------------------+--------------------------------
 admin            | スーパユーザ                                                             | {pg_monitor,pg_signal_backend}
 application_role | スーパユーザ                                                             | {}
 postgres         | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
 replication_user | レプリケーション可                                                       | {}
 user_a           | スーパユーザ                                                             | {}
 user_b           | スーパユーザ                                                             | {}
 weather_app      | DB作成可                                                                 | {}

postgres=# select rolname, rolsuper, rolcanlogin from pg_roles;
          rolname          | rolsuper | rolcanlogin 
---------------------------+----------+-------------
 pg_monitor                | f        | f
 pg_read_all_settings      | f        | f
 pg_read_all_stats         | f        | f
 pg_stat_scan_tables       | f        | f
 pg_read_server_files      | f        | f
 pg_write_server_files     | f        | f
 pg_execute_server_program | f        | f
 pg_signal_backend         | f        | f
 admin                     | t        | t
 application_role          | t        | t
 postgres                  | t        | t
 replication_user          | f        | t
 user_a                    | t        | t
 user_b                    | t        | t
 weather_app               | f        | t
(15 行)

postgres-# \z
                                アクセス権限
 スキーマ |       名前       |    型    | アクセス権限 | 列の権限 | ポリシー 
----------+------------------+----------+--------------+----------+----------
 public   | pgbench_accounts | テーブル |              |          | 
 public   | pgbench_branches | テーブル |              |          | 
 public   | pgbench_history  | テーブル |              |          | 
 public   | pgbench_tellers  | テーブル |              |          | 
(4 行)

    pg_dumpallでアカウント情報をダウンプする事が可能です。

-bash-4.2$ pg_dumpall -U postgres  --roles-only > dump_role_only.sql
-bash-4.2$ cat dump_role_only.sql 
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE admin;
ALTER ROLE admin WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5d9b39f44ce7405804d605804d6';
CREATE ROLE application_role;
ALTER ROLE application_role WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5d9b39f44ce7405804d605804d6';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md5d9b39f44ce7405804d605804d6';

    pg_adminでも付与さけた権限や権限設定が可能です

MySQLとPostgreSQLにおける基本的なユーザーアカウント管理について

Comments are closed.

Post Navigation