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