勉強日記

チラ裏

OSS-DB試験対策 緑本 ch7 基本的な運用管理

www.shoeisha.co.jp


基本的な運用管理

データベースユーザの追加/削除/変更

ユーザの追加

postgres@e3b855e6f1da:~$ createuser -P user1
  • -P, --pwprompt: パスワード設定
Enter password for new role: 
Enter it again:
  • 【補】パスワード認証を試すにはpg_hba.confの設定が必要
  # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
  # "local" is for Unix domain socket connections only
- local   all             all                                     trust
+ # local   all             all                                     trust
+ local   all             all                                     password
  • pg_hba.confの変更反映はSIGHUP
postgres@e3b855e6f1da:~$ pg_ctl reload
pg_ctl reload
server signaled
  • ユーザ確認
postgres@e3b855e6f1da:~$ psql -U user1 -d postgres -c "\du"
Password for user user1: 
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     |                                                            | {}

ユーザの削除

  • 削除対象のユーザ指定
postgres@e3b855e6f1da:~$ dropuser user1
dropuser user1
  • 削除対象のユーザを指定しないとエラー
postgres@e3b855e6f1da:~$ dropuser
dropuser: error: missing required argument role name
Try "dropuser --help" for more information.
  • -i, --interactive なら省略可能
postgres@e3b855e6f1da:~$ dropuser --interactive
Enter name of role to drop: user1

Role "user1" will be permanently removed.
Are you sure? (y/n) y

ユーザの変更

postgres@e3b855e6f1da:~$ psql -c "\h alter user"
Command:     ALTER USER
Description: change a database role
Syntax:
ALTER USER role_specification [ WITH ] option [ ... ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

ALTER USER name RENAME TO new_name
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-alteruser.html
postgres@e3b855e6f1da:~$ createuser syaro
postgres@e3b855e6f1da:~$ psql -c "\du"
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}
  • シャロちゃんをスーパーユーザにする
postgres@e3b855e6f1da:~$ psql -c "ALTER USER syaro WITH SUPERUSER"
ALTER ROLE
postgres@e3b855e6f1da:~$ psql -c "\du"
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     | Superuser                                                  | {}
  • もどす
postgres@e3b855e6f1da:~$ psql -c "ALTER USER syaro WITH NOSUPERUSER"
ALTER ROLE
postgres@e3b855e6f1da:~$ psql -c "\du"
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}

VACUUM, ANALYZE

VACUUM

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the
old version of the row. This approach is necessary to gain the benefits of
multiversion concurrency control (MVCC, see Chapter 13)
  • MVCC: Multi-Version Concurrency Control (多版型同時実行制御)
  • PostgreSQLのMVCCは追記型
    • UPDATEやDELETE時に即座には行を削除しない
    • 不要領域が溜まっていく
      • ディスク領域圧迫
      • I/O増加による性能低下
    • テーブルだけでなくインデックスにも生じる
  • VACUUMによる不要領域回収が必要
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

URL: https://www.postgresql.org/docs/12/sql-vacuum.html
  • VACUUMとVACUUM FULL
    • VACUUM
      • 定期的に実行しよう
        • 特に更新が頻繁なテーブル
    • VACUUM FULL
      • より多くの領域を回収できるが…
      • より時間がかかる
      • 排他ロックがかかる
      • 余分のディスク領域を要する
        • テーブルをコピーしてから不要領域を物理削除するから
      • 操作が完了するまで領域は回収されない
  • VACUUMしてみる
  • 作りたてのテーブルに対して
postgres=# CREATE TABLE tab1 (hoge integer);
CREATE TABLE

postgres=# \dt

        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tab1 | table | postgres
(1 row)

postgres=# VACUUM VERBOSE tab1;

INFO:  vacuuming "public.tab1"
INFO:  "tab1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 503
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
  • VERBOSEの情報はstderrに出る
  • dead tupleを3つ用意してみる
postgres=# INSERT INTO tab1 VALUES (1);
INSERT 0 1
postgres=# INSERT INTO tab1 VALUES (2);
INSERT 0 1
postgres=# INSERT INTO tab1 VALUES (3);
INSERT 0 1

postgres=# DELETE FROM tab1;
DELETE 3


postgres=# VACUUM VERBOSE tab1;

INFO:  vacuuming "public.tab1"
INFO:  "tab1": removed 3 row versions in 1 pages
INFO:  "tab1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 507
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "tab1": truncated 1 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
  • これ
...
INFO:  "tab1": removed 3 row versions in 1 pages
INFO:  "tab1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
...
  • 3行がremoveされた

ANALYZE

  • 統計情報
    • 実行計画を決めるやつ
  • 大量の挿入・更新等が行われたらANALYZEで統計情報更新
Command:     ANALYZE
Description: collect statistics about a database
Syntax:
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]

