Role‐Based Authentication Procedure for RDBMS Ireland Region

Objective:

Implement a method of role-based authentication for the Ireland DB by identifying and granting admin privileges only to the necessary users, and removing them from others.

Requirements:

1. Access to the Ireland DB instance: at least one superuser or root-level access.
2. List of current users and their roles: for auditing and validation.
3. Stakeholders or team leads: verifying user roles and validating admin needs.
4. Policy or standards: any security guidelines or company policy for database access control.

Plan:

pg_roles is a predefined system catalog view. It is available in every PostgreSQL database by default. It’s a read-only view, so you can query it but not modify it directly.

Column Name	Description
rolname	Name of the role (user/group)
rolsuper	Boolean - Is the role a superuser?
rolcreaterole	Can the role create other roles?
rolcreatedb	Can the role create databases?
rolcanlogin	Can the role log in?
rolreplication	Can the role initiate replication?
rolbypassrls	Can bypass row-level security policies?

1. Export Current User Roles: We need a baseline to know who currently has access and assess if it's appropriate. Query the Ireland DB to get a list of all users and their current roles (especially those with admin access). -- List all roles and their attributes SELECT rolname AS role_name, rolsuper AS is_superuser, rolcreaterole AS can_create_roles, rolcreatedb AS can_create_db, rolreplication AS replication, rolbypassrls AS bypass_rls FROM pg_roles ORDER BY rolname; One way, PgAdmin, after getting results from the query tool, click download.

---