PostgreSQL: CREATE USER

create user, creates a new user in PostgreSQL

• Official documentation: https://www.postgresql.org/docs/current/sql-createuser.html

WITH PASSWORD
WITH ENCRYPTED PASSWORD

• CREATE USER "your_username";
• CREATE USER your_username WITH PASSWORD 'your_pass';

• CREATE USER your_username WITH ENCRYPTED PASSWORD 'your_pass';
• CREATE USER username WITH CREATEUSER;
• CREATE USER username WITH CREATEROLE;

Synopsys

CREATE USER name [ [ 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'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Examples

• create user your_username

Create a new user

create user your_username;
Output:
CREATE ROLE

create user your_username;
Output:
ERROR: permission denied to create role

Create a new user with errors

create user your.username;
Output:
ERROR:  syntax error at or near "."

create user your_username;
ERROR:  cannot execute CREATE ROLE in a read-only transaction

Create a new user and assign a password

CREATE USER your_username WITH ENCRYPTED PASSWORD 'your_pass';
Output:
CREATE ROLE

CREATE USER your_username WITH ENCRYPTED PASSWORD 'your_pass';
Output:
ERROR:  role "your_username" already exists
Solution:
ALTER USER user_name WITH PASSWORD 'new_password';

Assign a role with GRANT command:
GRANT your_defined_role TO your_username;
Output:
GRANT ROLE

• List users: \du

Related terms

• GRANT
• Create database (PostgreSQL)
• ALTER USER YOUR_USER WITH PASSWORD 'your_new_password';
• sudo -u postgres psql
• CREATE
• SUPERUSER
• community.postgresql.postgresql_query
• community.postgresql.postgresql_privs
• DROP USER

See also

• PostgreSQL: Database roles, CREATE USER, CREATE ROLE, \du, \du+, create user, Alter user
• CREATE [ DATABASE | INDEX | USER | ROLE | VIEW | TABLE | TRIGGER | FUNCTION | COLLATION ]
• SQL, NewSQL, RDMS: GraphQL (2015), SQLite, select, insert, select count, Distributed SQL, TablePlus, Cache hit ratio, pg_stat_user_tables, EXPLAIN, Index (PostgreSQL), DML, DDL, SQL/MED, Group by, List users, SQL Injection, SQLAlchemy, CREATE USER, CREATE ROLE, EXPLAIN, ALTER DATABASE, REVOKE, NOLOCK, NOWAIT, EXISTS, Unique constraint, .sql, OpenSearch SQL, Transact-SQL