PostgreSQL | Role (User) ROLE Creation | Create a Role (CREATE ROLE)

Tags:

• PostgreSQL
• Database

This page explains how to create a new role in PostgreSQL by using the CREATE ROLE command.

Create a New Role

To create a new role, use the CREATE ROLE command. The syntax is as follows.

CREATE ROLE name [ [ WITH ] option [ ... ] ]

option:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

There are many options, but if the role will be used for user authentication, the basic form is as follows.

CREATE ROLE name [ WITH ] LOGIN PASSWORD 'password'

Specify the role name (name) and create a role that can be used for user authentication when connecting to PostgreSQL from a client. The password used for password authentication is specified as 'password'.

To create a role, the role executing the command must be a superuser or have the CREATEROLE privilege.

–

Now try it in practice. Run the following from psql.

postgres=# create role kuma with login password 'bear';
CREATE ROLE
postgres=#

A new role named kuma has been created with bear as its authentication password.

Now list the created roles.

postgres=# \du
                                롤 목록
 롤 이름  |                      속성                      | 소속 그룹:
----------+------------------------------------------------+------------
 kimkc    | 슈퍼유저                                       | {}
 kuma     |                                                | {}
 orange   |                                                | {}
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과 | {}


postgres=#

You can confirm that the newly created kuma role has been added in addition to the postgres role created automatically during PostgreSQL installation.

Connect to PostgreSQL with the Created Role

Try connecting to PostgreSQL with the role just created. Run the following from the command prompt.

psql -U kuma -d devkuma

When using psql, if you omit the database to connect to, psql connects to a database with the same name as the role. Because a database named kuma has not been created here, the created database name is specified explicitly.

The password prompt for kuma appears as shown below, so enter the password specified when creating the role.

C:\Users\kimkc>psql -U kuma -d devkuma
kuma 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

devkuma=>

You can connect to PostgreSQL with the kuma role.

CREATE ROLE Privilege Options

Among the options that can be specified with the CREATE ROLE command, this section explains options related to privileges. Other options are explained as needed.

SUPERUSER or NOSUPERUSER

If SUPERUSER is specified, the role being created becomes a superuser. A superuser has most privileges, so use care when creating one. The postgres role, which is created automatically when PostgreSQL is first installed, is a superuser. If NOSUPERUSER is specified, a role that is not a superuser is created.

To specify SUPERUSER, use the following. If this option is not specified, NOSUPERUSER is assumed by default.

CREATE ROLE name WITH SUPERUSER

CREATEDB or NOCREATEDB

If CREATEDB is specified, the role has permission to create databases. If NOCREATEDB is specified, the role cannot create databases.

To specify CREATEDB, use the following. If this option is not specified, NOCREATEDB is assumed by default.

CREATE ROLE name WITH CREATEDB

CREATEROLE or NOCREATEROLE

If CREATEROLE is specified, the role has permission to create roles. If NOCREATEROLE is specified, the role cannot create roles.

Be careful when granting the CREATEROLE privilege. A role with CREATEROLE can create another role with privileges that it does not itself have. Therefore, specifying CREATEROLE effectively has nearly the same meaning as granting almost all privileges.

To specify CREATEROLE, use the following. If this option is not specified, NOCREATEROLE is assumed by default.

CREATE ROLE name WITH CREATEROLE

–

Now try it in practice. Create a new role named superkuma with CREATEDB and CREATEROLE specified.

devkuma=# create role superkuma with createdb createrole login password 'bear';
CREATE ROLE
devkuma=#

A new role named superkuma has been created.

Now list the created roles.

devkuma=# \du
                                 롤 목록
  롤 이름  |                      속성                      | 소속 그룹:
-----------+------------------------------------------------+------------
 kimkc     | 슈퍼유저                                       | {}
 kuma      |                                                | {}
 orange    |                                                | {}
 postgres  | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과 | {}
 superkuma | 롤 만들기, DB 만들기                           | {}


devkuma=#

In the attributes column for the newly created role, “롤 만들기, DB 만들기” is displayed. This confirms that the CREATEDB and CREATEROLE settings specified when the CREATE ROLE command was executed have been applied.

–

This page explained how to create a new role by using the CREATE ROLE command.