User permission control

YMatrix adopts role-based user permission management to simplify authorization management of permissions. With the help of role mechanism, when authorizing a group of users with the same permissions, there is no need to authorize these users one by one. Just grant the permissions to the role and then grant the role to this group of users. Just manage the roles to manage the permissions of this group of users.

A role can be considered as a database user, or a group of database users. Role and user are two objects with the same difference, the only difference is whether there is LOGIN permission in the default value of the role at creation time. A role with LOGIN permissions can be considered a user. A role can be given to a user so that the user has all permissions in the role. A user can also belong to different roles and have permissions to different roles. Roles can also have database objects (such as tables, schemas, sequences) and can assign permissions on these objects to other roles. The specified permissions of a specific data object can be authorized to the user through the GRANT&REVOKE statement.

ACL permission abbreviation

Permissions	Abbreviation	Applicable object types
SELECT	r ("read")	LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT	a ("Supplement")	TABLE, table column
UPDATE	w ("write")	LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE, table column
TRIGGER	t	TABLE
CREATE	C	DATABASE, SCHEMA, TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

Access permission summary

Object Type	All Permissions	Default PUBLIC Permissions	psql Command
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxt	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

Example

User miriam creates table mytable and executes the following set of authorization statements:

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

Then the ACL information on this table is as follows:

\dp mytable
                                Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-−-−-−-−-−-−-−-−-−-+-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-
public | mytable | table | miriam=arwdDxt/miriam+| col1: +|
      | | | =r/miriam +| miriam_rw=rw/miriam |
      | | | admin=arw/miriam | |