User Role Management

Tacnode offers a robust permission system that integrates seamlessly with PostgreSQL, providing users with detailed control over permissions for various database objects.

Users and Roles

The concepts of users and roles in the Tacnode database align with those in standard Postgres. They are categorized into Global User and Local User/Local Roles based on their scope, as shown in the figure below:

The figure above shows that a GlobalUser has global visibility and can be referenced across any database on the Tacnode cloud platform. In contrast, LocalUser and LocalRole are limited to visibility within their specific database. Unlike earlier database products, User and Role information is stored within each database, meaning there is no shared User or Role data between databases.

Below are the specific distinctions among Global User, Local User, and Local Role (refer to the User/Role Difference Table):

Attributes: Refer to the chapter Role Attributes

Functions: Refer to the chapter Role Management

Permissions: Refer to the chapter Permissions List

For ease of reference, roles will denote GlobalUser, LocalUser, and LocalRole unless stated otherwise.

Role Attributes

A database role may have several attributes that define its privileges and facilitate client authentication. Here are detailed descriptions of these attributes:

PASSWORD

A role with a password attribute typically corresponds to a user type. If the user has the CONNECT permission for the active database, they can log in.

• The GlobalUser password is stored within the platform service and cannot be modified via SQL.

SUPERUSER | NOSUPERUSER

The database's superuser attribute bypasses all privilege checks except login permissions. This is a risky privilege and should be used sparingly; performing most tasks in a non-superuser role is advisable. To create a new database superuser, execute CREATE ROLE name SUPERUSER. This action must be carried out by a role that already has superuser access.

CREATEDB | NOCREATEDB

A role needs explicit permission to create a database, excluding superusers who can avoid all permission checks. To establish this role, execute CREATE ROLE name CREATEDB.

CREATEROLE | NOCREATEROLE

A role must be explicitly granted the necessary privileges to create additional roles, except for superusers who can bypass all privilege checks. Use the CREATE ROLE name CREATEROLE to create a role with this privilege. A role granted CREATEROLE can also modify or drop other roles and manage membership by granting or revoking it. Modifications to a role include changes that can be made using ALTER ROLE, like updating a password. Changes also encompass alterations through the COMMENT and SECURITY LABEL commands.

However, CREATEROLE does not include the ability to create SUPERUSER roles or any control over existing SUPERUSER roles.

Because the CREATEROLE privilege allows a user to grant or revoke membership even in roles that do not yet have any access privileges, a CREATEROLE user can gain access privileges to every predefined role (see section Predefined Roles) in the system, including highly privileged roles.

Role Management

The roles in the database have inheritance capabilities. Authorization allows roles to grant other roles their capabilities (permission sets, inheritance relationships). The corresponding reverse operation is to revoke authorization.

• The User/Role Differences table indicates that GlobalUser cannot be authorized for others.

The following figure illustrates the role's authorization limitations:

• Limitation 1: GlobalUser can solely function as a leaf node, not a parent node.
• Limitation 2: A loop occurs once authorization is successful, making the node its ancestor.

Role Management

Verifying if the current role has the necessary permissions to execute the operation is essential when conducting role management tasks. The verification process is detailed as follows:

Operations	Permission Check
create user/role
drop user/role
alter user/role
grant role
revoke role

Create Users and Roles

You can create a role through the command CREATE ROLE. The creation command is as follows:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

option including: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE

Example:

Create a ROLE with SUPERUSER attribute

CREATE ROLE role1 WITH SUPERUSER;

Create a composite attribute ROLE

CREATE ROLE role1 WITH SUPERUSER CREATEDB CREATEROLE;

Create a LocalUser

The command CREATE USER is synonymous with CREATE ROLE. The key distinction lies in the optional Option, which allows for including a PASSWORD attribute to define the User's password. Here is an example:

CREATE USER localuser WITH PASSWORD 'warehouse';

Create a GlobalUser

To create a GlobalUser, use the command CREATE USER. Unlike with a LocalUser, you cannot set the PASSWORD attribute for the GlobalUser during creation. Here’s an example:

CREATE USER "document@tacnode.io" WITH SUPERUSER;

Deleting Users and Roles

The command DROP ROLE|USER removes a database role.

DROP USER [ IF EXISTS ] name [, ...]

Manage Users and Roles Attributes

The command ALTER ROLE|USER changes to a role's attributes.

ALTER ROLE myname WITH NOSUPERUSER;

Role Group

A role group consists of users and roles with similar characteristics. Users and roles within the same role group can inherit the permissions granted to them. Both LocalUser and LocalRole possess the group's capabilities. It is important to note that GlobalUser can only be part of a role group.

Grant

The command GRANT role_group TO role1, ... [WITH admin OPTION] allows you to assign a role to a role group. Here’s an example:

GRANT group_role TO other_role [WITH admin OPTION];

• WITH admin OPTION: Allows the role other_role to grant or revoke authorization for the role group_role.

Revoke

The command REVOKE role_group from role1 effectively eliminates role1 from role_group.

Default Role Group PUBLIC

PUBLIC is a default permission group representing all roles in the current database. Members of the PUBLIC role group cannot be managed through the GRANT and REVOKE commands.

Predefined Roles

The database includes a default set of predefined roles that grant access to specific, commonly needed privileged functions and information. Administrators, including those with CREATEROLE permissions, can assign these roles using GRANT to users within their environment or other roles, allowing them access to the designated functions and information.

