Step By Step Guide On User Management On PostgreSQL [Access, Creation & Deletion Of User]
Introduction To PostgreSQL
PostgreSQL is a powerful and open-source RDBMS( Relational Database Management System). In layman terms , it’s a software that helps managing and organizing large amounts of data. PostgreSQL, unlike any spreadsheet or flat file, can handle complex relationships between different datasets.
Why Use PostgreSQL?
PostgreSQL is chosen for multiple reasons:
- Open Source: It is free to use and is open for anyone to modify and enhance.
- Scalable: It can manage large amounts of data and users.
- Extensible: It allows you to add custom functions and data types.
- Robust: It’s known for its dependability and ability to recover from downs .
- Community: There’s a large community available for support and resources whenever some guidance is required.
Understanding User Management
User management when talking about databases like PostgreSQL, refers to the process of supervising who can access the database, what changes they can make and which sections of the database they can interact with. Each user can be given different permissions and restrictions. For example, one user might only have view access , while another has access to both modify and delete the records.
Importance of User Management
- Data Security: With user management you can ensure that authorized personnel can get access to critical data. Unauthorized access can lead to data breaches and can compromise user privacy.
- Access Control: User management allows one to control the actions someone can take on the database.Frequent users might have read-only access, while key people might have full control.
- Data Integrity: By restricting who can modify data, user management helps maintain data accuracy. You can also prevent unwanted edits.
- Audit Trails: User management facilitates tracking user activities. If something goes wrong, you can trace back the steps and identify the responsible party.
- Compliance: Many industries have regulatory requirements about data access. Proper user management helps organizations comply with these regulations.
Should You Use Admin Passwords for Services
In our opinion, admin passwords should not be connected to a database.
- Security Risk: Admin passwords are critical and private. If someone gets access to it, they have complete access to your database, which may result in modification and removal of data.
- Limited Accountability: Specific actions cannot be tracked to individuals if everyone has access to the same password. With limited access, you can maintain accountability and track the issues to individuals.
- Best Practice: It’s a suggested practice for security purposes to follow the principle of least privilege. It means that the users have minimum access related to only their jobs, reducing the damage if accounts are ever compromised.
- Ease of Revocation: If you use individual user accounts, it’s much easier to remove access for an individual with personal access, rather than working on the admin password whenever access is to be revoked.
User management will enhance security, data integrity, and accountability. It’s crucial to avoid using admin passwords widely and instead assign individual user accounts with appropriate access,instead of using admin passwords widely.
Steps To Grant User Privileges In PostgreSQL!
1. Access your database using the following command with superuser privileges.
When accessing the PostgreSQL server, the interface will appear as follows
2. Creating a New Database
To verify the creation of your database, enter “ l ” in the command prompt
3. Obtain the Sample Database File
Download the sample database file by clicking on the following link: Sample Database File,After downloading, proceed with the next steps.
4. Extract and Import the SQL File into the Newly Created Database
Open a new terminal and run the below mentioned command to pump data into test database
5. Creating a USER
Now, return to the previous terminal window and enter the following command to create a new user.
This line creates a new user in PostgreSQL. Users are like individual accounts on a computer — they can access the database with different levels of control.
6. Testing the user
Open new terminal and run below command to test the user
a. Login ->
b. Shift to test database
c. Running select command on test database
When you try to access database table, it will show you a “permission denied” option
7. GRANT ALL PRIVILEGES to test user
Return to the superuser terminal, switch the database to the test database, and execute the following command.
This line grants access. Modify these permissions as per your requirements.
8. Testing the permissions
Return to the testuser terminal, switch the database to the test database, and execute the following command.
9. Clean up
a. REVOKE ALL PRIVILEGES
This line takes away all permissions from a user. Permissions control what a user can do within the database. Revoking privileges means the user can’t perform any actions on the tables in the ‘public’ schema.
b. DROP user
This line deletes a user from the database entirely. Once a user is dropped, they can’t access the database anymore.
Conclusion
These commands are like managing different user accounts on a computer. You can create new users, give them access to certain ‘rooms’ (schemas), remove the access, and even delete the users when required. This is crucial for real-world applications where multiple users require different levels of data access.
P.S.
Make sure you run all permission commands inside the exact database where you want to grant user access and control.
Also Read
