Role Grant Access
Overview
The lightest of the three PostgreSQL patterns. At checkout the broker runs GRANT <role> TO <user>; at checkin it runs REVOKE <role> FROM <user>. The PostgreSQL user must already exist — only role membership changes. Use this for routine, role-defined data access (analysts, reporting, application accounts).
Before You Begin
- The Access Broker is deployed and connected
psqlon the broker host; connectivity on the default port5432- A service account with
ADMIN OPTIONon each role it manages
Environment Variables
| Variable | Notes |
|---|---|
BRITIVE_USER | Injected — requesting user’s email; username derived from the local-part (non-alphanumerics stripped) |
svc_user, svc_password | Service account credentials |
db_host, db_name | Connection details |
role_name | Role to grant/revoke |
Checkout / Checkin
Full scripts: postgres-access-checkout.sh · postgres-access-checkin.sh
# checkout
psql "host=$db_host dbname=$db_name user=$svc_user password=$svc_password" \
-c "GRANT \"$role_name\" TO \"$username\";"
# checkin
psql ... -c "REVOKE \"$role_name\" FROM \"$username\";"Configure in Britive
Create the permission
Resource Manager → Resource Type Permissions → New Permission. Language = Shell. Paste both scripts. Declare svc_user, svc_password, db_host, db_name, role_name (BRITIVE_USER is system-defined).
Create a profile and policy
Create a profile (e.g. 4h), set role_name, add the permission, and add a policy assigning members by tag.
Verify
-- as the user, during checkout
SELECT rolname FROM pg_roles r
JOIN pg_auth_members m ON m.roleid = r.oid
JOIN pg_roles u ON u.oid = m.member
WHERE u.rolname = current_user;
-- the granted role appears; after checkin it's goneTroubleshoot
| Symptom | Cause | Fix |
|---|---|---|
must have admin option on role | Service account lacks ADMIN OPTION | GRANT <role> TO <svc> WITH ADMIN OPTION |
role "<user>" does not exist | User not pre-created | This pattern requires an existing user; use DBA Access or Superuser Access to create one |
Next Steps
Last updated on