Re: Curious case of the unstoppable user

On 29 March 2011 21:06, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> On 29/03/2011 19:44, Thom Brown wrote:
>>
>> Hi all,
>>
>> I've just set up a test user, revoked all access from them to a
>> database, then tried to connect to that database and it let me in.
>> When I try it all from scratch, it works correctly.
>>
>> Here's the set running correctly:
>>
>> postgres=# CREATE DATABASE testdb;
>> CREATE DATABASE
>> postgres=# CREATE ROLE testrole;
>> CREATE ROLE
>> postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE;
>> REVOKE
>> postgres=# \c testdb testrole
>> FATAL:  role "testrole" is not permitted to log in
>> Previous connection kept
>>
>> But now if I try something similar with an existing user and existing
>> database, it doesn't work:
>>
>> postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE;
>> REVOKE
>> postgres=# \c stuff meow
>> You are now connected to database "stuff" as user "meow".
>>
>> So, I'm overlooking something.  Could someone tell me what it is?  I
>> bet it's something obvious.  I'm using 9.1dev if it's relevant.
>
>
> Does the "public" role still have privileges on the database?

The access privileges shown on both databases are identical: "=Tc/thom
thom=CTc/thom", and both owned by user "thom".

Both users meow and testrole show blank membership: "{}"

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company