pgAdmin 등의 PostgreSQL Client 프로그램에서 Meta-Commands 명령어 대응 쿼리 수행

이 페이지에서는 PostgreSQL DB 와 연동되어 사용되는 PostgreSQL Client 프로그램에서

Meta-Commands 명령어에 대응되는 쿼리를 수행하는 방법에 대하여 다룬다.

 

해당 내용은 아래의 공식 문서 및 외부 자료를 참조하여 기재한다

 

- 공식 문서

 

[1] https://www.postgresql.org/docs/current/app-psql.html

 

- 외부 자료

 

[2] https://dzone.com/articles/revealing-the-queries-behind-psqls-backslash-comma

 

 

Psql 에서 Meta-Commands 명령어 수행

 

PostgreSQL 에서는 일반적인 쿼리 이외에 \ 명령어로 간편하게 특정 오브젝트 혹은 유저등의 설정정보 (metadata) 를 볼 수 있다. 이를 PostgreSQL 에서 Meta-Commands 라 명명하며 이는 일반적으로 Linux 등의 host 에서 직접 접속하여 사용하는 client tool 인 psql 에서 사용가능하며 이에 대한 예시는 아래와 같다.

 

--현재 DB 내에 구성된 Database list 확인

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +
           |          |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres

--현재 DB 내에 구성된 User list 확인

\du+

postgres=> \du+
                                                                      List of roles
    Role name    |                         Attributes                         |                          Member of                          | Description
-----------------+------------------------------------------------------------+-------------------------------------------------------------+-------------
 postgres        | Create role, Create DB                                    +| {rds_superuser}                                             |
                 | Password valid until infinity                              |                                                             |
 rds_ad          | Cannot login                                               | {}                                                          |
 rds_iam         | Cannot login                                               | {}                                                          |
 rds_password    | Cannot login                                               | {}                                                          |
 rds_replication | Cannot login                                               | {}                                                          |
 rds_superuser   | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_password,rds_replication} |
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}                                                          |
                 | Password valid until infinity                              |                                                             |
 test            |                                                            | {}                                                          |
 users           |                                                            | {}                                                          |

 

이와 같이 psql 에서는 특정 쿼리를 사용하지 않더라도 \ 명령어를 통하여 여러 정보들을 확인 할 수 있다.

 

 

Psql 이외의 환경에서 Meta-Commands 명령어 수행의 제한사항과 이에 대한 대응방법

 

그러나 위의 명령어는 psql 에서만 지원하는 명령어기 때문에 pgAdmin 등의 DB 가 구성된 host 이외의 환경에서 DB 를 사용하기 위해 이용하는 client 프로그램에서는 이를 지원하지 않는다.

 

물론, 사용자가 host 에 직접 들어가서 meta-commands 를 사용 할 수 있다. 그러나 회사마다 보안 또는 직무적인 영역등의 사유로 DB host 의 직접 접속을 막을 수 있고, 이럴때는 meta-commands 를 사용 할 수 없다.

 

이럴때는 [2] 의 내용과 같이 meta-commands 가 내부적으로 어떤 쿼리를 수행하는지 확인 후 이를 역으로 client 환경에서 사용하는 방법이 있다. 이를 위해서는 아래 예시와 같이 psql 을 사용시 -E 옵션을 넣으면 된다.

(이에 대한 자세한 내용은 [1] 을 참조)

 

$ psql -h test-1 -U postgres -W -E
Password:
psql (14.2, server 13.7)
SSL connection (protocol: TLSv1.2, cipher: AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

postgres=> \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +
           |          |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
(4 rows)

-- -E 옵션을 통해 \l 명령어를 통해 수행되는 쿼리를 확인 후 이를 pgAdmin 에 적용

-- 위의 쿼리를 psql 에서 동일하게 수행 시

postgres=> SELECT d.datname as "Name",
postgres->        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
postgres->        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
postgres->        d.datcollate as "Collate",
postgres->        d.datctype as "Ctype",
postgres->        pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
postgres-> FROM pg_catalog.pg_database d
postgres-> ORDER BY 1;
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +
           |          |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
(4 rows)