PostgreSQL - REVOKE

In PostgreSQL, the REVOKE command is used to remove specific permissions granted to users or roles. Using this command, you can take back privileges like SELECT, INSERT, UPDATE, DELETE, USAGE, REFERENCES, etc., that were previously granted to users or roles.

Syntax:

REVOKE [GRANT OPTION FOR] privilege [, ...]
ON object_type [FROM { user | GROUP | PUBLIC } [, ...] ]
[WHERE { current_user | current_role | user | CURRENT_USER | role } ]
[CASCADE | RESTRICT];

Where:

privilege represents the permissions you want to revoke, such as SELECT, UPDATE, INSERT, etc.
object_type can be a TABLE, SEQUENCE, DATABASE, SCHEMA, DOMAIN, TYPE, FUNCTION, etc.
user, GROUP, or PUBLIC represents the users or roles from whom the privileges are being revoked.

Examples:

Revoke SELECT on a Table from a User:
To revoke the SELECT privilege on a table named my_table from a user named john, you can use:
```
REVOKE SELECT ON TABLE my_table FROM john;
```
Revoke All Privileges on a Database from a User:
If you want to revoke all privileges on a database named my_database from a user named john, you can use:
```
REVOKE ALL PRIVILEGES ON DATABASE my_database FROM john;
```
Revoke All Privileges on All Tables in a Schema:
To revoke all privileges on all tables in the public schema from a user named john, you can use:
```
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM john;
```
Revoke Usage on a Schema:
To revoke usage on a schema named my_schema from the PUBLIC, which means from all users:
```
REVOKE USAGE ON SCHEMA my_schema FROM PUBLIC;
```
Revoke Privilege and Also Cascade:
If you've granted a privilege to a user or role, and that user or role has subsequently granted the privilege to other users or roles, you can revoke the privilege from everyone by using the CASCADE option:
```
REVOKE SELECT ON TABLE my_table FROM john CASCADE;
```

Important Notes:

Ensure you have the proper permissions to revoke the rights. Typically, only the owner of the object or a superuser can revoke rights on that object.
REVOKE does not automatically revoke privileges that were granted to other roles by the role from which the privileges are being revoked. To cascade the revocation, use the CASCADE option.
Be cautious when using REVOKE, especially in production databases, as it can impact applications or users relying on the privileges you're revoking.

In essence, REVOKE is an essential command in PostgreSQL for maintaining security and ensuring only authorized users and roles have access to specific database objects. Always make sure to test the impact of revocations in a safe environment before applying them in production.

How to use REVOKE in PostgreSQL: The REVOKE statement in PostgreSQL is used to revoke privileges previously granted to users or roles.
```
REVOKE privilege_name
ON object_name
FROM user_or_role;
```

Revoking privileges in PostgreSQL:

REVOKE SELECT, INSERT
ON TABLE your_table
FROM your_user;

Revoking privileges from a user in PostgreSQL:

REVOKE ALL PRIVILEGES
ON ALL TABLES IN SCHEMA public
FROM your_user;

REVOKE and specific database objects in PostgreSQL:

REVOKE USAGE
ON SEQUENCE your_sequence
FROM your_user;

Revoking ALL privileges in PostgreSQL:

REVOKE ALL PRIVILEGES
ON TABLE your_table
FROM your_user;

Granting and revoking privileges on tables in PostgreSQL:

-- Grant SELECT and INSERT privileges
GRANT SELECT, INSERT
ON TABLE your_table
TO your_user;

-- Revoke SELECT privilege
REVOKE SELECT
ON TABLE your_table
FROM your_user;

Revoking privileges on schemas in PostgreSQL:

REVOKE USAGE
ON SCHEMA your_schema
FROM your_user;

Revoking privileges on functions and procedures in PostgreSQL:
```
REVOKE EXECUTE
ON FUNCTION your_function()
FROM your_user;
```

Revoking privileges on sequences in PostgreSQL:

REVOKE USAGE, SELECT
ON SEQUENCE your_sequence
FROM your_user;

Revoking privileges on views in PostgreSQL:

REVOKE SELECT
ON VIEW your_view
FROM your_user;

Revoking privileges on columns in PostgreSQL:

REVOKE SELECT(column_name)
ON TABLE your_table
FROM your_user;

Revoking privileges on specific roles in PostgreSQL:
```
REVOKE your_role
FROM your_user;
```

REVOKE and GRANT options in PostgreSQL:

REVOKE INSERT
ON TABLE your_table
FROM your_user
CASCADE;

Revoking privileges on specific columns in PostgreSQL:

REVOKE SELECT(column_name)
ON TABLE your_table
FROM your_user;

Revoking privileges on a specific schema in PostgreSQL:
```
REVOKE USAGE
ON SCHEMA your_schema
FROM your_user;
```
Revoking privileges on a specific table for a specific user in PostgreSQL:
```
REVOKE ALL PRIVILEGES
ON TABLE your_table
FROM your_user;
```

Checking existing privileges before revoking in PostgreSQL:

-- Check existing privileges
\dp your_table

-- Revoke privileges
REVOKE SELECT, INSERT
ON TABLE your_table
FROM your_user;

Revoking privileges on a specific function or procedure in PostgreSQL:
```
REVOKE EXECUTE
ON FUNCTION your_function()
FROM your_user;
```