pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18717: ALTER ROLE SET ROLE functionality is broken
2+ messages / 2 participants
[nested] [flat]
* BUG #18717: ALTER ROLE SET ROLE functionality is broken
@ 2024-11-20 12:33 PG Bug reporting form <noreply@postgresql.org>
2024-11-20 15:48 ` Re: BUG #18717: ALTER ROLE SET ROLE functionality is broken Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2024-11-20 12:33 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: kolpasch@gmail.com
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18717: ALTER ROLE SET ROLE functionality is broken
2024-11-20 12:33 BUG #18717: ALTER ROLE SET ROLE functionality is broken PG Bug reporting form <noreply@postgresql.org>
@ 2024-11-20 15:48 ` Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Nathan Bossart @ 2024-11-20 15:48 UTC (permalink / raw)
To: kolpasch@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, Nov 20, 2024 at 12:33:41PM +0000, PG Bug reporting form wrote:
> 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.
This should be fixed in the next set of minor versions scheduled for
release tomorrow [0].
[0] https://postgr.es/c/7b88529
--
nathan
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-20 15:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-20 12:33 BUG #18717: ALTER ROLE SET ROLE functionality is broken PG Bug reporting form <noreply@postgresql.org>
2024-11-20 15:48 ` Nathan Bossart <nathandbossart@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox