RDS PostgreSQL に拡張機能導入 (pgaudit, pg_repack, PostGIS, pgBadger, pg_config)

2021-06-02 Last updated : 2021-06-11 hayashier

pgaudit 有効化

$ psql -U hayashier -h postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com -d test
Password for user hayashier: 
psql (9.6.8, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type help for help.

test=> CREATE ROLE rds_pgaudit;
CREATE ROLE

パラメータグループで、pgaudit.roleの値をrds_pgaudit,shared_preload_librariesの値をpgauditに変更し再起動後、変更が適用される。

$ aws rds modify-db-parameter-group    --db-parameter-group-name psql9-6-parameter-groups    --parameters ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot    --parameters ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot    --region us-west-2;
{
    DBParameterGroupName: psql9-6-parameter-groups
}
$ aws rds reboot-db-instance --db-instance-identifier postgres-test --region us-west-2
{
    DBInstance: {
        PubliclyAccessible: false, 
        MasterUsername: hayashier, 
        MonitoringInterval: 60, 
        LicenseModel: postgresql-license, 
:
:

shared_preload_librariesにpgaudit, pgaudit.roleにrds_pgauditが設定されていることを確認。

test=> SHOW shared_preload_libraries;
 shared_preload_libraries 
--------------------------
 rdsutils,pgaudit
(1 row)

test=> SHOW pgaudit.role;
 pgaudit.role 
--------------
 rds_pgaudit
(1 row)

監査ログが出力されることのテスト

test=> CREATE TABLE t1 (id int);
CREATE TABLE
test=> GRANT SELECT ON t1 TO rds_pgaudit;
GRANT
test=> SELECT * FROM t1;
 id 
----
(0 rows)

error/postgresql.log.20YY-MM-dd-hh に監査ログが出力されていることを確認。

2018-07-29 10:49:29 UTC:172.31.19.138(54768):hayashier@test:[22378]:LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.t1,SELECT * FROM t1;,<not logged>

pg_repack 導入

pg_repackの導入

test=> CREATE EXTENSION pg_repack;
CREATE EXTENSION
test=> ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES
test=> ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
ALTER DEFAULT PRIVILEGES

pg_repackのクライアントをインストール。RDS PostgreSQL 9.6.6を使用し、こちらはpg_repack 1.4.2に対応しているので、こちらのバージョンをインストール (参照: https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.96x)

$ sudo yum install postgresql96-static -y
$ wget http://api.pgxn.org/dist/pg_repack/1.4.2/pg_repack-1.4.2.zip pg_repack-1.4.2.zip
$ unzip pg_repack-1.4.2.zip
$ cd pg_repack-1.4.2
$ make
$ sudo make install

pg_repackがインストールされたことの確認

$ ~/pg_repack-1.4.2/bin/pg_repack --version
pg_repack 1.4.2

test=&gt; select * from pg_extension;
      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
--------------------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql            |       10 |           11 | f              | 1.0        |           | 
 pg_stat_statements |       10 |         2200 | t              | 1.4        |           | 
 pgaudit            |       10 |         2200 | t              | 1.0        |           | 
 pg_repack          |       10 |         2200 | f              | 1.4.2      |           | 
(4 rows)

pg_repackを実行

$ ~/pg_repack-1.4.2/bin/pg_repack --no-superuser-check --table=testtbl --dbname=test --host=postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com --port=5432 --username=hayashier --password --elevel=DEBUG 
DEBUG: No workers to disconnect.
Password: 
Password: 
INFO: repacking table &quot;testtbl&quot;
DEBUG: ---- repack_one_table ----
DEBUG: target_name       : testtbl
:
:

PostGIS 導入

ユーザー作成と権限の付与

$ psql -U hayashier -h postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com -d test
Password for user hayashier: 
psql (9.6.8, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type &quot;help&quot; for help.

test=&gt; CREATE USER u1 WITH PASSWORD &#039;password&#039;;
CREATE ROLE
test=&gt; ALTER ROLE u1 CREATEDB;
ALTER ROLE
test=&gt; GRANT RDS_SUPERUSER TO u1;
GRANT ROLE
test=&gt; CREATE DATABASE u1db1;
CREATE DATABASE
test=&gt; \c u1db1
psql (9.6.8, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database &quot;u1db1&quot; as user &quot;hayashier&quot;.
u1db1=&gt; \du
                                                   List of roles
    Role name    |                         Attributes                         |              Member of              
-----------------+------------------------------------------------------------+-------------------------------------
 hayashier        | Create role, Create DB                                    +| {rds_superuser}
                 | Password valid until infinity                              | 
 rds_replication | Cannot login                                               | {}
 rds_superuser   | Cannot login                                               | {rds_replication,pg_signal_backend}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                 | Password valid until infinity                              | 
 rdsrepladmin    | No inheritance, Replication                               +| {}
                 | Password valid until infinity                              | 
 u1              | Create DB                                                  | {rds_superuser}

PostGISのインストール

u1db1=&gt; CREATE EXTENSION postgis;
CREATE EXTENSION
u1db1=&gt; CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
u1db1=&gt; CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
u1db1=&gt; CREATE EXTENSION postgis_topology;
CREATE EXTENSION
u1db1=&gt; ALTER SCHEMA tiger owner TO rds_superuser;
ALTER SCHEMA
u1db1=&gt; ALTER SCHEMA tiger_data owner TO rds_superuser;
ALTER SCHEMA
u1db1=&gt; ALTER SCHEMA topology owner TO rds_superuser;
ALTER SCHEMA
u1db1=&gt; CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
u1db1=&gt; SELECT exec(&#039;ALTER TABLE &#039; || quote_ident(s.nspname) || &#039;.&#039; || quote_ident(s.relname) || &#039; OWNER TO rds_superuser;&#039;)
u1db1-&gt;   FROM (
u1db1(&gt;     SELECT nspname, relname
u1db1(&gt;     FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
u1db1(&gt;     WHERE nspname in (&#039;tiger&#039;,&#039;topology&#039;) AND
u1db1(&gt;     relkind IN (&#039;r&#039;,&#039;S&#039;,&#039;v&#039;) ORDER BY relkind = &#039;S&#039;)
u1db1-&gt; s;
                                exec                                
--------------------------------------------------------------------
 ALTER TABLE tiger.loader_variables OWNER TO rds_superuser;
 ALTER TABLE tiger.loader_lookuptables OWNER TO rds_superuser;
 ALTER TABLE tiger.street_type_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.tract OWNER TO rds_superuser;
 ALTER TABLE tiger.place_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.tabblock OWNER TO rds_superuser;
 ALTER TABLE tiger.county_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.bg OWNER TO rds_superuser;
 ALTER TABLE tiger.countysub_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_gaz OWNER TO rds_superuser;
 ALTER TABLE tiger.featnames OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_lex OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_lookup_all OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_rules OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_lookup_base OWNER TO rds_superuser;
 ALTER TABLE topology.topology OWNER TO rds_superuser;
 ALTER TABLE topology.layer OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.addr OWNER TO rds_superuser;
 ALTER TABLE tiger.county OWNER TO rds_superuser;
 ALTER TABLE tiger.faces OWNER TO rds_superuser;
 ALTER TABLE tiger.state OWNER TO rds_superuser;
 ALTER TABLE tiger.geocode_settings OWNER TO rds_superuser;
 ALTER TABLE tiger.place OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_state OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_state_loc OWNER TO rds_superuser;
 ALTER TABLE tiger.geocode_settings_default OWNER TO rds_superuser;
 ALTER TABLE tiger.cousub OWNER TO rds_superuser;
 ALTER TABLE tiger.direction_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.edges OWNER TO rds_superuser;
 ALTER TABLE tiger.secondary_unit_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.addrfeat OWNER TO rds_superuser;
 ALTER TABLE tiger.state_lookup OWNER TO rds_superuser;
 ALTER TABLE tiger.zcta5 OWNER TO rds_superuser;
 ALTER TABLE tiger.loader_platform OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_lex_id_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.featnames_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.addr_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.zcta5_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.county_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.state_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.place_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.cousub_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.edges_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.addrfeat_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.faces_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.tract_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.tabblock_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.bg_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_gaz_id_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_rules_id_seq OWNER TO rds_superuser;
 ALTER TABLE topology.topology_id_seq OWNER TO rds_superuser;
(52 rows)

u1db1=&gt; SET search_path=public,tiger;
SET
u1db1=&gt; \dn
      List of schemas
    Name    |     Owner     
------------+---------------
 public     | hayashier
 tiger      | rds_superuser
 tiger_data | rds_superuser
 topology   | rds_superuser
(4 rows)

インストールされたことの確認

u1db1=&gt; select postgis_version();
            postgis_version            
---------------------------------------
 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

動作確認

u1db1=&gt; SELECT na.address, na.streetname, na.streettypeabbrev, na.zip FROM normalize_address(&#039;1 Devonshire Place, Boston, MA 02109&#039;) AS na;
 address | streetname | streettypeabbrev |  zip  
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row)

u1db1=&gt; SELECT topology.createtopology(&#039;my_new_topo&#039;,26986,0.5);
 createtopology 
----------------
              1
(1 row)

u1db1=&gt; SELECT box2d(ST_GeomFromText(&#039;LINESTRING(1 2,3 4, 5 6)&#039;));
    box2d     
--------------
 BOX(1 2,5 6)
(1 row)

pgBadger

ログをパースした結果が分かりやすくビジュアライズされる。 ツールをダウンロール。ツールは、pgbadgerというPerlのスクリプト。 バージョンはこちらで確認 https://github.com/dalibo/pgbadger/releases

$ wget https://github.com/dalibo/pgbadger/archive/v9.2.zip
$ unzip v9.2.zip 
$ cd pgbadger-9.2/
$ chmod +x pgbadger

pgBadgerの実行。実行後、out.htmlという内容がBase64されたHTMLファイルが生成される。

$ ./pgbadger -p &#039;%t:%r:%u@%d:[%p]:&#039; postgresql.log.2018-07-29-10
[========================&gt;] Parsed 7518 bytes of 7518 (100.00%), queries: 0, events: 8
LOG: Ok, generating html report...

pg_config の内容確認

test=&gt; select * from pg_config();
       name        |                                                                                                                     setting                                                                    

-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
 BINDIR            | /rdsdbbin/postgres-9.6.6.R1/bin
 DOCDIR            | /rdsdbbin/postgres-9.6.6.R1/share/doc
 HTMLDIR           | /rdsdbbin/postgres-9.6.6.R1/share/doc
 INCLUDEDIR        | /rdsdbbin/postgres-9.6.6.R1/include
 PKGINCLUDEDIR     | /rdsdbbin/postgres-9.6.6.R1/include
 INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.6.R1/include/server
 LIBDIR            | /rdsdbbin/postgres-9.6.6.R1/lib
 PKGLIBDIR         | /rdsdbbin/postgres-9.6.6.R1/lib
 LOCALEDIR         | /rdsdbbin/postgres-9.6.6.R1/share/locale
 MANDIR            | /rdsdbbin/postgres-9.6.6.R1/share/man
 SHAREDIR          | /rdsdbbin/postgres-9.6.6.R1/share
 SYSCONFDIR        | /rdsdbbin/postgres-9.6.6.R1/etc
 PGXS              | /rdsdbbin/postgres-9.6.6.R1/lib/pgxs/src/makefiles/pgxs.mk
 CONFIGURE         | &#039;--prefix=/rdsdbbin/postgres-9.6.6.R1&#039; &#039;--with-openssl&#039; &#039;--with-perl&#039; &#039;--with-tcl&#039; &#039;--with-ossp-uuid&#039; &#039;--with-libxml&#039; &#039;--with-libraries=/rdsdbbin/postgres-9.6.6.R1/lib&#039; &#039;--with-includes=/rdsd
bbin/postgres-9.6.6.R1/include&#039; &#039;--enable-debug&#039;
 CC                | gcc
 CPPFLAGS          | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.6.R1/include
 CFLAGS            | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -
g -O2
 CFLAGS_SL         | -fPIC
 LDFLAGS           | -L../../src/common -L/rdsdbbin/postgres-9.6.6.R1/lib -Wl,--as-needed -Wl,-rpath,&#039;/rdsdbbin/postgres-9.6.6.R1/lib&#039;,--enable-new-dtags
 LDFLAGS_EX        | 
 LDFLAGS_SL        | 
 LIBS              | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm  
 VERSION           | PostgreSQL 9.6.6
(23 rows)

REFERENCE

  • PostgreSQL の一般的な DBA タスク – Amazon Relational Database Service
    • URL: https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

My Twitter & RSS

Categories
AWS, Cloud, General, PostgreSQL, RDBMS, and Setup

Leave a Reply

Your email address will not be published. Required fields are marked *

Previous article
ランダムなサンプルデータ生成 (MySQL, PostgreSQL, Oracle, SQL Server)
2021-06-02
Next article
PostgreSQL 運用管理、チューニング まとめ
2021-06-02