PostgreSQL: CREATE USER

create user, creates a new user in PostgreSQL

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

• CREATE USER your_username;
• 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

See also

• PostgreSQL: Database roles, CREATE USER, CREATE ROLE, \du, \du+, create user, Alter user
• PostgreSQL: psql, .psqlrc, .pgpass, $HOME/.pg service.conf, -U, -W, -d, meta-commands: \du, \dg, \ddp, \dp, \list, \dn, \dt, \c, \l, \di, \q, show users, show, su - postgres, psql -c, \?, \connect, \conninfo, --version, --help, PL/pgSQL
• SQL, NewSQL, RDMS: GraphQL (2015), SQLite, select, insert, select count, Distributed SQL, TablePlus, Cache hit ratio, pg_stat_user_tables, EXPLAIN, Index (PostgreSQL), DDL, SQL/MED, Group by, List users, SQL Injection, SQLAlchemy, CREATE USER, CREATE ROLE, EXPLAIN, ALTER DATABASE, REVOKE, NOLOCK, NOWAIT, EXISTS, Unique constraint, .sql