w3resource

Step-by-Step Guide to Creating a User in PostgreSQL


PostgreSQL Create User: Granting Access to Databases

Step-by-step guide to creating a user in PostgreSQL. Learn the syntax, examples, and tips for assigning roles and managing permissions effectively.

How to create a user in PostgreSQL?

In PostgreSQL, creating a user involves assigning a role with optional attributes such as login privileges, password, or specific database access.

Syntax for creating a user:

The CREATE USER command is used to add a new user to the database.

CREATE USER username [WITH options];

Options:

  • PASSWORD 'password': Sets a password for the user.
  • CREATEDB: Allows the user to create databases.
  • SUPERUSER: Grants superuser privileges.
  • NOSUPERUSER: Restricts superuser access (default).
  • LOGIN: Allows login (default for users).

Examples:

1. Create a Basic User

Code:

CREATE USER new_user;

Creates a user new_user without a password or specific permissions.

2. Create a User with Password

Code:

CREATE USER data_user WITH PASSWORD 'securepassword';

Creates data_user with a secure password.

3. User with Database Creation Privileges

Code:

CREATE USER db_creator WITH CREATEDB PASSWORD 'dbpass';

Grants the ability to create databases to db_creator.

4. Assigning Superuser Privileges

Code:

CREATE USER admin_user WITH SUPERUSER PASSWORD 'admin123';
  • Grants full access to the database system.

Granting Permissions

After creating a user, you can grant permissions to access specific databases or perform operations.

1. Grant Access to a Database

GRANT CONNECT ON DATABASE my_database TO new_user;

2. Grant Table Permissions

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO new_user;

Modifying Users

To change a user's attributes, use the ALTER USER command.

Change Password

ALTER USER new_user WITH PASSWORD 'newpassword';

Grant Superuser Privileges

ALTER USER new_user WITH SUPERUSER;

Best Practices

  • Strong Passwords: Always use strong, unique passwords for users.
  • Least Privilege Principle: Grant only necessary permissions to minimize security risks.
  • Role Management: Use roles to group users and assign permissions efficiently.

All PostgreSQL Questions, Answers, and Code Snippets Collection.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/postgresql-create-user.php