POSTGRESQL Archives - icodebroker

[PYTHON/STREAMLIT] connection 함수 : 데이터베이스 연결 만들기

■ connection 함수를 사용해 POSTGRESQL 데이터베이스 연결을 만드는 방법을 보여준다. ▶ ./.streamlit/secrets.toml


[connections.posgreSQL]
    type="sql"
    dialect="postgresql"
    username="user1"
    password="password1"
    host="127.0.0.1"
    port=5432
    database="test"

[connections.posgreSQL]

type="sql"

dialect="postgresql"

username="user1"

password="password1"

host="127.0.0.1"

port=5432

database="test"

※ postgreSQL : 연결명 ※ postgresql : 데이터베이스 타입

[PYTHON/STREAMLIT] SqlConnection 클래스 : query 메소드를 사용해 데이터 조회하기

■ SqlConnection 클래스의 query 메소드를 사용해 데이터를 조회하는 방법을 보여준다. ▶ ./.streamlit/secrets.toml


[connections.posgreSQL]
    type="sql"
    dialect="postgresql"
    username="user1"
    password="password1"
    host="127.0.0.1"
    port=5432
    database="test"

[connections.posgreSQL]

type="sql"

dialect="postgresql"

username="user1"

password="password1"

host="127.0.0.1"

port=5432

database="test"

※ postgreSQL : 연결명 ※ postgresql : 데이터베이스 타입

[DATABASE/POSTGRESQL] DROP USER 명령 : 사용자 삭제하기

■ DROP USER 명령을 사용해 사용자를 삭제하는 방법을 보여준다. • 사용자를 삭제하려면 슈퍼유저 권한이 필요하다. • 삭제하려는 사용자가 소유한 데이터베이스나 테이블이 있는

[DATABASE/POSTGRESQL] DROP DATABASE 명령 : 데이터베이스 삭제하기

■ DROP DATABASE 명령을 사용해 데이터베이스를 삭제하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DROP DATABASE testdb;

DROP DATABASE testdb;

※ testdb : 데이터베이스명

[DATABASE/POSTGRESQL] 우분투에서 PSQL 접속하기

■ 우분투에서 PSQL에 접속하는 방법을 보여준다. 1. CTRL + ALT + T 키를 눌러서 [터미널]을 실행한다. 2. [터미널]에서 아래 명령을 실행한다. ▶

[PYTHON/PSYCOPG2] 날짜/시간 처리하기

■ 날짜/시간을 처리하는 방법을 보여준다. ▶ 테스트 테이블 (SQL)


CREATE TABLE item
(
    item_id       INTEGER NOT NULL PRIMARY KEY,
    item_name     VARCHAR (100) NOT NULL,
    purchase_time TIMESTAMP NOT NULL,
    price         INTEGER NOT NULL
);

CREATE TABLE item

(

item_id INTEGER NOT NULL PRIMARY KEY,

item_name VARCHAR (100) NOT NULL,

purchase_time TIMESTAMP NOT NULL,

price INTEGER NOT NULL

);

▶ 예제 코드 (PY)


import datetime
import psycopg2

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("DELETE FROM item;")

cursor.execute(
    "INSERT INTO item (item_id, item_name, purchase_time, price) VALUES (%s, %s, %s, %s)",
    (12, "Keyboard", datetime.datetime.now(), 150)
)

connection.commit()

cursor.execute("SELECT purchase_time from item where item_id = 12")

purchaseTime = cursor.fetchone()

print("항목 구매 일자 :", purchaseTime[0].date())
print("항목 구매 시간 :", purchaseTime[0].time())

cursor.close()
connection.close()

"""
항목 구매 일자 : 2024-06-01
항목 구매 시간 : 00:58:33.167324
"""

import datetime

import psycopg2

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("DELETE FROM item;")

cursor.execute(

"INSERT INTO item (item_id, item_name, purchase_time, price) VALUES (%s, %s, %s, %s)",

(12, "Keyboard", datetime.datetime.now(), 150)

)

connection.commit()

cursor.execute("SELECT purchase_time from item where item_id = 12")

purchaseTime = cursor.fetchone()

print("항목 구매 일자 :", purchaseTime[0].date())

print("항목 구매 시간 :", purchaseTime[0].time())

cursor.close()

connection.close()

"""

항목 구매 일자 : 2024-06-01

항목 구매 시간 : 00:58:33.167324

"""

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] Cursor 클래스 : rowcount 속성을 사용해 데이터 처리 건수 구하기