The roles outlined in the table below may have changing permissions as new features are introduced. Administrators are advised to monitor the release notes for any updates in this area.

• pg_database_owner: This role denotes the current database owner logically and cannot be granted or revoked explicitly.

Permissions

Once an object is created, it is assigned an owner, typically the user who executed the creation statement. Generally, only the owner or a superuser can interact with the object initially. To enable other roles to access it, permissions must be granted.

There are various privileges available: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE. The specific privileges applicable to each object depend on its type, such as whether it is a table or a function. More details on these privileges are outlined below, and the following sections explain how to utilize them.

The ability to modify or delete an object is inherently tied to its ownership and cannot be granted or revoked in isolation.

Object Permissions Design

An object's owner can be changed using the correct ALTER command for its type.

ALTER TABLE table_name OWNER TO new_owner;

By role management, a role possessing the SUPERUSER attribute can always perform this action. In contrast, a standard role can only do so if it is the object's current owner (or part of the owning role) and belongs to the new owning role.

To assign permissions, utilize the GRANT command. For instance, if joe is an existing role and accounts is an existing table, you can grant permission to update the table like this:

GRANT UPDATE ON accounts TO joe [WITH grant OPTION];

• WITH grant OPTION: joe can continue granting UPDATE permissions to other users.

Using ALL instead of specific permissions will provide all permissions related to that object type.

A specific PUBLIC role, labeled "PUBLIC," allows permission to all roles within the database.

To withdraw previously granted permissions, utilize the command REVOKE:

REVOKE ALL ON accounts FROM PUBLIC;

Typically, only the object owner or a superuser can grant or revoke permissions on an object. Nevertheless, permissions can be issued with grant option to permit the recipient to pass those permissions to others. If the grant option is later removed, all permissions obtained from that recipient (either directly or via a grant chain) will also be revoked.

An object owner has the ability to withdraw their standard privileges, for example, to render the table read-only for themselves and others. However, owners are always considered to possess full grant options, enabling them to reissue their privileges anytime.

Permissions List

The valid permissions are as follows:

The Tacnode Database automatically assigns specific object privileges to PUBLIC upon creation. By default, PUBLIC does not receive any privileges for tables, table columns, sequences, or tablespaces. However, for other object types, PUBLIC is granted default privileges: CONNECT and TEMPORARY (to create temporary tables) at the database level. Object owners have the ability to REVOKE these defaults as well as specifically assigned privileges. To enhance security, it is advisable to issue the REVOKE command within the same transaction that creates the object, eliminating the opportunity for other users to access it during that time. Additionally, there are commands available to modify these default privilege settings.

ACL (Access Control List)

This table presents the single-letter abbreviations for the various permission types in ACL (access control list) values. These letters appear in the output of the psql commands provided below and in the ACL column of system catalogs.

ACL Rules

Every object in the database logs the relevant ACL entry for permission verification. The typical format for defining an ACL is as follows:

acl_item: Grantee=Privilege[*][Privilege[*]].../Grantor

Grantee: an empty field indicates a role designated for the system.

Privilege: A shorthand for permission; see the abbreviation column in "Table 2.3." When permission is marked with the '*' character, the grantee can extend that permission to others.

Grantor: The individual who assigns the permission. An empty designation indicates that the permission is granted by a role reserved for the system.

An ACL comprises a comprehensive list characterized as follows:

acl: {acl_item[,acl_item]...}

Examples:

{=rx/,public=rx/, joe=r*x*/owner, nick=rx/owner,owner=rxcd/owner}

In the example above, the object owner grants the rx permission to nick and joe. Additionally, joe has the authority to extend this permission further. It is important to note that owner=rxcd/owner demonstrates that the owner retains all default permissions as the object's creator, along with the permissions granted to others.

Grant

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]
    
GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH { ADMIN | } { OPTION | TRUE | FALSE } ]

Revoke

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
 
REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    FROM role_specification [, ...]
 
REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM role_specification [, ...]
 
REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    FROM role_specification [, ...]
 
REVOKE [ { ADMIN } OPTION FOR ]
    role_name [, ...] FROM role_specification [, ...]

Table Row-level Security

CREATE POLICY — define a new row-level security policy for a table

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

The CREATE POLICY command establishes a row-level security policy for a table. To make the policy effective, it is important to enable row-level security on the table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY.

A policy allows selecting, inserting, updating, or deleting rows corresponding to the defined policy expression. Existing rows in the table are evaluated against the specified expression using USING, while new rows intended for insertion or updates undergo checks against the WITH CHECK expression. If the USING expression evaluates to true for a row, that row becomes visible to the user; conversely, if it results in false or null, the row is hidden. Additionally, when a WITH CHECK expression yields true for a row, the row can be inserted or updated; an error arises if it yields false or null.

For INSERT, UPDATE, and MERGE statements, WITH CHECK expressions are executed after the trigger fires and before data modifications. Consequently, BEFORE ROW triggers can alter the data set for insertion, which impacts the outcomes of security policy checks. It is important to note that WITH CHECK expressions are executed before other constraints.

Policy names correspond to each table. Thus, a single policy name may apply to multiple tables, and suitable definitions may be offered for each.

For more information, see Row-Level Security.