Website database admin

[jrmerz]

Functionality overview:

Functional screenshots:

User view:

Table view:

Edit View:

[wrenaria]

@jrmerz A few questions --

1. Adding Users Can you provide some parameters on how users are added? It seems like you will need to support a mix of CAS and other. Do you need different fields for adding those different types of accounts? Like just the Kerberos username for UC Davis CAS and more fields for other? Which ones are required vs optional?

Example fields --

• Username
• Display name
• Password
• Email

Do users have any power over managing their own account (like changing their password or display name, deleting their account from a database)?

2. Roles Are roles customizable, or does read + write/execute cover the user needs?

[jrmerz]

@wrenaria

Accounts

A CAS username is (currently) all that's required to add a user to a database. Then there are two types of (human) user accounts for a pg farm database; normal and admin. So the only fields required to create an account are:

• CAS Username
• Account Type (should default to normal)

The admin account type is allowed to:

• Add/Remove other users
• Grant/Revoke user access to schema, tables, views, etc
• Login as the postgres user, ie the superuser account and preform any action on the database.

Allowing non-cas users will be a design TODO for later (probably after launch).

Passwords are handled by CAS.

Roles

Roles are more tricky. Postgres has a role system, where you can assign access to tables/views/functions to roles and then assign roles to users. However, normal user accounts are a role as well, and can be directly assigned access to tables/views/functions. Additionally, there are A LOT of different permissions for the various database types (tables/views/functions/sequences, etc) https://www.postgresql.org/docs/current/ddl-priv.html .

My approach. PG Farm has simplified commands so an admin can think in terms of granting read or read+write access to a single table/view/function or they can grant read or read+write access to ALL objects (again tables/views/functions) in a schema (databases have multiple schemas).

It's my goal to push researchers to think of user access on the schema level, to simplify their lives. ie jrmerz has access to database my-cool-database and read access to everything in schema foo.

*A final foot note. Say I grant jrmerz read access to schema foo using the PG Farm helper method described above. If a another user adds another table to schema foo, jrmerz will not have access to the new table unless the 'grant schema access' command is run again. This is a pattern/problem, I'm hoping we can help smooth out, but I have to great thoughts how to attack as of yet.