CREATE USER

Synopsis

Use the CREATE USER statement to create a user. The CREATE USER statement is an alias for CREATE ROLE, but creates a role that has LOGIN privileges by default.

Syntax

Grammar
Diagram

create_user ::= CREATE USER role_name 
                [ [ WITH ] role_option [ , ... ] ]

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

create_user

role_option

Semantics

See CREATE ROLE for more details.

Public schema privileges

In releases prior to v2.25, which were PostgreSQL 11 compatible, new database users were automatically granted CREATE and USAGE privileges on the public schema. This allowed them to create objects, such as tables and views, in the shared schema by default.

Starting with release v2.25 (PostgreSQL 15 compatible), this default privilege is no longer provided. New users can no longer create objects in the public schema unless explicitly authorized. Administrators must now manually grant CREATE or other required privileges on the public schema to specific roles or users as needed. This provides a more secure default configuration and protection against attacks described in CVE-2018-1058.

Administrators can grant access to the public schema to specific users as follows:

GRANT CREATE ON SCHEMA public TO <username>;

Examples

Create a sample user with password.

yugabyte=# CREATE USER John WITH PASSWORD 'password';

Grant John all permissions on the yugabyte database.

yugabyte=# GRANT ALL ON DATABASE yugabyte TO John;

Remove John's permissions from the yugabyte database.

yugabyte=# REVOKE ALL ON DATABASE yugabyte FROM John;