When a CREATE VIEW statement is issued in a session, the view’s ownership is assigned to the current session user.
Example:
yugabyte=# SELECT current_user;
current_user
--------------
yugabyte
(1 row)
yugabyte=# CREATE VIEW luis_vw AS SELECT 1 some_data;
CREATE VIEW
named "luis".
yugabyte=# \dv luis_vw
List of relations
Schema | Name | Type | Owner
--------+---------+------+----------
public | luis_vw | view | yugabyte
(1 row)
What if you want to change the owner of the sequence to a different user later on? Is that possible? Let’s find out…
yugabyte=# CREATE USER kannan;
CREATE ROLE
yugabyte=# ALTER VIEW luis_vw OWNER TO kannan;
ERROR: ALTER VIEW not supported yet
LINE 1: ALTER VIEW luis_vw OWNER TO kannan;
^
HINT: See https://github.com/yugabyte/yugabyte-db/issues/1131. React with thumbs up to raise its priority
No luck there—it seems we’ll need to wait for YB engineering to address the GitHub issue.
Or will we?
The Notes section of the PostgreSQL documentation states, “For historical reasons,
ALTER TABLE can also be used with views.” Since YugabyteDB is PostgreSQL-compatible, the same should apply in YSQL. Let’s give it a try.
yugabyte=# ALTER TABLE luis_vw OWNER TO kannan;
ALTER TABLE
yugabyte=# \dv luis_vw;
List of relations
Schema | Name | Type | Owner
--------+---------+------+--------
public | luis_vw | view | kannan
(1 row)
And that’s how you change the owner of a view in YSQL!
UPDATE: The ALTER VIEW..OWNER TO syntax will be supported beginning in YugabyteDB 2024.1.3 and 2024.2!
Have Fun!
