Обсуждение: BUG #12765: Postgresql ROLE dropped, but its not dropped from pg_roles

The following bug has been logged on the website:

Bug reference:      12765
Logged by:          vivek singh
Email address:      vivek.singh@rackspace.com
PostgreSQL version: 9.3.5
Operating system:   CentOS release 6.5
Description:

Postgresql ROLE dropped, but its not dropped from pg_roles view. rolconfig
column of pg_roles still shows the dropped role for associated user.

Bug reproduction:
=================================
postgres=# CREATE ROLE role1;
CREATE ROLE
postgres=# CREATE USER user1;
CREATE ROLE
postgres=# ALTER ROLE user1 SET ROLE TO role1;
ALTER ROLE
postgres=# \du+
                                    List of roles
 Role name |                   Attributes                   | Member of |
Description
-----------+------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication | {}        |
 role1     | Cannot login                                   | {}        |
 user1     |                                                | {}        |

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_roles WHERE rolname ='user1';
-[ RECORD 1 ]--+-------------
rolname        | user1
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolconfig      | {role=role1}
oid            | 24861

postgres=# DROP ROLE role1 ;
DROP ROLE
postgres=# \x
Expanded display is off.
postgres=# \du+
                                    List of roles
 Role name |                   Attributes                   | Member of |
Description
-----------+------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication | {}        |
 user1     |                                                | {}        |

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_roles WHERE rolname ='user1';
-[ RECORD 1 ]--+-------------
rolname        | user1
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolconfig      | {role=role1}
oid            | 24861

postgres=# SELECT version();
-[ RECORD 1
]---------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

postgres=#

vivek.singh wrote
> postgres=# ALTER ROLE user1 SET ROLE TO role1;

This does not do what it is you think it does...

In this statement the word "role" following SET is interpreted as an
identifier, specifically a GUC/configuration-variable.  It is not the
command/keyword ROLE as it is in the first part (ALTER ROLE).

See:  http://www.postgresql.org/docs/9.4/static/sql-alterrole.html

Custom user variables are allowed to be created and this is what you
accomplished.

You likely meant to issue:

GRANT role1 TO user1;

David J.




--
View this message in context:
http://postgresql.nabble.com/BUG-12765-Postgresql-ROLE-dropped-but-its-not-dropped-from-pg-roles-tp5837714p5837720.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.