■ Cursor 클래스의 rowcount 속성을 사용해 데이터 처리 건수를 구하는 방법을 보여준다. ▶ 테스트 테이블 (SQL)


CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

▶ 예제 코드 (PY)


import psycopg2

from psycopg2 import Error

try:
    connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

    cursor = connection.cursor()

    cursor.execute("DELETE FROM test")

    print(f"삭제 : {cursor.rowcount}건")

    cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")

    print(f"추가 : {cursor.rowcount}건")

    cursor.execute("INSERT INTO test (id, name, age) VALUES (2, '김철수', 30)")

    print(f"추가 : {cursor.rowcount}건")

    cursor.execute("INSERT INTO test (id, name, age) VALUES (3, '이영희', 25)")

    print(f"추가 : {cursor.rowcount}건")

    connection.commit()

    print("트랜잭션이 완료되었습니다.")

except (Exception, Error) as error:
    connection.rollback()

    print("트랜잭션이 취소되었습니다.")
    print(f"PostgreSQL 연결시 에러가 발생했습니다 : {error}")

finally:
    if connection:
        cursor.close()
        connection.close()

        print("PostgreSQL 연결이 종료되었습니다.")

import psycopg2

from psycopg2 import Error

try:

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("DELETE FROM test")

print(f"삭제 : {cursor.rowcount}건")

cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")

print(f"추가 : {cursor.rowcount}건")

cursor.execute("INSERT INTO test (id, name, age) VALUES (2, '김철수', 30)")

print(f"추가 : {cursor.rowcount}건")

cursor.execute("INSERT INTO test (id, name, age) VALUES (3, '이영희', 25)")

print(f"추가 : {cursor.rowcount}건")

connection.commit()

print("트랜잭션이 완료되었습니다.")

except (Exception, Error) as error:

connection.rollback()

print("트랜잭션이 취소되었습니다.")

print(f"PostgreSQL 연결시 에러가 발생했습니다 : {error}")

finally:

if connection:

cursor.close()

connection.close()

print("PostgreSQL 연결이 종료되었습니다.")

[PYTHON/PSYCOPG2] PYTHON과 POSTGRESQL 데이터베이스 데이터 타입 관계

■ PYTHON과 POSTGRESQL 데이터베이스 데이터 타입 관계에 대해 보여준다. ▶ 표


==========================================
Python      PostgreSQL
==========  ==============================
None        NULL
bool        bool
float       real, double
int         smallint, integer, bigint
Decimal     numeric
str         varchar, text
date        date
time        time, timetz
datetime    timestampe, timestamptz
timedelta   interval
list        ARRAY
tuple       Compositie types, IN syntax
dict        hstore
==========================================

==========================================

Python PostgreSQL

========== ==============================

None NULL

bool bool

float real, double

int smallint, integer, bigint

Decimal numeric

str varchar, text

date date

time time, timetz

datetime timestampe, timestamptz

timedelta interval

list ARRAY

tuple Compositie types, IN syntax

dict hstore

==========================================

[PYTHON/PSYCOPG2] Connecton 클래스 : commit/rollback 메소드를 사용해 트랜잭션 처리하기

■ Connecton 클래스의 commit/rollback 메소드를 사용해 트랜잭션을 처리하는 방법을 보여준다. ▶ 테스트 테이블 (SQL)


CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

▶ 예제 코드 (PY)


import psycopg2

from psycopg2 import Error

try:
    connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

    cursor = connection.cursor()

    cursor.execute("DELETE FROM test")

    cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")
    cursor.execute("INSERT INTO test (id, name, age) VALUES (2, '김철수', 30)")
    cursor.execute("INSERT INTO test (id, name, age) VALUES (3, '이영희', 25)")

    connection.commit()

    print("트랜잭션이 완료되었습니다.")

except (Exception, Error) as error:
    connection.rollback()

    print("트랜잭션이 취소되었습니다.")
    print(f"PostgreSQL 연결시 에러가 발생했습니다 : {error}")

finally:
    if connection:
        cursor.close()
        connection.close()

        print("PostgreSQL 연결이 종료되었습니다.")

import psycopg2

from psycopg2 import Error

try:

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("DELETE FROM test")

cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")

cursor.execute("INSERT INTO test (id, name, age) VALUES (2, '김철수', 30)")

cursor.execute("INSERT INTO test (id, name, age) VALUES (3, '이영희', 25)")

connection.commit()

print("트랜잭션이 완료되었습니다.")

except (Exception, Error) as error:

connection.rollback()

print("트랜잭션이 취소되었습니다.")

print(f"PostgreSQL 연결시 에러가 발생했습니다 : {error}")

