GRANT

Define access permissions.

Summary

GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |
TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }
    ON { [TABLE] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO { role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { {CREATE | CONNECT | TEMPORARY | TEMP} [, ...] | ALL
[PRIVILEGES] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [PRIVILEGES] }
    ON { FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...]
] ) [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [PRIVILEGES] }
    ON LANGUAGE lang_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [PRIVILEGES] }
    ON SCHEMA schema_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [PRIVILEGES] }
    ON TABLESPACE tablespace_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT parent_role [, ...]
    TO member_role [, ...] [WITH ADMIN OPTION]

GRANT { SELECT | INSERT | ALL [PRIVILEGES] }
    ON PROTOCOL protocolname
    TO username

describe

The YMatrix database unifies the concepts of users and groups into an entity called roles. Therefore, it is not necessary to use the keyword GROUP to identify whether the grantee is a user or a group. GROUP is still allowed in the command, but this is a distracting word.

The GRANT command has two basic variants: one grants privileges to database objects (tables, columns, views, external tables, sequences, databases, external data wrappers, external servers, functions, procedural languages, schemas, or tablespaces), and grants membership in a role.

GRANT on database object

This variant of the GRANT command grants a specific privilege of a database object to one or more roles. These privileges will be added to the granted privileges, if any.

There is also an option to grant privileges to all objects of the same type in one or more schemas. Currently only tables, sequences, and functions support this functionality (but note that ALL TABLES is considered to include views and external tables).

The keyword PUBLIC means that privileges are granted to all roles, including those that may be created later. PUBLIC can be considered as an implicit definition of a group-level role that always contains all roles. Any particular role will have the sum of the privileges granted to it directly, the privileges granted to any roles it currently belongs to, and the privileges granted to PUBLIC.

If WITH GRANT OPTION is specified, the privileged recipients can in turn grant it to others. Without the grant option, the receiver will not be able to do this. Grant options cannot be granted to PUBLIC.

There is no need to grant privileges to the owner of the object (usually the role that created the object) because the owner has all privileges by default. (However, owners may choose to revoke certain privileges of themselves for security reasons.)

The right to delete an object or change its definition in any way is not considered a grantable privilege; it is inherent to the owner and cannot be granted or revoked. (However, a similar effect can be achieved by granting or revoking membership of a role that owns an object; see below.) The owner also implicitly owns all grant options for that object.

The YMatrix database grants the default privileges of certain types of objects to PUBLIC. By default, permissions on tables, table columns, sequences, external data wrappers, external servers, large objects, schemas, or tablespaces are not granted to PUBLIC. For other types of objects, the default privileges granted to PUBLIC are as follows:

CONNECT and TEMPORARY (create temporary tables) privileges for the database,
EXECUTE privileges for functions
USAGE privileges for language and data types (including domains).

Of course, object owners can REVOKE default privileges and explicitly granted privileges. (For maximum security, execute REVOKE in the same transaction where the object was created; therefore, there is no time window for other users to use the object.)

GRANT on the character

This variant of the GRANT command grants membership of a role to one or more other roles. Membership of a role is significant because it can convey the privileges granted to a role to each member.

If WITH ADMIN OPTION is specified, members can grant membership to others in turn or revoke membership for that role. Without the admin option, the average user will not be able to do this. A role is not considered to have the WITH ADMIN OPTION itself, but it can grant or revoke membership from a database session where the session user matches the role. Database Superuser can grant or revoke membership to anyone in any role. A role with CREATEROLE privileges can grant or revoke membership for any role that is not a superuser.

Unlike with privileged cases, membership of a role cannot be granted to PUBLIC.

GRANT on the protocol

You can also use the GRANT command to specify which users can access trusted protocols. (If the protocol is not trusted, you cannot grant any other user permissions to use it to read or write data.)

To allow users to create readable external tables using trusted protocols:

GRANT SELECT ON PROTOCOL protocolname TO username

To allow users to create writable external tables using trusted protocols:

GRANT INSERT ON PROTOCOL protocolname TO username

To allow users to create readable and writable external tables using trusted protocols:

GRANT ALL ON PROTOCOL protocolname TO username

You can also use this command to grant users permission to create and use s3 and pxf external tables. However, external tables of types http, https, gpfdist and gpfdists are implemented inside the YMatrix database, not as custom protocols. For these types, use the CREATE ROLE or ALTER ROLE command to set the CREATEEXTTABLE or NOCREATEEXTTABLE attribute for each user.

Parameters

SELECT

Allows SELECT from any column of a specified table, view, or sequence or from a specific column listed. COPY TO is also allowed. This privilege is also required to reference existing column values in UPDATE or DELETE.

INSERT

Allows INSERT to the specified table. If a specific column is listed, you can only specify those columns in the INSERT command (other columns will receive default values). COPY FROM is also allowed.

UPDATE

Allows UPDATE to specify any column or specific columns of the table. SELECT ... FOR UPDATE and SELECT ... FOR SHARE also requires this privilege (and SELECT privilege) on at least one column. For sequences, this privilege allows the use of nextval() and setval() functions.

DELETE

Allows DELETE a row from the specified table.

REFERENCES

