DBA TASKS (PostgreSQL)

• Granting DB access to User
  • CREATE ROLE username WITH PASSWORD ‘new password’

NOSUPERUSER

NOCREATEDB

INHERIT

LOGIN

CONNECTION LIMIT -1;

GRANT read_role TO username;

• Removing DB Access from user
  • REVOKE read_role FROM username;
  • DROP ROLE username;

• Checking User Privileges
  • SELECT * FROM pg_roles;
  • SELECT * FROM pg_auth_members

• Resetting User Password
  • ALTER ROLE username WITH PASSWORD ‘new_password’

• Locking/Unlocking a User
  • ALTER ROLE username NOLOGIN;
  • ALTER ROLE username LOGIN;

• Creating Read only role
  • CREATE ROLE read_only_role;

• Checking Database size
  • SELECT datname,

pg_size_pretty(pg_database_size(datname))

FROM pg_tables

WHERE schemaname = ‘public’;