PostgreSQL - GRANT

In PostgreSQL, the GRANT command is used to give specific privileges on database objects to one or more users or roles. By managing privileges, you can control which users can read, modify, or administer particular objects.

Syntax:

The basic syntax for the GRANT command is:

GRANT privilege [, ...]
ON object_type [ * ] object_name [, ...]
TO { role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];

privilege: The privilege you want to grant. Common privileges include SELECT, INSERT, UPDATE, DELETE, USAGE, EXECUTE, etc.
object_type: The type of the database object. This could be TABLE, SEQUENCE, DATABASE, FUNCTION, etc.
object_name: Name of the database object.
role_name: The user or role that will receive the privilege.
PUBLIC: Grants the privilege to all users.
WITH GRANT OPTION: Allows the user to grant the privilege to other users.

Examples:

Grant SELECT permission on a table:
```
GRANT SELECT ON TABLE employees TO user1;
```
This allows user1 to select rows from the employees table.
Grant multiple privileges:
```
GRANT SELECT, INSERT, UPDATE ON TABLE employees TO user2;
```
user2 can now read, insert into, and update the employees table.
Grant all privileges on a database:
```
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin_role;
```
This gives the admin_role all permissions on the mydb database.
Grant privilege to all tables in a schema:
```
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
```
This command provides readonly_user with the select permission on all tables within the public schema.
Grant with WITH GRANT OPTION:
```
GRANT UPDATE ON TABLE employees TO manager WITH GRANT OPTION;
```
This allows the manager role not only to update the employees table but also to grant this update privilege to other roles.
Grant usage on a sequence:
```
GRANT USAGE ON SEQUENCE employees_id_seq TO user3;
```
This gives user3 the permission to use the sequence employees_id_seq.

Remember that after revoking privileges, it's a good practice to run the REASSIGN OWNED and DROP OWNED commands if the user or role is being removed or you want to ensure that all objects previously owned or privileges granted by a role are properly reassigned or dropped.

Managing permissions correctly is crucial for database security, so always ensure that you grant the least privilege necessary for a role to perform its tasks.

How to use GRANT in PostgreSQL:
- Description: Grant specific privileges on database objects to users or roles.
- Code:
```
GRANT SELECT, INSERT, UPDATE ON TABLE example_table TO user_name;
```

GRANT and REVOKE in PostgreSQL:

Description: Use GRANT to give privileges and REVOKE to take them away.

Code:

GRANT SELECT ON TABLE example_table TO user_name;
REVOKE SELECT ON TABLE example_table FROM user_name;

PostgreSQL GRANT privileges on tables:
- Description: Grant specific privileges on a table to a user or role.
- Code:
```
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE example_table TO user_name;
```
Granting permissions to users and roles in PostgreSQL:
- Description: Grant various permissions to a specific user or role.
- Code:
```
GRANT SELECT, INSERT, UPDATE ON TABLE example_table TO user_name;
```
GRANT ALL privileges in PostgreSQL:
- Description: Grant all privileges on a table to a user or role.
- Code:
```
GRANT ALL ON TABLE example_table TO user_name;
```
Granting SELECT, INSERT, UPDATE, DELETE permissions in PostgreSQL:
- Description: Grant individual CRUD permissions on a table.
- Code:
```
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE example_table TO user_name;
```
Granting EXECUTE permission on functions in PostgreSQL:
- Description: Grant the ability to execute a specific function.
- Code:
```
GRANT EXECUTE ON FUNCTION example_function() TO user_name;
```
Granting USAGE permission on sequences in PostgreSQL:
- Description: Allow a user to use a sequence for generating values.
- Code:
```
GRANT USAGE ON SEQUENCE example_sequence TO user_name;
```
GRANT with WITH GRANT OPTION in PostgreSQL:
- Description: Grant privileges with the ability to grant them to others.
- Code:
```
GRANT SELECT, INSERT ON TABLE example_table TO user_name WITH GRANT OPTION;
```
Granting privileges on columns in PostgreSQL:
- Description: Grant specific privileges on individual columns of a table.
- Code:
```
GRANT SELECT (column1, column2) ON TABLE example_table TO user_name;
```
Revoking permissions with REVOKE in PostgreSQL:
- Description: Take away previously granted privileges.
- Code:
```
REVOKE SELECT ON TABLE example_table FROM user_name;
```
GRANT on schema and database levels in PostgreSQL:
- Description: Grant privileges on entire schemas or databases.
- Code:
```
GRANT USAGE ON SCHEMA example_schema TO user_name;
```
Granting access to specific schemas in PostgreSQL:
- Description: Grant access to specific schemas for a user or role.
- Code:
```
GRANT USAGE ON SCHEMA example_schema TO user_name;
```
Granting CONNECT permission in PostgreSQL:
- Description: Allow a user to connect to a specific database.
- Code:
```
GRANT CONNECT ON DATABASE example_db TO user_name;
```
Granting EXECUTE privilege on procedures in PostgreSQL:
- Description: Grant the ability to execute stored procedures.
- Code:
```
GRANT EXECUTE ON FUNCTION example_procedure() TO user_name;
```
Granting privileges to PUBLIC in PostgreSQL:
- Description: Grant privileges to all users by using the PUBLIC keyword.
- Code:
```
GRANT SELECT ON TABLE example_table TO PUBLIC;
```
Managing privileges with GRANT and REVOKE in PostgreSQL:
- Description: Use GRANT and REVOKE to manage permissions efficiently.
- Code:
```
GRANT SELECT, INSERT ON TABLE example_table TO user_name;
REVOKE INSERT ON TABLE example_table FROM user_name;
```
Viewing granted privileges in PostgreSQL:
- Description: Check existing privileges on a database object.
- Code:
```
\z example_table
```