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)