Updated May 19, 2023
Introduction to PostgreSQL flush privileges
The following article provides an outline for PostgreSQL flush privileges. PostgreSQL provides a user management facility to handle user privilege. We grant different privilege to users as well as revoke user privileges. Flush privilege is a user management facility in which we revoke all grant privileges of users, which means we reset all grant privileges of users. The flush privileges means to send the instruction to the server to reload all grant privileges. The flush privilege plays an important role in the database administration system because flush privilege is an administrative part. In flush privilege, we perform different operations, such as revoking the user’s single privilege, multiple privileges of a single user, etc.
Syntax:
revoke privilege on schema name from user name;Explanation:
- In the above syntax, where revoke privilege is used to reset all grant privileges of users, schema name is used to indicate table name and database name, where the user name is a specified user name and on and from are keywords.
How flush privileges work in PostgreSQL?
- We must install PostgreSQL in our system.
- Required basic knowledge of PostgreSQL.
- We must require users and schema to perform flush privilege.
- Must need basic knowledge about user management, that means how it is used.
- We can perform different operations on users with the help of psql and pgAdmin.
Before flush privilege, let’s see how we can grant users the different types of privilege as follows.
- select: In this privilege, suppose the user needs to choose any column of the table when we use select privilege.
- insert: In this privilege, suppose users need to insert data in the table at that time we use insert privilege.
- update: In this privilege, suppose users need to update any record from the table at that time we use update privilege.
- delete: In this privilege, suppose users need to delete any record from the table at that time we use the delete privilege.
- create: In this privilege, suppose users need to create a new table in the specified database at that time, we use create privilege.
- temporary: In this privilege, suppose users need to create a new temporary table for a specific time period at that time, we use temporary privilege.
- grant all privilege: In this privilege, the user is able to grant all privileges. GRANT command is used to assign privilege to users.
Now let’s see how we can assign privilege to users.
Select privilege:
Syntax:
grant privilege on object to user name;Explanation:
- In the above syntax, where the grant is a command, select is a privilege, the object is the specified table name, and user name means specified user name that we want to assign select privilege.
Example:
grant select on emp t sam;Explanation:
- In this example, we see how we can grant the select privilege to the sam user.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Now let’s see how we can grant multiple privileges to a single user.
Syntax:
grant privilege 1, privilege 2….. on table name to user name;Explanation:
- In the above syntax, the grant is a command where privilege 1, privilege 2 is a privilege which we need to assign, the table name is the specified table name, and the user is the specified user name.
Example:
grant select, insert, update on emp to sam;Explanation:
- In the above example, we assign more than one privilege to the sam user, where select, insert, and update are the privilege, emp is a table name, and sam is a username.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Let’s see how we can use flush privilege to flush privilege. We use the REVOKE command. The flush privilege is also called a reset operation.
Example:
revoke select on emp from sam;Explanation:
- In the above example, suppose the user need to flush select privilege of sam user at that time we use the above statement.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Now let’s see how we can flush more than one privilege of a single user.
Syntax:
revoke privilege 1, privilege 2,……. .privilege N on table name from user name;Explanation:
- In the above syntax, where privilege 1 and privilege 2 is a flush privilege, table name means specified table name, and user name means specified user name, and on and from are the keywords.
Example:
revoke update, insert on emp from sam;Explanation:
- In the above example, suppose users want to flush more than one privilege at that time, we use the above statement where update and insert are the privilege, emp is a table name, and sam is the user.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Let’s see how we can flush more than one privilege with more than one user.
Syntax:
revoke privilege 1,privilege 2, ………….privilege N on table name from user 1, user2,……. .user N;Explanation:
- In the above syntax, privilege 1 and privilege 2 are the flush privilege, where the table is the table name and user 1 and user 2 are the specified user names.
Example:
revoke select, insert on emp from sam, jenny;Explanation:
- In the above example, we use the revoke command to flush privilege, where select and insert are the privilege, where emp is the specified table name, and sam and jenny are the specified user.
- In this example, we flush more than one privilege with more than one user. On and from are the keywords.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Conclusion
From the above article, we saw the basic syntax of flush privilege. We also saw how we could implement them in PostgreSQL with different examples of each type, such as more than one privilege with a single user and more than one user. This article showed us how we can handle flush privilege in PostgreSQL on the server.
Recommended Article
We hope that this EDUCBA information on “PostgreSQL flush privileges” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
