Data Engineering/Data Infra & Process

[3ํŽธ] PostgreSQL ์‚ฌ์šฉ์ž(Role) ์ถ”๊ฐ€ ๋ฐ ์ˆ˜๋™ ๊ด€๋ฆฌ (SQL ์ฟผ๋ฆฌ ๋ฐฉ์‹)

ygtoken 2025. 3. 7. 14:52
728x90

 

๐Ÿ“Œ ๊ฐœ์š”

 

์ด ๊ธ€์—์„œ๋Š” Helm๊ณผ ArgoCD๋กœ PostgreSQL์„ ๋ฐฐํฌํ•œ ํ›„, SQL์„ ์ง์ ‘ ์‹คํ–‰ํ•˜์—ฌ ํŠน์ • ์‚ฌ์šฉ์ž(Role) ๋ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ•˜๊ณ  ๊ด€๋ฆฌํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์„ค๋ช…ํ•ฉ๋‹ˆ๋‹ค.

Helm์˜ values.yaml์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๊ณ , postgres ๊ณ„์ •์œผ๋กœ ์ง์ ‘ psql์„ ์‹คํ–‰ํ•˜์—ฌ ์‚ฌ์šฉ์ž ๋ฐ ๊ถŒํ•œ์„ ์„ค์ •ํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ๋‹ค๋ฃน๋‹ˆ๋‹ค.

 


๐Ÿš€ 1. PostgreSQL ์ ‘์† ๋ฐ ๊ธฐ๋ณธ ์„ค์ • ํ™•์ธ

 

1๏ธโƒฃ PostgreSQL Pod์— ์ ‘์†

 

๋จผ์ € PostgreSQL Pod ๋‚ด๋ถ€์—์„œ psql์„ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.

kubectl exec -it $(kubectl get pod -n database -l app.kubernetes.io/name=postgresql -o jsonpath="{.items[0].metadata.name}") -n database -- psql -U postgres

 

โœ… ์ •์ƒ์ ์œผ๋กœ ์ ‘์†๋˜๋ฉด ํ”„๋กฌํ”„ํŠธ๊ฐ€ ๋‚˜ํƒ€๋‚ฉ๋‹ˆ๋‹ค.

psql (14.2)
Type "help" for help.

postgres=#

 

 


2๏ธโƒฃ PostgreSQL์˜ ๊ธฐ๋ณธ ์„ค์ • ํ™•์ธ

 

๐Ÿ“Œ ํ˜„์žฌ ์กด์žฌํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋ชฉ๋ก ํ™•์ธ

\l

 

โœ… ์ถœ๋ ฅ ์˜ˆ์‹œ

      Name    | Owner     | Encoding | Collate | Ctype | Access privileges
-------------+-----------+----------+---------+-------+-------------------
 postgres    | postgres  | UTF8     | C       | C.UTF-8 |

 

๐Ÿ“Œ ํ˜„์žฌ ์กด์žฌํ•˜๋Š” ์‚ฌ์šฉ์ž(Role) ๋ชฉ๋ก ํ™•์ธ

\du

 

โœ… ์ถœ๋ ฅ ์˜ˆ์‹œ

 Role name | Attributes  | Member of
-----------+------------+-----------
 postgres  | Superuser  | {}

ํ˜„์žฌ postgres ๊ณ„์ •๋งŒ ์กด์žฌํ•˜๋Š” ์ƒํƒœ์ž…๋‹ˆ๋‹ค.

 


๐Ÿš€ 2. ์‚ฌ์šฉ์ž(Role) ๋ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ถ”๊ฐ€

 

์ด์ œ adminuser ์‚ฌ์šฉ์ž์™€ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ง์ ‘ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.

-- ์ƒˆ๋กœ์šด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ
CREATE DATABASE ragdb;

-- ์ƒˆ๋กœ์šด ์‚ฌ์šฉ์ž ์ƒ์„ฑ ๋ฐ ๋น„๋ฐ€๋ฒˆํ˜ธ ์„ค์ •
CREATE ROLE adminuser WITH LOGIN PASSWORD 'admin1234';

-- adminuser๊ฐ€ ragdb์˜ ์†Œ์œ ์ž๊ฐ€ ๋˜๋„๋ก ์„ค์ •
ALTER DATABASE ragdb OWNER TO adminuser;

-- adminuser์—๊ฒŒ ๋ชจ๋“  ๊ถŒํ•œ ๋ถ€์—ฌ
GRANT ALL PRIVILEGES ON DATABASE ragdb TO adminuser;
ALTER ROLE adminuser CREATEDB;
ALTER ROLE adminuser SUPERUSER;

 

โœ… ์„ค๋ช…

CREATE DATABASE ragdb;ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ

CREATE ROLE adminuser WITH LOGIN PASSWORD 'admin1234';adminuser ์‚ฌ์šฉ์ž ์ƒ์„ฑ

ALTER DATABASE ragdb OWNER TO adminuser;adminuser๋ฅผ ragdb์˜ ์†Œ์œ ์ž๋กœ ์„ค์ •

GRANT ALL PRIVILEGES ON DATABASE ragdb TO adminuser;adminuser์—๊ฒŒ ragdb์˜ ๋ชจ๋“  ๊ถŒํ•œ ๋ถ€์—ฌ

ALTER ROLE adminuser CREATEDB;adminuser๊ฐ€ ์ƒˆ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ•  ์ˆ˜ ์žˆ๋„๋ก ์„ค์ •

ALTER ROLE adminuser SUPERUSER;adminuser์—๊ฒŒ SUPERUSER ๊ถŒํ•œ ๋ถ€์—ฌ

 

โœ… ์ ์šฉ๋œ ์„ค์ • ํ™•์ธ