where option can be one of:

    VERBOSE [ boolean ]
    SKIP_LOCKED [ boolean ]

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

URL: https://www.postgresql.org/docs/12/sql-analyze.html
  • 公式/ANALYZE
  • 【補】統計情報はpg_statisticシステムカタログに格納される
postgres=# ANALYZE VERBOSE tab1;

INFO:  analyzing "public.tab1"
INFO:  "tab1": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
ANALYZE
  • 処理時間

For large tables, ANALYZE takes a random sample of the table contents,
rather than examining every row.
This allows even very large tables to be analyzed in a small amount of time.

  • 巨大なテーブルでも各行舐めるのではなくランダムサンプリングするので高速

VACUUM ANALYZE

  • VACUUMとANALYZEをまとめて実行

自動バキューム

  • postgresql.confに設定項目あり
#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------

# - Query and Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_io_timing = off

...
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

#autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.

システム情報取得関数

version()関数

postgres@e3b855e6f1da:~$ psql
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# SELECT version();
SELECT version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
  • psql起動時のバージョンはクライアントのバージョン
  • version()関数で表示されるのはサーバーのバージョン

current_database()関数

postgres=# SELECT current_database();

 current_database 
------------------
 postgres
(1 row)
  • 現在接続中のデータベース
    • psqlのプロンプトのpostgres=#=#の前のやつ

current_userとuser

postgres=# SELECT current_user;

 current_user 
--------------
 postgres
(1 row)

postgres=# SELECT user;

   user   
----------
 postgres
(1 row)
  • SQL規格上、カッコが付かないことになっている
postgres=# SELECT current_user();

ERROR:  syntax error at or near "("
LINE 1: SELECT current_user();
                           ^
postgres=# SELECT user();

ERROR:  syntax error at or near "("
LINE 1: SELECT user();
                   ^

情報スキーマ、システムカタログ(pg_roles, pg_authidなど)

情報スキーマ -- information_schema

postgres=# SELECT * FROM information_schema.enabled_roles;

         role_name         
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
 syaro
(10 rows)
  • 現在有効なユーザ、ならびにそのユーザが継承しているメンバシップ(role)が再帰的に出力される
  • 全テーブル
    • publicスキーマ
      • デフォルトのスキーマ
      • 普段遣いのテーブルが入ってるやつ
    • information_schema
    • pg_catalog
      • システムカタログ
      • 【補】\dSメタコマンドで出てくるやつ
SELECT * FROM information_schema.tables;
 table_catalog |    table_schema    |              table_name               | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------------+---------------------------------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
 postgres      | public             | tab1                                  | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_statistic                          | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_type                               | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_foreign_server                     | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_authid                             | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_shadow                             | VIEW       |                              |                      |                           |                          |                        | NO                 | NO       | 
 postgres      | pg_catalog         | pg_statistic_ext_data                 | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_roles                              | VIEW       |                              |                      |                           |                          |                        | NO                 | NO       | 
 postgres      | pg_catalog         | pg_settings                           | VIEW       |                              |                      |                           |                          |                        | NO                 | NO       | 
...

システムカタログ、pg_rolesビュー、pg_authidカタログ

  • PostgreSQL\duメタコマンドで出てくるような情報 + α
postgres@e3b855e6f1da:~$ psql -c "SELECT * FROM pg_roles WHERE rolcanlogin IS TRUE" -P pager=off
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 postgres | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 syaro    | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16394
(2 rows)
  • 比較
postgres@e3b855e6f1da:~$ psql -c "\du"
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}
  • pg_rolesではpasswordがrolpassword列に*で隠れて表示されたりする
  • cf. pg_authidではMD5ハッシュ値が出てくる
postgres@e3b855e6f1da:~$  psql -c "SELECT * FROM pg_authid WHERE rolcanlogin IS TRUE" -P pager=off
  oid  | rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |             rolpassword             | rolvaliduntil 
-------+----------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+---------------
    10 | postgres | t        | t          | t             | t           | t           | t              | t            |           -1 |                                     | 
 16394 | syaro    | f        | t          | f             | f           | t           | f              | f            |           -1 | md5cd2fae6681b2fee62e31bd949d21abcf | 
(2 rows)

その他のシステムカタログ

  • 公式/pg_settings
  • SHOW/SET文に代わるPostgreSQLの設定のインタフェース
    • 最大/最小値、設定可能タイミング等も確認できる
postgres=# \x

Expanded display is on.


postgres=# SELECT * FROM pg_settings;

-[ RECORD 1 ]---+-----------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------------
name            | allow_system_table_mods
setting         | off
unit            | 
category        | Developer Options
short_desc      | Allows modifications of the structure of system tables.
extra_desc      | 
context         | postmaster
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
...
  • 【補】設定可能タイミング(context)の確認とか
