​

Overview

In Oxla, ownership refers to the relationship where objects such as database, tables 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 | 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.

  Copy

  Ask AI

  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.

  Copy

  Ask AI

  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.

  Copy

  Ask AI

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