\du

 

โœ… ์ถœ๋ ฅ ์˜ˆ์‹œ

 Role name | Attributes  | Member of
-----------+------------+-----------
 adminuser | Superuser, Create DB | {}
 postgres  | Superuser  | {}
\l

 

โœ… ์ถœ๋ ฅ ์˜ˆ์‹œ

      Name    | Owner     | Encoding | Collate | Ctype | Access privileges
-------------+-----------+----------+---------+-------+-------------------
 postgres    | postgres  | UTF8     | C       | C.UTF-8 |
 ragdb       | adminuser | UTF8     | C       | C.UTF-8 | =Tc/adminuser + postgres=CTc/adminuser

 

 


๐Ÿš€ 3. adminuser ๊ณ„์ •์œผ๋กœ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ ‘์†

 

์ด์ œ adminuser ๊ณ„์ •์œผ๋กœ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ ‘์†ํ•˜์—ฌ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.

 

1๏ธโƒฃ adminuser ๊ณ„์ •์œผ๋กœ PostgreSQL ์ ‘์†

kubectl exec -it $(kubectl get pod -n database -l app.kubernetes.io/name=postgresql -o jsonpath="{.items[0].metadata.name}") -n database -- psql -U adminuser -d ragdb

 

โœ… ์ •์ƒ์ ์œผ๋กœ ์ ‘์†๋˜๋ฉด ํ”„๋กฌํ”„ํŠธ๊ฐ€ ๋ณ€๊ฒฝ๋ฉ๋‹ˆ๋‹ค.

ragdb=>

 

2๏ธโƒฃ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ํ…Œ์ด๋ธ” ํ™•์ธ

\dt

 

โœ… ์•„์ง ํ…Œ์ด๋ธ”์ด ์—†๊ธฐ ๋•Œ๋ฌธ์— ์•„๋ž˜์™€ ๊ฐ™์€ ๋ฉ”์‹œ์ง€๊ฐ€ ์ถœ๋ ฅ๋ฉ๋‹ˆ๋‹ค.

No relations found.

 

3๏ธโƒฃ ํ…Œ์ŠคํŠธ์šฉ ํ…Œ์ด๋ธ” ์ƒ์„ฑ

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

 

โœ… ํ…Œ์ด๋ธ”์ด ์ƒ์„ฑ๋˜์—ˆ๋Š”์ง€ ํ™•์ธ

\dt
          List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+----------
 public | users | table | adminuser

 

๐Ÿ“Œ ํ…Œ์ŠคํŠธ ๋ฐ์ดํ„ฐ ์‚ฝ์ž…

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

 

๐Ÿ“Œ ๋ฐ์ดํ„ฐ ์กฐํšŒ

SELECT * FROM users;

 

โœ… ์ถœ๋ ฅ ์˜ˆ์‹œ

 id |  name  |       email
----+--------+-------------------
  1 | Alice  | alice@example.com
(1 row)

 

๐Ÿ“Œ ์—ฐ๊ฒฐ ์ข…๋ฃŒ

\q

 

 


๐Ÿš€ 4. DBeaver์—์„œ adminuser ๊ณ„์ •์œผ๋กœ ragdb ์—ฐ๊ฒฐ

 

์ด์ œ DBeaver์—์„œ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— adminuser ๊ณ„์ •์œผ๋กœ ์ ‘์†ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

โœ… DBeaver ์„ค์ • ๋ณ€๊ฒฝ

 

1๏ธโƒฃ DBeaver์—์„œ ๊ธฐ์กด PostgreSQL ์—ฐ๊ฒฐ์„ ๋ณต์‚ฌํ•˜๊ฑฐ๋‚˜ ์ƒˆ๋กœ์šด ์—ฐ๊ฒฐ ์ƒ์„ฑ

 

2๏ธโƒฃ ์•„๋ž˜ ์ •๋ณด ์ž…๋ ฅ

Host: localhost

Port: 5432

Database: ragdb

Username: adminuser

Password: admin1234

 

3๏ธโƒฃ “Test Connection” ๋ฒ„ํŠผ ํด๋ฆญํ•˜์—ฌ ์—ฐ๊ฒฐ ํ…Œ์ŠคํŠธ

 

4๏ธโƒฃ ์ •์ƒ์ ์œผ๋กœ ์—ฐ๊ฒฐ๋˜๋ฉด “Finish” ๋ฒ„ํŠผ ํด๋ฆญํ•˜์—ฌ ์ €์žฅ

 

โœ… ์ด์ œ DBeaver์—์„œ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ํƒ์ƒ‰ํ•˜๊ณ , ํ…Œ์ด๋ธ” ๋ฐ ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 


๐Ÿ“Œ 5. ์ตœ์ข… ์ •๋ฆฌ

 

โœ… postgres ๊ณ„์ •์œผ๋กœ ์ง์ ‘ ๋กœ๊ทธ์ธํ•˜์—ฌ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋ฐ adminuser ์‚ฌ์šฉ์ž ์ƒ์„ฑ

โœ… SQL์„ ์‹คํ–‰ํ•˜์—ฌ adminuser์—๊ฒŒ ragdb์— ๋Œ€ํ•œ ๋ชจ๋“  ๊ถŒํ•œ ๋ถ€์—ฌ

โœ… adminuser ๊ณ„์ •์œผ๋กœ ์ง์ ‘ ๋กœ๊ทธ์ธํ•˜์—ฌ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ณ  ๋ฐ์ดํ„ฐ ์‚ฝ์ž…

โœ… DBeaver์—์„œ adminuser ๊ณ„์ •์œผ๋กœ ragdb ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์—ฐ๊ฒฐ ๋ฐ ํ™•์ธ

 

728x90