Privileges

Privilege is a right to execute a particular type of SQL statement, or a right to access another users object.
There are two types of privileges:
Cluster level and Object level privileges.

Cluster Level Privileges : Cluster level privileges are granted by the super user. It can be granted during create user or by altering an existing user.

Object level privileges : Object level privileges can be granted by Superuser or the owner of the object or someone with grant privileges.

Privileges allows a user to perform particular actions on a database object asuch as table, view or sequence.

CLUSTER LEVEL PRIVILEGES : it includes Creating DB, creating user, alter user
    postgres=# alter user testuser with superuser;

    ALTER ROLE
    postgres=# \du
                                                List of roles
    Role name |                                Attributes                                | Member of  
    -----------+--------------------------------------------------------------------------+------------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS               | {}
    role_dba  | Superuser, Create role, Create DB, Cannot login, Replication, Bypass RLS | {}
    tesdbuser | Superuser                                                                | {role_dba}
    testuser  | Superuser

    postgres=# alter user testuser createdb;
    ALTER ROLE
    postgres=# \du
                                                List of roles
    Role name |                                Attributes                                | Member of  
    -----------+--------------------------------------------------------------------------+------------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS               | {}
    role_dba  | Superuser, Create role, Create DB, Cannot login, Replication, Bypass RLS | {}
    tesdbuser | Superuser                                                                | {role_dba}
    testuser  | Superuser, Create DB   

    postgres=# alter user testuser with nosuperuser;
    ALTER ROLE
    postgres=# \du
                                                List of roles
    Role name |                                Attributes                                | Member of  
    -----------+--------------------------------------------------------------------------+------------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS               | {}
    role_dba  | Superuser, Create role, Create DB, Cannot login, Replication, Bypass RLS | {}
    tesdbuser | Superuser                                                                | {role_dba}
    testuser  | Create DB  

We can give “GRANT” privilegde to users which will enable them to grant acess to other users.
    Following are syntax for GRANT :
    >GRANT CONNECT ON DB
    “grant connect on database < db name > to < username >;”
    >GRANT USAGE ON SCHEMA
    “grant usage on schema < schema_name> to < user_name >;”
    >GRANT ALL ON TABLES FOR DML STATEMENTS:SELECT, INSERT, UPDATE,DELETE
    “grant select, insert, update,delete on all tables in schema < schema_name > to < username >;”
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA
    “grant all privileges on all tables in schema < schema_name > to < user_name >;”
    GRANT ALL PRIVILEGES ON ALL SEQUENCE IN SCHEMA
    “grant all privileges on all sequences in schema < schema_name > to < username >”;
    GRANT PERMISSION TO A EXISTING USER TO CREATE DATABASE
    “alter user < username > createdb;”

    MAKE A USER SUPER USER
    “alter user < username > with superuser;”
    REMOVE SUPER USER STATUS
    “alter user < username > with nosuperuser;”
    COLUMN LEVEL ACCESS
    “grant select(col1), update (col1) on < table name > to < username >;”
    REVOKE:
    Removing access from users.
    >REVOKE DELETE/UPDATE PRIVILEGE ON < TABLE > FROM 
    “revoke delete,update on < table_name > from < user >;
    >REVOKE ALL PRIVILEGE
    “revoke all on < table > from < username >;”
    >REVOKING SELECT PRIVILEGE FROM ALL USERS
    “revoke select on < tablename > from public;”


(Assigning role to user)