PostgreSQL 10 유저 superuser 권한, 롤 부여

OS환경 : Oracle Linux 7.6 (64bit)

DB 환경 : PostgreSQL 10

방법 : PostgreSQL 10 유저 superuser 권한, 롤 부여

psql 접속 후 유저 권한(롤) 확인

1 2 3 4 5 6 7 8 9

$ psql psql (12.0) Type "help" for help.   postgres=# \du                                    List of roles  Role name |                         Attributes                         | Member of  -----------+------------------------------------------------------------+-----------  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

신규 유저 scott 생성

1 2 3 4 5 6 7 8

postgres=# create user scott; CREATE ROLE postgres=# \du                                    List of roles  Role name |                         Attributes                         | Member of  -----------+------------------------------------------------------------+-----------  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  scott     |                                                            | {}

해당 유저에게 postgres 유저와 같은 권한(롤) 부여 후 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

postgres=# alter user scott with superuser; ALTER ROLE postgres=# alter user scott with createdb; ALTER ROLE postgres=# alter user scott with createrole; ALTER ROLE postgres=# alter user scott with replication; ALTER ROLE postgres=# alter user scott with bypassrls; ALTER ROLE postgres=# \du                                    List of roles  Role name |                         Attributes                         | Member of  -----------+------------------------------------------------------------+-----------  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  scott     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

alter user 명령을 더 알고 싶으면 아래 명령 입력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

postgres=# \h alter user Command:     ALTER USER Description: change a database role Syntax: ALTER USER role_specification [ WITH ] option [ ... ]   where option can be:         SUPERUSER | NOSUPERUSER     | CREATEDB | NOCREATEDB     | CREATEROLE | NOCREATEROLE     | INHERIT | NOINHERIT     | LOGIN | NOLOGIN     | REPLICATION | NOREPLICATION     | BYPASSRLS | NOBYPASSRLS     | CONNECTION LIMIT connlimit     | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL     | VALID UNTIL 'timestamp'   ALTER USER name RENAME TO new_name   ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAUL T } ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL   where role_specification can be:       role_name   | CURRENT_USER   | SESSION_USER   URL: https://www.postgresql.org/docs/12/sql-alteruser.html

참조 :