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:
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';
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
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4