GRANT
and REVOKE
to manage privileges on database objects. Privileges can be assigned to a role for tables, schemas and database.
Privilege | Description | Database Objects |
---|---|---|
CONNECT | Allows a role to connect to the database | database |
USAGE | Allows access to objects within the schema, provided the objects’ own privilege requirements are also met | schema |
CREATE | Allows creating tables and types in a schema. CREATE INDEX requires ownership of the table and both CREATE and USAGE privilege on the schema | schema |
SELECT | Allows selecting data from a table | table |
INSERT | Allows inserting data into a table | table |
UPDATE | Allows updating data in a table | table |
DELETE | Allows deleting and truncating data from a table Allows | table |
USAGE
privilege is required to see what objects (tables / types / indices) exist inside the schema. Unless the user has such a privilege, they will get does not exist
error on accessing any objects in the schema, even if the object exists. It is also required to look up tables using search_path
. In case of objects listed by tables in information_schema.tables
and pg_catalog.pg_class
schemas but also other metatables user can see these objects when they either have USAGE
on schema or any grant on the object itself.
The only exception to this rule is information_schema.role_table_grants
table - if a user has any grant on a table, but does not have USAGE
grant to this table, on parent schema, this grant will still be visible in the information_schema.role_table_grants
table.
ALL PRIVILEGES
alias representing all available privileges for a given database object.
By default, every new role has CONNECT
privilege on the database and USAGE
privilege on the default public
schema.
See the Default Privileges section for details.pg_authid
and pg_shadow
tables from pg_catalog
and can access resources in oxla_internal
pg_catalog
, information_schema
, system
pg_catalog
, information_schema
, system
, oxla_internal
id
: role IDdatabase
: database nameschema
: schema nametable
: table nameprivilege
: privilege granted on a given objectprivilege
: one or more privileges from the available listobject_name
: name of the object on which the privileges are being grantedrole_name
: name of the role to which the privileges are being grantedTABLE
, SCHEMA
or DATABASE
keyword is omitted, TABLE
keyword is assumed.privilege
: one or more privileges from the list of available privilegesobject_name
: name of the object on which the privileges are being revokedrole_name
: name of the role from which the privileges are being revokedTABLE
, SCHEMA
or DATABASE
keyword is omitted, TABLE
keyword is assumed.CONNECT
: privilege on the default databaseUSAGE
: privilege on the default public
schemaCONNECT
privilege cannot connect to the databaseUSAGE
privilege cannot access the default public
schema