Difference between revisions of "PostgreSQL: CREATE USER"

From wikieduonline
Jump to navigation Jump to search
Tags: Mobile web edit, Mobile edit
 
(39 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
<code>[[create user]]</code>, creates a new user in PostgreSQL
 
<code>[[create user]]</code>, creates a new user in PostgreSQL
* Official documentation: https://www.postgresql.org/docs/13/sql-createuser.html
+
* Official documentation: https://www.postgresql.org/docs/current/sql-createuser.html
 +
 
 +
 
 +
WITH PASSWORD
 +
WITH ENCRYPTED PASSWORD
 +
 
 +
* <code>[[CREATE]] USER "your_username";</code>
 +
* CREATE USER your_username WITH PASSWORD 'your_pass';
 +
 
 +
* <code>CREATE USER your_username WITH ENCRYPTED PASSWORD 'your_pass';</code>
 +
* <code>CREATE USER username WITH CREATEUSER;</code>
 +
* <code>CREATE USER username WITH CREATEROLE;</code>
 +
 
 +
 
 +
== 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 ==
 
== Examples ==
 
* <code>create user your_username</code>
 
* <code>create user your_username</code>
  
Create a new user:
+
=== Create a new user ===
 
  create user your_username;
 
  create user your_username;
 
  Output:
 
  Output:
 
  [[CREATE ROLE]]
 
  [[CREATE ROLE]]
  
Create a new user and assign a password:
+
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';
 
  CREATE USER your_username WITH ENCRYPTED PASSWORD 'your_pass';
 
  Output:
 
  Output:
 
  [[CREATE ROLE]]
 
  [[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 <code>[[GRANT]]</code> command:
 +
GRANT [[your_defined_role]] TO your_username;
 +
Output:
 +
[[GRANT ROLE]]
  
 
* [[List users]]: <code>[[\du]]</code>
 
* [[List users]]: <code>[[\du]]</code>
  
 
== Related terms ==
 
== Related terms ==
 +
* <code>[[GRANT]]</code>
 
* [[Create database (PostgreSQL)]]
 
* [[Create database (PostgreSQL)]]
 
* <code>[[ALTER USER]] YOUR_USER WITH PASSWORD 'your_new_password';</code>
 
* <code>[[ALTER USER]] YOUR_USER WITH PASSWORD 'your_new_password';</code>
 
* <code>[[sudo]] -u postgres [[psql]]</code>
 
* <code>[[sudo]] -u postgres [[psql]]</code>
* <code>[[GRANT]]</code>
+
* <code>[[CREATE]]</code>
* {{TOC operation databases}}
+
* <code>[[SUPERUSER]]</code>
 +
* [[community.postgresql.postgresql_query]]
 +
* <code>[[community.postgresql.postgresql_privs]]</code>
 +
* [[DROP USER]]
  
 
== See also ==
 
== See also ==
* {{psql}}
+
* {{Role}}
 +
* {{CREATE}}
 
* {{SQL}}
 
* {{SQL}}
* {{PostgreSQL}}
 
  
  
 
[[Category:PostgreSQL]]
 
[[Category:PostgreSQL]]

Latest revision as of 10:42, 23 September 2024

create user, creates a new user in PostgreSQL


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[edit]

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[edit]

  • create user your_username

Create a new user[edit]

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

Create a new user with errors[edit]

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[edit]

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

Related terms[edit]

See also[edit]

Advertising: