In PostgreSQL, the CREATE USER statement with “PASSWORD” attributes creates a new user with login privileges. However, the newly created user can’t access or modify the database objects, such as tables, functions, views, etc. until-unless the user is granted privileges on the database objects.
To grant all the database privileges to the user/role the “GRANT ALL” statement is used in Postgres.
This write presents a step-wise guide on how to create a database, user, and grant privileges on the database to the user.
How to Create a User, Database, and Grant User Privileges to Database?
Follow the below-provided steps to learn how to create a user and a database, and grant user privileges to the database:
Step 1: Creating a User
Launch the SQL Shell, provide the login details, and execute the below-provided command to create a new user:
CREATE USER example_user WITH PASSWORD 'user_12345';
You can verify the user creation by executing the “\du” command:
\du;
The output signifies that a new user named “example_user” has been successfully created.
Step 2: Creating a Database
Use the below-given command to create a new database named “example_db”:
CREATE DATABASE example_db;
To verify the database creation, use the “\l” command:
\l
The output snippet demonstrates that a new database named “example_db” has been successfully created.
Step 3: Grant All Privileges/Access to the User/Role
Finally, execute the “GRANT ALL PRIVILEGES” command to grant all the database privileges to the user, such as creating a database, dropping a database, etc.
GRANT ALL PRIVILEGES ON DATABASE "example_db" to example_user;
The output shows that the permissions have been successfully granted to the user.
That’s all from this post!
Conclusion
In PostgreSQL, the CREATE USER statement with “PASSWORD” attributes creates a new user with login privileges. While the “CREATE DATABASE” command creates a new database. However, the newly created user can’t access or modify the database objects, such as tables, functions, views, etc. until the user is granted privileges on the database objects. To fulfill this purpose, the “GRANT ALL” statement is used in Postgres. This post presented a comprehensive guide on creating a new user, a database, and granting/assigning the database privileges to the newly created user.
