How to Create PostgreSQL users?

Spread the love

There are two main methods for creating users in PostgreSQL:

  1. Using the createuser client utility: This is a command-line tool specifically designed for user management in PostgreSQL.

  2. Using the psql interactive shell: This is the PostgreSQL command-line interface where you can directly execute SQL statements, including user creation with the CREATE ROLE command (often shortened to CREATE USER).

Here’s a breakdown of both methods:

Using createuser:

  • This method requires you to be logged in as a superuser or a user with the CREATEROLE privilege.
  • The syntax is:
createuser [options] username
  • Some common options include:

    • -S: Create a superuser
    • -D: Create a user with the CREATEDB privilege (can create databases)
    • -R: Create a user with the CREATEROLE privilege (can create other users)
    • -P: Prompt for a password interactively
  • For example, to create a regular user named new_user with a password, you would run:

createuser -P new_user

Using psql:

  • Connect to the PostgreSQL server using psql.

  • Use the CREATE ROLE command with similar options as createuser.

  • The syntax is:

CREATE ROLE [options] username;  Use code with caution.
  • Some commonly used options are the same as createuser.
  • For instance, to create a user named new_user with a password set interactively, you would run within psql:
  • Always follow security best practices when creating users. Avoid granting unnecessary privileges and use strong passwords.
  • Refer to the PostgreSQL documentation for a detailed list of options and functionalities for both createuser and CREATE ROLE

I hope this helps!