Although the YMatrix database does not currently support foreign key constraints, this keyword is still acceptable. To create a foreign key constraint, you must have this privilege on both the referenced column and the referenced column. Privileges can be granted for all or only specific columns of a table.

TRIGGER

Allows the creation of triggers on the specified table. Note: The YMatrix database does not support triggers.

TRUNCATE

Allows TRUNCATE for all rows in the specified table.

CREATE

For databases, allow creating new schemas in the database. For schema, it is allowed to create new objects within schema. To rename an existing object, you must have the object and have this privilege on the included schema. For table spaces, it is allowed to create tables and indexes within the table space, and allows to create databases that have table spaces as their default table spaces. (Note that revoking this privilege does not change the location of an existing object.)

CONNECT

Allows users to connect to the specified database. This privilege is checked when the connection is started (except for any restrictions imposed by pg_hba.conf).

TEMPORARY

TEMP

Allows temporary tables to be created when using the database.

EXECUTE

Allows the use of specified functions and any operators implemented on top of that function. This is the only privileged type that works for a function. (This syntax also applies to aggregate functions.)

USAGE

For procedural languages, it is allowed to use the specified language to create functions for that language. This is the only privilege type that applies to procedural languages.
For schema, access to the objects contained in the specified schema is allowed (assuming that the object's own privilege requirements are also met). Essentially, this allows the grantee to look up objects in the schema.
For sequences, this privilege allows the use of currval() and nextval() functions. For types and fields, this privilege allows the use of types or fields when creating tables, functions, and other schema objects. (Note that it does not control the general "usage" of types, such as the value of a type that appears in a query. It only prevents the creation of objects that depend on types. The main purpose of the privilege is to control which users create dependencies based on the type, which may prevent the owner from changing the type later.)
For external data wrappers, this privilege allows the grantee to create a new server using that external data wrapper. For the server, this privilege enables the grantee to use the server to create external tables, and can also create, change, or delete user maps of their own users associated with the server.

ALL PRIVILEGES

Grant all available privileges at once. The PRIVILEGES keyword is optional in the YMatrix database despite strict SQL requirements.

PUBLIC

Special group-level roles, indicating that privileges are granted to all roles, including those that may be created later.

WITH GRANT OPTION

The recipient of privilege can in turn grant it to others.

WITH ADMIN OPTION

Members of roles can in turn grant membership to other people.

Note

If the user has that privilege for a specific column or the entire table, the user can perform SELECT, INSERT and other operations on that column. Granting privileges at the table level and then revoking it a column is not as you would expect: Table-level grants are not affected by column-level operations.

Database superusers can access all objects regardless of object privilege settings. One exception to this object is the view object. Access to tables referenced in a view is determined by the permissions of the view owner (not the current user, even if the current user is a superuser).

If the superuser chooses to execute the GRANT or REVOKE command, the execution of the command is as if it was executed by the owner of the affected object. In particular, the privileges granted by such commands appear to have been granted by the object owner. For role membership, membership appears to have been granted by the role that contains it.

GRANT and REVOKE can also be done by not the owner of the affected object, but require members of the role that owns the object, or members of the role that owns the WITH GRANT OPTION privileges to the object. In this case, the privilege will be recorded as being granted by a role that actually owns the object or has the WITH GRANT OPTION privilege.

Granting a table permission does not automatically extend the permission to any sequence used by the table, including those bound to the SERIAL column. The permissions of the sequence must be set separately.

The GRANT command cannot be used to set privileges for protocol files, gpfdist, or gpfdists. These protocols are implemented inside the YMatrix database. Instead, use the CREATE ROLE or ALTER ROLE command to set the CREATEEXTTABLE property of the role.

Use the \dp meta command of psql to get information about existing privileges for tables and columns. You can also use other \d meta commands to display privileges for non-table objects.

Example

Grant insert permissions to all roles on the table mytable:

GRANT INSERT ON mytable TO PUBLIC;

Grant all available privileges to the roles in the topten view sally. Note that if the above is indeed executed by the superuser or the owner of the topten, all privileges will be granted, and when executed by others it will only grant those permissions that grant roles with the grant option.

GRANT ALL PRIVILEGES ON topten TO sally;

Grant membership of role admins to user joe:

GRANT admins TO joe;

compatibility

The PRIVILEGES keyword is required in the SQL standard, but is optional in the YMatrix database. The SQL standard does not support setting privileges on multiple objects per command.

The YMatrix database allows object owners to revoke their own ordinary privileges: For example, a table owner can make the table read-only to itself by revoking its own INSERT, UPDATE, DELETE, and TRUNCATE privileges. According to SQL standards, this is not possible. YMatrix databases treat owner privileges as granted to owners by the owner; therefore they can also revoke them. In the SQL standard, the owner's privilege is granted by the assumed system entity.

The SQL standard provides USAGE privileges for other types of objects: character sets, collations, translations.

In the SQL standard, sequences only have USAGE privileges, which control the use of NEXT VALUE FOR expressions, which are equivalent to nextval functions in the YMatrix database. Sequence privileges SELECT and UPDATE are YMatrix database extensions. Applying sequence USAGE privileges to currval functions is also a YMatrix database extension (and so is the function itself).

The privileges of databases, tablespaces, schemas and languages are YMatrix database extensions.