​
Overview

Oxla supports GRANT and REVOKE to manage privileges on database objects. Privileges can be assigned to a role for tables, schemas and database.

​
Available Privileges

In the table below, you can read about all available privileges:

PrivilegeDescriptionDatabase Objects
CONNECTAllows a role to connect to the databasedatabase
USAGEAllows access to objects within the schema, provided the objects’ own privilege requirements are also metschema
CREATEAllows creating tables and types in a schema. CREATE INDEX requires ownership of the table and both CREATE and USAGE privilege on the schemaschema
SELECTAllows selecting data from a tabletable
INSERTAllows inserting data into a tabletable
UPDATEAllows updating data in a tabletable
DELETEAllows deleting and truncating data from a table Allowstable

USAGE Privilege Essentials

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.

There is an 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.

Schemas and Tables

  • Only superuser can read pg_authid and pg_shadow tables from pg_catalogand can access resources in oxla_internal
  • Anyone can read other resources in: pg_catalog, information_schema, system
  • No one can modify: pg_catalog, information_schema, system, oxla_internal

​
Checking Privileges

To check privileges on tables, database or schemas, a superuser needs to execute the following commands:

  • Table privileges

    SELECT * FROM oxla_internal.oxla_role_table_grants;

    Example output:

    id | grantor | database | schema |      table      | privilege 
----+---------+----------+--------+-----------------+-----------
  1 |       1 | oxla     | public | data_types_demo | INSERT
  1 |       1 | oxla     | public | data_types_demo | DELETE

  • Database privileges

    SELECT * FROM oxla_internal.oxla_role_db_grants;

    Example output:

    id | grantor | database | privilege 
----+---------+----------+-----------
  1 |       1 | oxla     | CONNECT

  • Schema privileges

    SELECT * FROM oxla_internal.oxla_role_ns_grants;

    Example output:

    id  | grantor | database | schema | privilege 
----+---------+----------+--------+-----------
  1 |       1 | oxla     | public | USAGE

Here’s the breakdown of the above outputs:

  • id: role ID
  • database: database name
  • schema: schema name
  • table: table name
  • privilege: privilege granted on a given object

​
Granting Privilege

To grant privileges, execute the following command:

GRANT privilege [, privilege] ON [ TABLE | SCHEMA | DATABASE ] object_name TO role_name;

where:

  • privilege: one or more privileges from the available list
  • object_name: name of the object on which the privileges are being granted
  • role_name: name of the role to which the privileges are being granted
If TABLE, SCHEMA or DATABASE keyword is omitted, TABLE keyword is assumed.

​
Revoking Privileges

Here’s the code that needs to be run, in order to revoke a privilege:

REVOKE privilege [, privilege] ON [ TABLE | SCHEMA | DATABASE ] object_name FROM role_name;

where:

  • privilege: one or more privileges from the list of available privileges
  • object_name: name of the object on which the privileges are being revoked
  • role_name: name of the role from which the privileges are being revoked
If TABLE, SCHEMA or DATABASE keyword is omitted, TABLE keyword is assumed.

​
Default Privileges

By default, every created role has the following privileges:

  • CONNECT: privilege on the default database
  • USAGE: privilege on the default public schema

Default privileges can be revoked at any time with the following query:

REVOKE CONNECT ON DATABASE oxla FROM role_name;
REVOKE USAGE ON SCHEMA public FROM role_name;
  • A user without the CONNECT privilege cannot connect to the database
  • A user without the USAGE privilege cannot access the default public schema