Thread: Bug #438: New users inherit permissions from dropped users

Bug #438: New users inherit permissions from dropped users

From
pgsql-bugs@postgresql.org
Date:
Sverre H. Huseby (shh@thathost.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
New users inherit permissions from dropped users

Long Description
I discovered the following problem on my PostgreSQL 7.1.3
installation.  Hopefully, you will be able to reproduce it.  Summary:
A new user will inherit permissions from a dropped user.

Create a test database, connect to it:

        template1=# create database bug;
        CREATE DATABASE

        template1=# \c bug
        You are now connected to database bug.

Make a test table, and show all permissions:

        bug=# create table bugtable ( foo integer );
        CREATE

        bug=# \dp
        Access permissions for database "bug"
         Relation | Access permissions
        ----------+--------------------
         bugtable |
        (1 row)

Now create a test user, and show the permissions again:

        bug=# create user buguser with password 'foo';
        CREATE USER

        bug=# \dp
        Access permissions for database "bug"
         Relation | Access permissions
        ----------+--------------------
         bugtable |
        (1 row)

Looks good.  The new user has no access rights to our database tables.
Now grant him everything on the test table:

        bug=# grant all on bugtable to buguser;
        CHANGE

        bug=# \dp
              Access permissions for database "bug"
         Relation |          Access permissions
        ----------+--------------------------------------
         bugtable | {"=","sverrehu=arwR","buguser=arwR"}
        (1 row)

Well, looks quite OK.  A couple of other entries suddenly appears too,
but well, don't know if that's related to the problem.

Now remove this user, and show some strange permissions:

        bug=# drop user buguser;
        DROP USER

        bug=# \dp
           Access permissions for database "bug"
         Relation |       Access permissions
        ----------+---------------------------------
         bugtable | {"=","sverrehu=arwR","30=arwR"}
        (1 row)

Hah!  What's that?  Doesn't look good.  But it gets worse: Now we
create a new user without giving him any permissions:

        bug=# create user newuser;
        CREATE USER

        bug=# \dp
              Access permissions for database "bug"
         Relation |          Access permissions
        ----------+--------------------------------------
         bugtable | {"=","sverrehu=arwR","newuser=arwR"}
        (1 row)

The user inherited all permissions from the deleted user!  Seems like
a security problem to me...


Sample Code


No file was uploaded with this report