postgres=# SELECT name, context FROM pg_settings WHERE name = 'max_connections';

-[ RECORD 1 ]------------
name    | max_connections
context | postmaster

postgres=#  SELECT name, context FROM pg_settings WHERE name = 'max_wal_size';

-[ RECORD 1 ]---------
name    | max_wal_size
context | sighup
  • 【補】enumの設定可能値の確認とか
postgres=# SELECT name, context, enumvals FROM pg_settings WHERE name = 'archive_mode';

-[ RECORD 1 ]-------------
name     | archive_mode
context  | postmaster
enumvals | {always,on,off}
  • pg_databaseシステムカタログ
    • psql -lコマンドや\lメタコマンドで表示されるデータベース一覧を調べられる
    • これはデータベースごとではなく、データベースクラスタに対して1つ存在する
postgres=# SELECT * FROM pg_database;

-[ RECORD 1 ]-+------------------------------------
oid           | 13408
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_US.utf8
datctype      | en_US.utf8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 13407
datfrozenxid  | 480
datminmxid    | 1
dattablespace | 1663
datacl        | 
-[ RECORD 2 ]-+------------------------------------
oid           | 1
datname       | template1
  • pg_tables

postgres=# SELECT * FROM pg_tables WHERE schemaname = 'public';

-[ RECORD 1 ]---------
schemaname  | public
tablename   | tab1
tableowner  | postgres
tablespace  | 
hasindexes  | f
hasrules    | f
hastriggers | f
rowsecurity | f
  • 【補】information_schema.tablesとの比較
postgres=# SELECT * FROM information_schema.tables WHERE table_schema = 'public';

-[ RECORD 1 ]----------------+-----------
table_catalog                | postgres
table_schema                 | public
table_name                   | tab1
table_type                   | BASE TABLE
self_referencing_column_name | 
reference_generation         | 
user_defined_type_catalog    | 
user_defined_type_schema     | 
user_defined_type_name       | 
is_insertable_into           | YES
is_typed                     | NO
commit_action                | 
  • pg_tablesにはインデックス、トリガーの情報などがある
  • 【補】標準SQLにインデックスに関する既定はない

Compatibility

CREATE INDEX is a PostgreSQL language extension. There are no provisions for
indexes in the SQL standard.

テーブル単位の権限、GRANT文とREVOKE文

  • テーブル単位での権限設定

GRANT文

  • なんかいっぱい書式ある
postgres=# \h grant

Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
  • 文、テーブル、ロールに対して権限設定
  • 全〜系
    • ALL: 全権限
    • TO PUBLIC: 全ユーザ
    • ON ALL TABLES IN SCHEMA public: publicスキーマに属する全テーブル
  • ○○もできる系
    • SELECT権限: COPY TOも許可
    • INSERT権限: COPY FROMも許可
  • WITH GRANT OPTIONを指定すると、他の人に権限を付与できるようになる
  • SELECTの列単位で権限付与可能
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
  • 権限付与してみる
    • 作りたてのシャロちゃんはINSERT権限なし
postgres@e3b855e6f1da:~$ psql -U syaro -d postgres

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=> INSERT INTO tab1 VALUES (1);

ERROR:  permission denied for table tab1
  • INSERT権限を与える
postgres@e3b855e6f1da:~$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# GRANT INSERT ON tab1 TO syaro;

GRANT
  • 再度INSERTを試してみる
postgres@e3b855e6f1da:~$ psql -U syaro -d postgres

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=> INSERT INTO tab1 VALUES (1);

INSERT 0 1
  • 通った

REVOKE文

  • GRANTの逆のやつ
postgres=# REVOKE ALL ON ALL TABLES IN SCHEMA public FROM syaro;

REVOKE
  • INSERT試してみる
postgres@e3b855e6f1da:~$ psql -U syaro -d postgres

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=> INSERT INTO tab1 VALUES (2);

ERROR:  permission denied for table tab1
  • 無事権限剥奪できた

\dpまたは\z -- テーブルの権限の確認

  • 権限付与前
postgres=# \dp

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | tab1 | table | postgres=arwdDxt/postgres |                   | 
(1 row)
  • 全権付与後
postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO syaro;

GRANT

postgres=# \dp

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | tab1 | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | syaro=arwdDxt/postgres    |                   | 
(1 row)
  • 部分的に権限を取り上げる
postgres=# REVOKE INSERT,UPDATE,DELETE,TRUNCATE ON tab1 FROM syaro;

REVOKE

postgres=# \dp

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | tab1 | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | syaro=rxt/postgres        |                   | 
(1 row)
  • syaro=rxt/postgresの意味
    • postgressyaroに権限rxtを付与した
  • 権限の見方
文字 意味 権限
a append INSERT
r read SELECT
w write UPDATE
d delete DELETE
D TRUNCATE
x REFERENCES
t TRIGGER