docs/RFCS/20171220_sql_role_based_access_control.md
Role-based access control is an enterprise feature.
We propose the addition of SQL roles to facilitate user management.
A role can be seen as a group containing any number of "entities" as members. An entity can be a user or another role.
Roles can have privileges on SQL objects. Any member of the role (directly a member, or indirectly by being a member-of-a-member) inherits all privileges of the role.
A new admin role will replace the current root user, with root being a member of admin.
Non-enterprise users will not be able to manipulate roles (create/delete/change membership), but
will retain all roles created using an enterprise license.
This will be implemented through the addition of a new role_members system table containing role-role and
user-role relationships. Role memberships are synchronized through descriptor versions, a new versions indicating
that a node should refresh role information.
Roles simplify the task of user management on a large database.
By granting database/table privileges to a role, users of a team can be more easily added to the system and granted all necessary privileges by simply adding them as members of the corresponding roles. The alternative would be granting privileges for all necessary objects, making it likely to forget some.
Managing privileges on a given object becomes easier: by changing the privileges granted to the role, all members of the role are updated implicitly.
We follow the postgresql use of roles:
The following are not in scope but should not be hindered by implementation of this RFC:
ALTER command (modify role attributes, rename role)See Future improvements for discussion of some of these.
Some terminology used in this RFC:
admin roleA ∈ B: user or role A is a member of role BA ∈ BA ∈ C ... ∈ B where ... is an arbitrary number of membershipsA role can be thought of as a group with any number of members belonging to the group. Roles have privileges on objects in the same way that users do. All members of the role inherit the privileges held by the role. This is done recursively.
For example:
employees has ALL privileges on table mydb.employee_datamarc is a member of employees (also written: marc ∈ employees)marc can now perform any operations on table mydb.employee_dataRoles and users follow a small set of rules:
marc exists, we cannot create a role marc)A ∈ B ∈ A or indirect: A ∈ B ∈ C ... ∈ A)ADMIN OPTION) can modify role membershipsadmin role)Cockroach administrators can create a new role called myrole using:
CREATE ROLE myrole
This fails if myrole exists either as a role or a user.
Granting privileges to a role is the same a granting privileges to a user. This syntax has not changed.
For example, granting SELECT privileges to the role myrole on a table consists of:
GRANT SELECT ON TABLE mydb.mytable TO myrole
Revoking privileges is also the same as before:
REVOKE SELECT ON TABLE mydb.mytable FROM myrole
Modification of role membership requires one of the following:
admin role)WITH ADMIN OPTION)A user marc can be added to the role myrole without role admin permissions using:
GRANT myrole TO marc
marc is now a "regular" member of the role, inheriting all role privileges. However, marc
is not allowed to modify role membership.
To allow a user to modify role membership, we can use WITH ADMIN OPTION:
GRANT myrole TO marc WITH ADMIN OPTION
marc is now a member of the role and is allowed to modify role membership.
Cockroach administrators can delete a role called myrole using:
DROP ROLE myrole
This fails if myrole does not exist. We can instead use:
DROP ROLE IF EXISTS myrole
Dropping a role will fail if it still has direct privileges on database objects (privileges through membership in other roles does not trigger an error).
Any role relationships involving this role will be removed automatically.
This applies to dropping users as well: dropping user A when A ∈ myrole will automatically remove
the membership.
We can get a simple list of all roles in the cluster by running:
SHOW ROLES
+--------------+
| Role |
+--------------+
| admin |
| myrole |
| myotherrole |
+--------------+
A list of members can be found using:
SHOW GRANTS ON ROLE myrole
+--------+----------- +-------+
| Role | User/Role | Admin |
+--------+------------+-------+
| myrole | marc | YES |
| myrole | other | NO |
+--------+------------+-------+
We will also allow the following variants:
SHOW GRANTS ON ROLESHOW GRANTS ON ROLE FOR marcSHOW GRANTS ON ROLE myroleSHOW GRANTS ON ROLE myrole FOR marcBefore this RFC, there is a single administrative user in a cockroach cluster: the user root.
We propose to add a default role: admin with a single user: root.
root will have admin privileges on the admin role, allowing addition/removal of other users.
The admin role cannot be deleted, and cannot be empty. The special root user cannot be dropped or removed
from the admin role.
All operations that previously checked for the root user will now check for membership in the admin role.
Clusters created before the existence of roles will have a new admin role created automatically and privileges
for it added where root currently has privileges. root will keep its individual privileges to allow downgrades
to older versions.
Manipulation of roles is an enterprise feature.
Without a valid enterprise license, the following are not allowed:
CREATE ROLE ...)DROP ROLE ...)GRANT myrole TO ...)REVOKE myrole TO ...)The following will function without an enterprise license:
This restriction leaves non-enterprise users with a fully-functioning product but without the ease of management provided by roles.
Any modifications to the SQL code must keep in mind:
A number of concepts are detailed in the Guide-level explanation and not repeated in this section.
We propose the modification of the users table to store roles, and the addition of a new table to store role memberships.
Since roles and users share the same namespace, it is simplest to alter the system.users table to
include roles.
The schema for the users table becomes:
CREATE TABLE system.users (
username STRING PRIMARY KEY,
"hashedPassword" BYTES,
isRole BOOL,
(INDEX isRole)
);`
Depending on the complexity of migration, we may be able to rename the username field and even the table
to rolename and system.roles to reflect the fact that users are really just roles.
isRole denotes whether the corresponding name is a user or a role. Roles cannot log in or have passwords.
The role_members stores the list of all role memberships. This only stores direct relationships:
CREATE TABLE system.role_members (
role STRING,
member STRING,
isAdmin BOOL,
PRIMARY KEY (role, member),
INDEX (member)
);
For alternate representations Internal representation of memberships.
We propose some new and some modified SQL statements, all mimicking the corresponding statements in PostgreSQL (see Related resources).
The new statements all operate on the system.users and system.role_members tables.
CREATE ROLE [ IF NOT EXISTS ] rolename
rolename. Fails if a role or user by that name exists.DROP ROLE [ IF EXISTS ] rolename
rolename. Fails if it does not exist, unless IF EXISTS is specified.The GRANT and REVOKE statements used to grant privileges to a user will accept a role. No syntax
changes are necessary.
An enterprise license is not required.
GRANT rolename TO name [ WITH ADMIN OPTION ]
name as a member of rolename. Member is a role admin if WITH ADMIN OPTION is specified.
rolename or name does not exist, or if this creates a membership loop.WITH ADMIN option enabled, but never disabled.ADMIN OPTION is inherited. eg: if A ∈ B ∈ C and B has ADMIN OPTION on C, then A has ADMIN OPTION on C (but not necessarily on B).REVOKE [ ADMIN OPTION FOR ] rolename FROM name
name as a member of rolename. Member admin setting is removed if ADMIN OPTION FOR is specified, but the membership remains.
Fails if either rolename or name does not exist. Succeeds if the membership does not exist.SHOW ROLES
SHOW GRANTS ON ROLE [rolename] [ FOR name ]
Where rolename can be one or more role and name can be one of more user or role. An unspecified rolename
or name returns all roles or members.
TODO(mberhault): should we have an option to pick whether to show direct/indirect/all memberships?
Modifying role memberships is done through normal operations on the system tables.
However, we set UpVersion on the role_members table to trigger a descriptor version upgrade,
notifying lease holders that a refresh is needed.
Expanding role memberships is necessary to perform permission checks. Given how frequently these need to be
done, we propose a per-node cache of role memberships with refreshes triggered by increases of the
role_members table descriptor version.
The process for a permission check is:
role_members table.role_members table is performed at the table descriptor's ModificationTimerole_members tablerole_members table descriptor version changes (indicating a change to role memberships), refresh the roles from the tableThis insures that we can keep role membership information reasonably current with refreshes being forced only when changes occur.
The role cache can be a simple LRU cache with a maximum size of a few MB, but this limit should be configurable. Given the likely small size of the cached entries, we can expire them after some amount of inactivity rather than constantly refreshing them.
Role membership expansion can be pre-computed (see Internal representation of memberships) to allow for cheaper lookup at the time of permission checks.
Permission checks currently consist of calls to one of:
func (p *planner) CheckPrivilege(descriptor sqlbase.DescriptorProto, privilege privilege.Kind) error
func (p *planner) CheckAnyPrivilege(descriptor sqlbase.DescriptorProto) error
func (p *planner) RequireAdminRole(action string) error
func (p *planner) HasAdminRole() (bool, error)
func (p *planner) MemberOfWithAdminOption(member string) (map[string]bool, error)
All methods operate on p.session.User.
These functions check a number of things for the specified user:
root or node userIn a world with roles, permission checks are performed against the set of names consisting of:
Checking permissions becomes checking that any of the names in the set have the required permissions.
For root checks, this is hard-coded to checking that the expanded list includes the admin role.
The admin role is a special role corresponding to the current root user.
It is checked everywhere we currently check if user == security.RootUser.
It is created at cluster-creation time (or migration) and initialized with the root
user as a member.
The admin role cannot be dropped and cannot be empty (member removal of the last member
will fail).
Migrations from older versions will perform the following tasks:
isRole column to the system.users tableadmin role to the system.users tablesystem.role_members tableroot userroot as a member of admin with isAdmin = trueroot userNotes:
root privileges to allow for binary downgrade.admin exists, log.Fatal the migration and ask the user to downgrade/drop the user/try again.See Non-enterprise users for actions requiring an enterprise license.
All prohibited actions are in the "administrative" category of operations (role management), and not in the path of data-access SQL queries.
This makes the enterprise license check relatively painless.
It is currently possible to manipulate the system tables directly, bypassing role manipulation statements. Closing this loop-hole is out of scope for this document. See tracking issue #19277.
We must populate some virtual tables with role information.
See PostgresSQL doc
The existing pg_roles table is currently populated for all users:
h.UserOid(username), // oid
tree.NewDName(username), // rolname
tree.MakeDBool(isRoot), // rolsuper
tree.MakeDBool(false), // rolinherit
tree.MakeDBool(isRoot), // rolcreaterole
tree.MakeDBool(isRoot), // rolcreatedb
tree.MakeDBool(false), // rolcatupdate
tree.MakeDBool(true), // rolcanlogin
negOneVal, // rolconnlimit
tree.NewDString("********"), // rolpassword
tree.DNull, // rolvaliduntil
tree.NewDString("{}"), // rolconfig
A similar entry must be created for each existing role with the following changes:
isRoot is replaced with the condition rolename == adminrolcanlogin is always falseTODO(mberhault): determine if members that inherit superuser roles are listed here with the inherited attributes
(eg: if marc ∈ admin, does marc have rolsuper set to true?).
See PostgresSQL doc
The new virtual table pg_auth_members must be added. It describes role memberships:
CREATE TABLE pg_catalog.pg_auth_members (
roleid OID, ; ID of the role that has a member
member OID, ; ID of the member
grantor OID, ; ID of the role that granted this membership
admin_options BOOL ; true if member can grant membership in roleid to others
)
The grantor may or may not be a bogus value.
TODO(mberhault): determine PostgreSQL logic for grantor (especially if original OID is dropped).
See PostgreSQL doc.
The pg_authid virtual table is not currently implemented in Cockroach.
It contains sensitive information (passwords) and PostgreSQL clearly states:
Since this catalog contains passwords, it must not be publicly readable. pg_roles is a publicly readable view on pg_authid that blanks out the password field.
We propose to keep in unimplemented, users should query pg_roles and pg_auth_members instead.
There are multiple relevant tables in information_schema that must be changed or added.
administrable_role_authorizations: roles the current user has the ADMIN OPTION on.applicable_roles: roles whose privileges the current user can use.enabled_roles: roles the current use is a member of (direct or indirect).role_table_grants: similar to the existing table_privileges.TODO(mberhault): determine exactly which are needed and flesh out details.
Roles themselves follow the PostgreSQL behavior and any drawbacks there are intended for compatibility.
There are some minor drawbacks to this implementation.
While allowing for cheaper role expansions, the use of leases to signal role membership changes subverts the original purpose.
If a more appropriate solution is found, it can replace the current mechanism at a later date.
The role manipulation code is very similar to user manipulation (it's just a field inside system.users), making
it preferable to reuse most of the code. We also need the role logic (expand roles, check privileges, etc..) to
keep working in non-enterprise mode.
These make it tricky to place all the role-related code in pkg/ccl. We need to figure out exactly which code
can be kept separate.
The current proposal requires an iterative lookup to discover all memberships for a user. This is the most naive way of representing memberships and maps well to the conceptual representation of roles.
This makes role manipulation cheap, with role lookups bearing the brunt of the cost.
Some alternatives include:
Expanding role membership into the roles table (each role contains the list of all roles its a member of):
Expanding role membership into database/table descriptors (descriptor contains all users with privileges):
Dual tables: one for direct role membership information, one for expanded membership information:
ADMIN OPTION field and would be queried for role manipulationPrivileges are currently stored in database/table descriptors. To properly integrate with the role change control, they need to be moved to their own table.
We do not currently have attributes on users or roles.
While we do not propose to implement all of them, some of them would be useful. For instance:
SUPERUSER: database superuserCREATEDB: can create databasesCREATEROLE: can manipulate all roles (except the superuser roles)INHERIT|NOINHERIT: roles privileges are inherited (or not)Implementing additional attributes is out of scope for this RFC. If added, this can be done for
both users and roles by adding the corresponding columns to the system.users table.
Some SQL statements can be enhanced to improve usability. For example:
Adding members firstmember and secondmember when creating a new role myrole:
CREATE ROLE myrole ROLE firstmember, secondmember
Adding the new role myrole as a member of existing roles existingrole1 `existingrole2:
CREATE ROLE myrole IN ROLE existingrole1, existingrole2
Adding members firstadmin and secondadmin to the new role myrole with the ADMIN OPTION set:
CREATE ROLE myrole ADMIN firstadmin, secondadmin
At the very least, the admin UI should show role information (privileges on objects, memberships, etc...). Manipulation of roles may be a UI event.
Changing roles through the admin UI should be possible, but is currently gated on admin UI authentication.
Backup/restore currently does not restore privileges or users. Should it start doing so, roles must be restored as well.