finally:

if connection:

cursor.close()

connection.close()

print("PostgreSQL 연결이 종료되었습니다.")

▶ requirements.txt

[PYTHON/PSYCOPG2] Cursor 클래스 : execute/fetchone 메소드를 사용해 데이터베이스 버전 구하기

■ Cursor 클래스의 execute/fetchone 메소드를 사용해 데이터베이스 버전을 구하는 방법을 보여준다. ▶ 예제 코드 (PY)


import psycopg2

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("SELECT version();")

versionString = cursor.fetchone()

print(f"PostgreSQL 버전 : {versionString}")

cursor.close()
connection.close()

"""
PostgreSQL 버전 : ('PostgreSQL 11.10, compiled by Visual C++ build 1914, 64-bit',)
"""

import psycopg2

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("SELECT version();")

versionString = cursor.fetchone()

print(f"PostgreSQL 버전 : {versionString}")

cursor.close()

connection.close()

"""

PostgreSQL 버전 : ('PostgreSQL 11.10, compiled by Visual C++ build 1914, 64-bit',)

"""

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] Connection 클래스 : get_dsn_parameters 메소드를 사용해 DSN 매개 변수 구하기

■ Connection 클래스의 get_dsn_parameters 메소드를 사용해 DSN 매개 변수를 구하는 방법을 보여준다. ▶ 예제 코드 (PY)


import psycopg2

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

print("=" * 100)
print(connection.get_dsn_parameters())
print("=" * 100)
print()

cursor.close()
connection.close()

"""
====================================================================================================
{'user': 'testuser1', 'channel_binding': 'prefer', 'dbname': 'testdb', 'host': '192.168.29.118', 'port': '5432', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'sslcertmode': 'allow', 'sslsni': '1', 'ssl_min_protocol_version': 'TLSv1.2', 'gssencmode': 'prefer', 'krbsrvname': 'postgres', 'gssdelegation': '0', 'target_session_attrs': 'any', 'load_balance_hosts': 'disable'}
====================================================================================================
"""

import psycopg2

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

print("=" * 100)

print(connection.get_dsn_parameters())

print("=" * 100)

print()

cursor.close()

connection.close()

"""

====================================================================================================

{'user': 'testuser1', 'channel_binding': 'prefer', 'dbname': 'testdb', 'host': '192.168.29.118', 'port': '5432', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'sslcertmode': 'allow', 'sslsni': '1', 'ssl_min_protocol_version': 'TLSv1.2', 'gssencmode': 'prefer', 'krbsrvname': 'postgres', 'gssdelegation': '0', 'target_session_attrs': 'any', 'load_balance_hosts': 'disable'}

====================================================================================================

"""

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] connect 함수 : 데이터베이스 연결하기

■ connect 함수를 사용해 데이터베이스에 연결하는 방법을 보여준다. ▶ 예제 코드 1 (PY)


import psycopg2

with psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234") as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM test;")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

"""
(1, '홍길동', 20)
(2, '김철수', 30)
(3, '이영희', 25)
"""

import psycopg2

with psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234") as connection:

with connection.cursor() as cursor:

cursor.execute("SELECT * FROM test;")

resultList = cursor.fetchall()

for result in resultList:

print(result)

"""

(1, '홍길동', 20)

(2, '김철수', 30)

(3, '이영희', 25)

"""

▶ 예제 코드 2 (PY)


import psycopg2

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM test;")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

"""
(1, '홍길동', 20)
(2, '김철수', 30)
(3, '이영희', 25)
"""

import psycopg2

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:

with connection.cursor() as cursor:

cursor.execute("SELECT * FROM test;")

resultList = cursor.fetchall()

for result in resultList:

print(result)

"""

(1, '홍길동', 20)

(2, '김철수', 30)

(3, '이영희', 25)

"""

▶ requirements.txt

[PYTHON/PSYCOPG2] execute_values 함수 : BULK INSERT 처리하기

■ execute_values 함수를 사용해 BULK INSERT를 처리하는 방법을 보여준다. ▶ 테스트 테이블 (SQL)


CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

▶ 예제 코드 (PY)


import psycopg2
import psycopg2.extras

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:
    with connection.cursor() as cursor:

        cursor.execute("DELETE FROM test")

        psycopg2.extras.execute_values(
            cursor, "INSERT INTO test (id, name, age) VALUES %s;",
            [(1, '홍길동', 20), (2, '김철수', 30), (3,'이영희', 25)]
        )

        connection.commit()

        cursor.execute("SELECT * FROM test")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

