​
Overview

In Oxla, ownership refers to the relationship where objects such as database, tables, types and schemas are owned by a specific role. Here are a few principles to keep in mind when thinking about ownership in Oxla:

  • Index doesn’t have an explicit owner as the table owner is also the index owner
  • Ownership is required to DROP an object
  • For the purpose of grants validation, owner has all privileges available for a given resource:
    • For table: SELECT, INSERT, UPDATE, DELETE
    • For schema: USAGE, CREATE

In the following sections, you’ll learn how to check and change ownership but also find out more about what are the differences between ownership and role privileges.

​
Checking Ownership

To check ownerships in Oxla, a superuser can execute the following query:

SELECT * FROM oxla_internal.oxla_object_owner;

 id | database | schema |   object_name   | object_type
----+----------+--------+-----------------+-------------
  5 | oxla     | public | data_types_demo | TABLE

Here’s the breakdown of the above output:

  • id: role id
  • database: database name
  • schema: schema name (empty if object_type is DATABASE)
  • object_name: object name (empty if object_type is SCHEMA or DATABASE)
  • object_type: type of the object

​
Changing Ownership

To change ownership, one needs to run the code below:

ALTER [ TABLE | TYPE | SCHEMA | DATABASE ] OBJECT_NAME OWNER TO ROLE_NAME;

where:

  • OBJECT_NAME: name of the object, whose ownership they want to change
  • ROLE_NAME: name of the role that will become the new owner of the specified object

​
Ownership vs Role Privileges

In Oxla, unlike PostgreSQL, object ownership and grants are independent entities. As mentioned above, for the purpose of grants validation, owner has all privileges available for a given resource. These privileges however, are not visible in oxla_internal.oxla_role_ns_grants / oxla_internal.oxla_role_table_grants and cannot be revoked.

GRANT / REVOKE operations can still be performed on object owner - they will result in creating / removing entries in oxla_internal.oxla_role_..._grants tables, which are independent of data stored in oxla_internal.oxla_object_owner. These grants do not matter anything as long as the user is the owner of a given resource, but they will take effect when the owner is changed.

​
Examples

Here are a few examples that demonstrate the behaviours described above, assuming there is a table1 and user1 role with USAGE grant in public schema:

  • After the following operations user1 will no longer be the owner of table1, but will have SELECT grant on that table.

    ALTER TABLE table1 OWNER TO user1;
GRANT SELECT ON table1 TO user1;
ALTER TABLE table1 OWNER TO oxla;

  • After the following operations user1 will still be able to SELECT from table1 because of ownership, however REVOKE does not change anything.

    ALTER TABLE table1 OWNER TO user1;
REVOKE SELECT ON table1 FROM user1;

  • After the following operations user1 will not have access to table1, however the owner has been changed and grant has been revoked.

    ALTER TABLE table1 OWNER TO user1;
GRANT SELECT ON table1 TO user1;
REVOKE SELECT ON table1 FROM user1;
ALTER TABLE table1 OWNER TO oxla;
PrivilegesError Handling