基本的な運用管理

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

ユーザの追加

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

ALTER USERはALTER ROLEのエイリアス
- 【補】現在のPostgreSQLでは、USERもGROUPもROLEに統合されている
PostgreSQL拡張文
公式/ALTER ROLE
権限つけはずし

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

公式/Routine Vacuuming

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 (多版型同時実行制御)
- 公式/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.

公式/postgresql.confのautovacuum設定
公式/postgresql.confの統計情報設定
autovacuumをonにする場合、track_countsも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

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など)

データベースクラスタ全体にかかわる各種情報の大別
- 情報スキーマ
  - SQL標準規格
- システムカタログ
  - RDBMS固有

情報スキーマ -- information_schema

公式
SQL標準
- 可搬性がある
- 安定
PostgreSQL固有機能の情報は含まれない
例: enabled_roles
- 公式

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にインデックスに関する既定はない
- = RDBMS固有 = システムカタログ
- 公式/CREATE INDEX

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の意味
- postgresがsyaroに権限rxtを付与した
権限の見方

文字	意味	権限
a	append	INSERT
r	read	SELECT
w	write	UPDATE
d	delete	DELETE
D		TRUNCATE
x		REFERENCES
t		TRIGGER

勉強日記

チラ裏

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

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

ユーザの追加

ユーザの削除

ユーザの変更

VACUUM, ANALYZE

VACUUM

ANALYZE

VACUUM ANALYZE

自動バキューム

システム情報取得関数

version()関数

current_database()関数

current_userとuser

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

情報スキーマ -- information_schema

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

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

`pg_tables`

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

GRANT文

REVOKE文

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