In YB release 2.25.0, we’ve upgraded our PostgreSQL fork from version 11.2 to 15.0, allowing you to take advantage of the many powerful features introduced in PostgreSQL over these versions. This upgrade significantly enhances YSQL API support!
One notable feature is the ability to easily create a read-only user via the pg_read_all_data role, as highlighted in a previous YugabyteDB Tip: Create a Read Only User.
At the time, we couldn’t leverage the PostgreSQL 14 feature because YugabyteDB was still based on PostgreSQL 11. However, with YugabyteDB now upgraded to PostgreSQL 15, we can finally take advantage of this enhanced functionality!
Example:
yugabyte=# SELECT split_part(version(), '-', 1) "Postgres Version",
yugabyte-# split_part(version(), '-', 2) || ' ' || split_part(version(), '-', 3) "YB Version";
Postgres Version | YB Version
------------------+-------------
PostgreSQL 15.2 | YB 2.25.0.0
(1 row)
yugabyte=# CREATE TABLE some_table(c1 INT);
CREATE TABLE
yugabyte=# INSERT INTO some_table VALUES (1), (2), (3);
INSERT 0 3
yugabyte=# CREATE USER read_only_user WITH LOGIN;
CREATE ROLE
yugabyte=# \! ysqlsh -h $(hostname -I) -U read_only_user -c "SELECT * FROM some_table;"
ERROR: permission denied for table some_table
yugabyte=# GRANT pg_read_all_data TO read_only_user;
GRANT ROLE
yugabyte=# \! ysqlsh -h $(hostname -I) -U read_only_user -c "SELECT * FROM some_table;"
c1
----
3
2
1
(3 rows)
Note that the user can not INSERT data into the table:
yugabyte=# \! ysqlsh -h $(hostname -I) -U read_only_user -c "INSERT INTO some_table SELECT 4;"
ERROR: permission denied for table some_table
Have Fun!
