​
Overview

Oxla supports GRANT and REVOKE to manage privileges on database objects. Privileges can be assigned to a role for tables, schemas and database. They cannot be granted to or revoked from a superuser.

Performing GRANT or REVOKE on the superuser does not change anything

​
Available Privileges

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

PrivilegeDescriptionDatabase Objects
SELECTAllows SELECT on a given tabletable
INSERTAllows INSERT to a given tabletable
UPDATEAllows UPDATE on a given tabletable
DELETEAllows DELETE and TRUNCATE from a given tabletable
CONNECTAllows role to connect to the databasedatabase
CREATEAllows to CREATE tables and types in a given schema. CREATE INDEX requires ownership of the table and both CREATE and USAGE privilege on the schemaschema
USAGEAllows to access objects within the schema, provided the objects’ own privilege requirements are also satisfiedschema

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, which stands for all available privileges for a given database object. By default, every new role has CONNECT privilege to a database and USAGE privilege to public schema. For more details on that, please refer to the Default Privileges section

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
  • Nobody can modify: pg_catalog, information_schema, system, oxla_internal
Only the superuser is permitted to create a new schema

​
Checking Privileges

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

  • Table
SELECT * FROM oxla_internal.oxla_role_table_grants;

 id | database | schema  |      table      | privilege
----+----------+---------+-----------------+-----------
  1 | oxla     | public  | data_types_demo | INSERT
  1 | oxla     | public  | data_types_demo | DELETE
  • Database
SELECT * FROM oxla_internal.oxla_role_db_grants;

 id | database | privilege
----+----------+-----------
  1 | oxla     | CONNECT
  • Schema
SELECT * FROM oxla_internal.oxla_role_ns_grants;

 id | database | schema | privilege
----+----------+--------+-----------
  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 for a given object

​
Granting Privilege

In order to grant a privilege, one needs to execute the following command:

GRANT privilege [,privilege] ON [ TABLE | SCHEMA | DATABASE ] object_name TO 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 granted
  • role_name: name of the role to which the privileges are being granted
When the query is missing TABLE, SCHEMA or DATABASE keyword, TABLE keyword is being assumed

​
Revoking Privilege

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
When the query is missing TABLE, SCHEMA or DATABASE keyword, TABLE keyword is assumed

​
Default Privileges

By default, every created role has the following privileges:

  • CONNECT: privilege to a default database
  • USAGE: privilege to a 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;
  • User with revoked CONNECT privilege won’t be able to connect to the database
  • User with revoked USAGE privilege won’t be able to access the public schema
RolesOwnership