How to create new user in postgresql

To create a new user in PostgreSQL, you can use the CREATE ROLE command. Here is the basic syntax:

CREATE ROLE newuser [WITH [OPTION]...];

Here, newuser is the name of the new user you want to create.

You can also specify additional options to customize the new user's privileges and behavior. Some common options include:

• PASSWORD 'password': sets the password for the new user
• SUPERUSER: grants superuser privileges to the new user
• CREATEROLE: grants the ability to create new roles (users) to the new user
• CREATEDB: grants the ability to create new databases to the new user
• INHERIT: allows the new user to inherit privileges from other roles
• NOINHERIT: prevents the new user from inheriting privileges from other roles
• NOCREATEDB: prevents the new user from creating new databases
• NOCREATEROLE: prevents the new user from creating new roles (users)

Here is an example of creating a new user with a password and superuser privileges:

CREATE ROLE newuser WITH PASSWORD 'mypassword' SUPERUSER;

You can also create a new user with a specific set of privileges by specifying the INHERIT and NOINHERIT options. For example:

CREATE ROLE newuser WITH PASSWORD 'mypassword' INHERIT NOINHERIT;

This will create a new user with a password and allow them to inherit privileges from other roles, but prevent them from inheriting privileges from other roles.

Once you have created the new user, you can log in to the database as that user using the psql command:

psql -U newuser mydatabase

Replace mydatabase with the name of the database you want to connect to.

Note: You can also use the CREATE USER command instead of CREATE ROLE, they are equivalent.