GRANT

Name

GRANT -- Grants access privilege to a user, a group or all users

GRANT privilege [, ...] ON object [, ...] TO { PUBLIC | GROUP group | username }

Inputs

privilege 

The possible privileges are:

SELECT

Access all of the columns of a specific table/view.

INSERT

Insert data into all columns of a specific table.

UPDATE

Update all columns of a specific table.

DELETE

Delete rows from a specific table.

RULE

Define rules on the table/view (See CREATE RULE statement).

ALL

Grant all privileges.

object 

The name of an object to which to grant access. The possible objects are:

• table
• view
• sequence

PUBLIC

A short form representing all users.

GROUP group 

A group to whom to grant privileges.

username 

The name of a user to whom to grant privileges. PUBLIC is a short form representing all users.

Outputs

CHANGE 

Message returned if successful.

ERROR: ChangeAcl: class "object " not found

Message returned if the specified object is not available or if it is impossible to give privileges to the specified group or users.

Description

GRANT allows the creator of an object to give specific permissions to all users (PUBLIC) or to a certain user or group. Users other than the creator don't have any access permission unless the creator GRANTs permissions, after the object is created.

Once a user has a privilege on an object, he is enabled to exercise that privilege. There is no need to GRANT privileges to the creator of an object, the creator automatically holds ALL privileges, and can also drop the object.

Notes

Currently, to grant privileges in Postgres to only a few columns, you must create a view having desired columns and then grant privileges to that view.

Use psql \z for further information about permissions on existing objects:

 

               Database    = lusitania
           +------------------+---------------------------------------------+
           |  Relation        |        Grant/Revoke Permissions             |
           +------------------+---------------------------------------------+
           | mytable          | {"=rw","miriam=arwR","group todos=rw"}      |
           +------------------+---------------------------------------------+
           Legend:
                 uname=arwR -- privileges granted to a user
           group gname=arwR -- privileges granted to a GROUP
                      =arwR -- privileges granted to PUBLIC
                          r -- SELECT
                          w -- UPDATE/DELETE
                          a -- INSERT
                          R -- RULE
                       arwR -- ALL  

Refer to REVOKE statements to revoke access privileges.

Usage

Grant insert privilege to all users on table films:

 

        GRANT INSERT ON films TO PUBLIC;  

Grant all privileges to user manuel on view kinds:

 

        GRANT ALL ON kinds TO manuel;   

Compatibility

SQL92

The SQL92 syntax for GRANT allows setting privileges for individual columns within a table, and allows setting a privilege to grant the same privileges to others:

 

        GRANT privilege [, ...] 
        ON object [ ( column [, ...] ) ] [, ...] 
        TO {  PUBLIC | username [, ...] }  
        [ WITH GRANT OPTION ]  

Fields are compatible with those in the Postgres implementation, with the following additions:

privilege

SQL92 permits additional privileges to be specified:

SELECT

 

REFERENCES

Allowed to reference some or all of the columns of a specific table/view in integrity constraints.

USAGE

Allowed to use a domain, character set, collation or translation. If an object specifies anything other than a table/view, privilege must specify only USAGE.

object

 

[ TABLE table ]

SQL92 allows the additional non-functional keyword TABLE.

CHARACTER SET

Allowed to use the specified character set.

COLLATION

Allowed to use the specified collation sequence.

TRANSLATION

Allowed to use the specified character set translation.

DOMAIN

Allowed to use the specified domain.

WITH GRANT OPTION

Allowed to grant the same privilege to others.