The following bug has been logged on the website:
Bug reference: 18717
Logged by: Gennady Kk
Email address: kolpasch@gmail.com
PostgreSQL version: 14.14
Operating system: Ubuntu 20.04.6 LTS
Description:
Bug Report
**Summary**
Our goal is to create a database where several users work with owner
rights.
Every object created in the database must have the same owner as the
database itself,so that any object is accessible to all users with maximum
permissions.
To achieve this, we create several login roles and one non-login role as the
database owner.
We grant the owner role to the login roles, and assign it by ALTER ROLE
<login_role> SET ROLE = <owner_role>. As a result, when logging into the
database with a login role, it is automatically assigned the owner role.
Starting with version 14.14, this behavior is broken, and the issue persists
in version 17.1. When connecting to the database with a login role, the
session is assigned an empty (none) value for the role, leaving users
without the necessary permissions.
**Steps to Reproduce**
postgres=# create role "bench-owner";
postgres=# create database bench owner "bench-owner";
postgres=# create user "bench-srv" noinherit;
postgres=# grant "bench-owner" to "bench-srv";
postgres=# alter role "bench-srv" set role 'bench-owner';
postgres=# \q
**Expected Result**
root@hostname:~# psql -U bench-srv bench
psql (14.13 (Ubuntu 14.13-1.pgdg20.04+1))
Type "help" for help.
bench=> show role;
role
--------------
bench-owner
(1 row)
bench=> create table tst(id int);
bench=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size
| Description
--------+------+-------+--------------+-------------+---------------+---------+-------------
public | tst | table | bench-owner | permanent | heap | 0 bytes
|
(1 row)
bench=> select version();
version
-------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.13 (Ubuntu 14.13-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
(1 row)
**Actual Result**
root@hostname:~# psql -U bench-srv bench
psql (14.13 (Ubuntu 14.13-1.pgdg20.04+1), server 14.14 (Ubuntu
14.14-1.pgdg20.04+1))
Type "help" for help.
bench=> show role;
role
------
none
(1 row)
bench=> create table tst(id int);
ERROR: permission denied for schema public
bench=> select version();
version
-------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.14 (Ubuntu 14.14-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
(1 row)
