PrivilegesThere 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 DBWe 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;” « Previous (Assigning role to user) |