"""
(1, '홍길동', 20)
(2, '김철수', 30)
(3, '이영희', 25)
"""

import psycopg2

import psycopg2.extras

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:

with connection.cursor() as cursor:

cursor.execute("DELETE FROM test")

psycopg2.extras.execute_values(

cursor, "INSERT INTO test (id, name, age) VALUES %s;",

[(1, '홍길동', 20), (2, '김철수', 30), (3,'이영희', 25)]

)

connection.commit()

cursor.execute("SELECT * FROM test")

resultList = cursor.fetchall()

for result in resultList:

print(result)

"""

(1, '홍길동', 20)

(2, '김철수', 30)

(3, '이영희', 25)

"""

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] execute_batch 함수 : BULK INSERT 처리하기

■ execute_batch 함수를 사용해 BULK INSERT를 처리하는 방법을 보여준다. ▶ 테스트 테이블 (SQL)


CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

▶ 예제 코드 (PY)


import psycopg2
import psycopg2.extras

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:
    with connection.cursor() as cursor:

        cursor.execute("DELETE FROM test")

        psycopg2.extras.execute_batch(
            cursor, "INSERT INTO test (id, name, age) VALUES (%s, %s, %s);",
            [(1, '홍길동', 20), (2, '김철수', 30), (3,'이영희', 25)]
        )

        connection.commit()

        cursor.execute("SELECT * FROM test")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

"""
(1, '홍길동', 20)
(2, '김철수', 30)
(3, '이영희', 25)
"""

import psycopg2

import psycopg2.extras

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:

with connection.cursor() as cursor:

cursor.execute("DELETE FROM test")

psycopg2.extras.execute_batch(

cursor, "INSERT INTO test (id, name, age) VALUES (%s, %s, %s);",

[(1, '홍길동', 20), (2, '김철수', 30), (3,'이영희', 25)]

)

connection.commit()

cursor.execute("SELECT * FROM test")

resultList = cursor.fetchall()

for result in resultList:

print(result)

"""

(1, '홍길동', 20)

(2, '김철수', 30)

(3, '이영희', 25)

"""

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] Cursor 클래스 : execute/fetchall 메소드를 사용해 데이터 조회하기

■ Cursor 클래스의 execute/fetchall 메소드를 사용해 데이터를 조회하는 방법을 보여준다. ▶ 예제 코드 (PY)


import psycopg2

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM test")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

import psycopg2

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:

with connection.cursor() as cursor:

cursor.execute("SELECT * FROM test")

resultList = cursor.fetchall()

for result in resultList:

print(result)

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] with … as … 구문 : 리소스 자동 해제하기

■ with … as … 구문을 사용해 리소스를 자동으로 해제하는 방법을 보여준다. ▶ 예제 코드 (PY)


import psycopg2

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM test")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

import psycopg2

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:

with connection.cursor() as cursor:

cursor.execute("SELECT * FROM test")

resultList = cursor.fetchall()

for result in resultList:

print(result)

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[PYTHON/PSYCOPG2] Cursor 클래스 : execute 메소드를 사용해 데이터 추가하기

■ Cursor 클래스의 execute 메소드를 사용해 데이터를 추가하는 방법을 보여준다. ▶ 테스트 테이블 (SQL)


CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

▶ 예제 코드 (PY)


import psycopg2

connection = psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'")

cursor = connection.cursor()

cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")

connection.commit()

cursor.close()
connection.close()

import psycopg2

connection = psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'")

cursor = connection.cursor()

cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")

connection.commit()

cursor.close()

connection.close()

▶ requirements.txt

[PYTHON/PSYCOPG2] Cursor 클래스 : execute 메소드를 사용해 테이블 생성하기

■ Cursor 클래스의 execute 메소드를 사용해 테이블을 생성하는 방법을 보여준다. ▶ 예제 코드 (PY)


import psycopg2

connection = psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'")

cursor = connection.cursor()

sql = """
CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);
"""

cursor.execute(sql)

connection.commit()

cursor.close()
connection.close()

import psycopg2

connection = psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'")

cursor = connection.cursor()

sql = """

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

"""

cursor.execute(sql)

connection.commit()

cursor.close()

connection.close()

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9

[DATABASE/POSTGRESQL] GRANT ALL PRIVILEGES ON DATABASE … TO … 명령 : 데이터베이스 사용자 접근 권한 설정하기

■ GRANT ALL PRIVILEGES ON DATABASE … TO … 명령을 사용해 해당 데이터베이스에 사용자 접근 권한을 설정하는 방법을 보여준다. ▶ 예제 코드

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (PGSQL)

