feat: support SET ROLE command

[neverchanje]

Is your feature request related to a problem? Please describe.

I wish we can add more e2e tests to our access control mechanism, but it would not be doable if without switching roles in the same session.

https://www.postgresql.org/docs/current/sql-set-role.html The command SET ROLE can allow us to switch to non-root users in the same session. Since it's only for testing purposes, we can only allow superusers to SET ROLE to other roles.

With SET ROLE, we can write tests such as granting a few privileges to a user and checking if it's indeed granted for the corresponding operations.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

@yezizp2012 Please share your thoughts if you don't think this feature is necessary..

[yezizp2012]

Actually I was think about to support some Access Privilege Inquiry Functions to achieve that. For example:

has_table_privilege ( [ user name or oid, ] table text or oid, privilege text ) → boolean Does user have privilege for table? Allowable privilege types are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.

The advantage is that we only need to check whether the privilege is granted or revoked, and not to do some real DDLs to verify it.

[neverchanje]

Thanks. has_table_privilege is orthogonal to SET ROLE. But I agree that it's more useful for testing.

[github-actions[bot]]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.