PostgreSQLでデフォルトの権限を設定する
コマンドはALTER DEFAULT PRIVILEGES
標準SQLにはないコマンドです。
USERに設定してみる
今回はDockerコンテナで環境を準備しました。
docker run -d \ --name postgres10\ -e POSTGRES_USER=postgres\ -e POSTGRES_PASSWORD=postgres\ postgres:10.4
コンテナ内からpsqlで接続します。
$ docker exec -ti postgres10 /bin/bash $ su - postgres $ psql -U postgres
新しいROLEを作成
postgres=# select session_user; session_user -------------- postgres postgres=# CREATE ROLE testrole WITH NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN;CREATE ROLE postgres=# CREATE TABLE test1 (id int); CREATE TABLE postgres=# \z Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------+-------+-------------------+-------------------+---------- public | test1 | table | | |
SELECTできるか確認します。
$ psql -U testrole -d postgres
postgres=> select session_user; session_user -------------- testrole postgres=> select * from test1; ERROR: permission denied for relation test1
権限が無いのでできません。
新しい権限を設定
今度はDEFAULT権限を変更したあとでTABLEを作ります。
postgres=# select session_user; session_user -------------- postgres postgres=# ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO testrole; ALTER DEFAULT PRIVILEGES postgres=# CREATE TABLE test2 (id int); CREATE TABLE postgres=# \z Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------+-------+---------------------------+-------------------+---------- public | test1 | table | | | public | test2 | table | postgres=arwdDxt/postgres+| | | | | testrole=r/postgres | | (2 rows)
postgres=> select session_user; session_user -------------- testrole postgres=> select * from test2; id ---- (0 rows) postgres=> select * from test1; ERROR: permission denied for relation test1
権限を設定したあとに作ったtest2テーブルは参照できました。
一方で、先に作ったtest1は参照できないままです。
設定した権限を削除
設定を削除してみます。
postgres=# select session_user; session_user -------------- postgres postgres=# ALTER DEFAULT PRIVILEGES REVOKE SELECT ON TABLES FROM testrole; ALTER DEFAULT PRIVILEGES postgres=# CREATE TABLE test3 (id int); CREATE TABLE postgres=# \z Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------+-------+---------------------------+-------------------+---------- public | test1 | table | | | public | test2 | table | postgres=arwdDxt/postgres+| | | | | testrole=r/postgres | | public | test3 | table | | | (3 rows)
postgres=> select session_user; session_user -------------- testrole postgres=> select * from test3; ERROR: permission denied for relation test3 postgres=> select * from test2; id ---- (0 rows)
設定を削除したあとのtest3テーブルは参照できませんが、
その前に作っていたtest2テーブルへの参照権限は残されたままです。
公式ドキュメントにあるとおり、すでに設定されている権限には影響しないので、DB構築初期に実施するのがよさそうです。