■ 사용자 권한을 조회하는 방법을 보여준다. (PGSQL) ▶ 실행 명령


postgres=# \du

postgres=# \du

▶ 실행 결과


                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser1 | Superuser, Create DB                                       | {}
 testuser2 |                                                            | {}

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

testuser1 | Superuser, Create DB | {}

testuser2 | | {}

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (테이블 수준)

■ 사용자 권한을 조회하는 방법을 보여준다. (테이블 수준) ▶ 예제 코드 (SQL)


SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    PRIVILEGE_TYPE
FROM  information_schema.role_table_grants
WHERE GRANTEE = 'testuser1';

SELECT

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME,

PRIVILEGE_TYPE

FROM information_schema.role_table_grants

WHERE GRANTEE = 'testuser1';

※ 해당 뷰 명칭은 소문자로 입력한다. ※ testuser1 :

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (스키마 수준)

■ 사용자 권한 조회하기 (스키마 수준) ▶ 예제 코드 (SQL)


SELECT
    N.NSPNAME                                            AS SCHEMA,
    U.USENAME                                            AS USER,
    HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'USAGE' ) AS CAN_USE,
    HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'CREATE') AS CAN_CREATE
FROM       PG_NAMESPACE N
CROSS JOIN PG_USER U
WHERE U.USENAME = 'testuser1';

SELECT

N.NSPNAME AS SCHEMA,

U.USENAME AS USER,

HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'USAGE' ) AS CAN_USE,

HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'CREATE') AS CAN_CREATE

FROM PG_NAMESPACE N

CROSS JOIN PG_USER U

WHERE U.USENAME = 'testuser1';

▶ 실행 결과


       schema       |   user    | can_use | can_create
--------------------+-----------+---------+------------
 pg_toast           | testuser1 | t       | t
 pg_temp_1          | testuser1 | t       | t
 pg_toast_temp_1    | testuser1 | t       | t
 pg_catalog         | testuser1 | t       | t
 public             | testuser1 | t       | t
 information_schema | testuser1 | t       | t
(6 rows)

schema | user | can_use | can_create

--------------------+-----------+---------+------------

pg_toast | testuser1 | t | t

pg_temp_1 | testuser1 | t | t

pg_toast_temp_1 | testuser1 | t | t

pg_catalog | testuser1 | t | t

public | testuser1 | t | t

information_schema | testuser1 | t | t

(6 rows)

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (데이터베이스 수준)

■ 사용자 권한을 조회하는 방법을 보여준다. (데이터베이스 수준) ▶ 예제 코드 (SQL)


SELECT
    D.DATNAME AS DATABASE,
    U.USENAME AS USER,
    HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CONNECT') AS CAN_CONNECT,
    HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CREATE' ) AS CAN_CREATE
FROM       PG_DATABASE D
CROSS JOIN PG_USER     U
WHERE U.USENAME = 'testuser1';

SELECT

D.DATNAME AS DATABASE,

U.USENAME AS USER,

HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CONNECT') AS CAN_CONNECT,

HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CREATE' ) AS CAN_CREATE

FROM PG_DATABASE D

CROSS JOIN PG_USER U

WHERE U.USENAME = 'testuser1';

※ testuser1 : 사용자명, 대소문자 구분한다. ▶ 실행 결과

[DATABASE/POSTGRESQL] CREATE ROLE … WITH LOGIN PASSWORD … 명령 : 사용자 생성하기

■ CREATE ROLE … WITH LOGIN PASSWORD … 명령을 사용해 사용자를 생성하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE ROLE user1 WITH LOGIN PASSWORD '1234';

CREATE ROLE user1 WITH LOGIN PASSWORD '1234';

※ user1 :

[DATABASE/POSTGRESQL] 데이터베이스 목록 조회하기 (PGSQL)

■ 데이터베이스 목록을 조회하는 방법을 보여준다. (PGSQL) ▶ 실행 명령


postgres=# \l

또는

postgres=# \list

postgres=# \l

또는

postgres=# \list

▶ 실행 결과


                                       List of databases
   Name    |  Owner   | Encoding |     Collate      |      Ctype       |   Access privileges
-----------+----------+----------+------------------+------------------+-----------------------
 postgres  | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 |
 stat      | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 |
 template0 | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |          |                  |                  | postgres=CTc/postgres
 template1 | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |          |                  |                  | postgres=CTc/postgres
(4 rows)

List of databases

-----------+----------+----------+------------------+------------------+-----------------------

(4 rows)