Database Roles

1Transact creates three PostgreSQL database roles to allow access to the 1Transact PostgreSQL database functionality:

  • sms_read - A role for users that are reading (or writing) versioned data from the database. This provides SELECT privilege to the required tables and views in sms_1transact, EXECUTE access to the functions that return information but do not modify the system and also EXECUTE on goto_* functions which create temporary per-session views in order to access the correct data.

    Note: Users that need to update versioned data will also need this role in addition to standard INSERT, UPDATE and DELETE privileges on the activated feature tables (renamed *_vm). This can be achieved by either granting INSERT, UPDATE and DELETE on the table before it is version-enabled, or by granting them on the renamed *_vm table after it has been version enabled.

  • sms_diff_views - Accessing the differences between two checkpoints is done by reading from a normal persistent view. These different views can be created in a different session and by a different user to the users that read from those views. For this reason there is a specific role for calling the function that creates those views.

  • sms_admin - This is the role that can perform the version maintenance tasks that will affect the database such as creating, deleting, and merging versions or creating and rolling back transactions.

The roles are cumulative and listed in increasing order of power so sms_admin includes all the capabilities of sms_read and sms_diff_views.

Note: A few powerful operations require a superuser role not covered by the roles above. This superuser is required in order to install, initialise, or reset the extension as well as activating and deactivating feature tables.

These roles are essentially group roles; they can not be used to log in to the database but can be assigned your user roles to give those users access to the functionality.

ClosedAssign a role to a user

To assign a role to your users, you can use the standard PostgreSQL statements, for example:

GRANT sms_read TO web_access_user1;

Functionality available per role

Functionality sms_read sms_diff_views sms_admin superuser
read versioned data and metadata
insert/update/delete versioned data

1

2

3

rollback version
rollback to checkpoint
merge version
create version
delete version
create checkpoint
activate table 4
deactivate table 5
create view of linear difference between checkpoints
compress checkpoints
compress version

Permissions on database objects per role

Database Object Type Privileges
sms_1transact schema

USAGE to sms_read
version table

SELECT to sms_read

ALL to sms_admin
checkpoint table

SELECT to sms_read

ALL to sms_admin
closure table

SELECT to sms_read

ALL to sms_admin
activated_tables table

SELECT to sms_read

ALL to sms_admin
get_last_ckpt function

EXECUTE to sms_read
goto_checkpoint function

EXECUTE to sms_read
get_current_version function

SELECT to sms_read
goto_version function

EXECUTE to sms_read
checkpoint_name_to_id function

EXECUTE to sms_read
create_linear_ difference_in_ checkpoints function

EXECUTE to sms_diff_views
check_ckpts_in_path function

EXECUTE to sms_diff_views
sms_last_checkpoint view

ALL to sms_admin
sms_current_version view

ALL to sms_admin
checkpoint_ckpt_seq sequence

USAGE to sms_admin
initialise function

Need to be superuser
truncate function

Need to be superuser
create_checkpoint function

EXECUTE to sms_admin
create_version function

EXECUTE to sms_admin
delete_version function

EXECUTE to sms_admin
create_view function

Need to be superuser
activate_table function

Need to be superuser
deactivate_table function

Need to be superuser
merge_version function

EXECUTE to sms_admin
compress_checkpoints function

EXECUTE to sms_admin
compress_version function

EXECUTE to sms_admin
rollback_version function

EXECUTE to sms_admin
rollback_to_checkpoint function

EXECUTE to sms_admin