Revoke all privileges on database Postgres with the REVOKE command with a little help from our experts. 

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Services.

Let’s look at how our Support Team is ready to help customers revoke all privileges on the database PostgreSQL.

How to Revoke all Privileges on database Postgres

The REVOKE statement in PostgreSQL revokes any privileges on database objects from a role. This statement is handy in revoking already granted privileges. Let’s take a look at the syntax of the REVOKE statement:

REVOKE privilege | ALL
ON TABLE table_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;

According to our Support Techs, we have to mention the one or more privileges that we want to revoke. Moreover, we can also use the ALL option in order to revoke all privileges.

Next, we can opt to specify the name of one table after the ON keyword or use ALL TABLES option to revoke privileges from all tables. Finally, we can also mention the name of the role from which we want to revoke the privileges.

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

The GRANT OPTION FOR indicates that only the grant option for the privilege is revoked. Furthermore, this is convenient when we do not want to revoke the privilege itself.

PostgreSQL REVOKE statement example

1. First, log into the database with the postgres user:

   psql -U postgres -d bookrental
2. Next, create a new role john is seen below:

    CREATE ROLE john
   LOGIN 
   PASSWORD 'securePass1';
3. After that, grant all privileges on the books table to the role john:

   GRANT ALL 
   ON books
   TO john;
4. Finally, grant the SELECT privilege on the fiction table to the role john:

   GRANT SELECT
   ON fiction
   TO john;
5. Now, we are going to revoke the SELECT privilege from the fiction table from the role john with this statement:

   REVOKE SELECT
   ON fiction
   FROM john;

   Or, we can revoke all privileges on the fiction table from the role john by using the REVOKE statement with the ALL option as seen here:

   REVOKE ALL
   ON fiction
   FROM john;

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to revoke all privileges on the database PostgreSQL.