Re: Curious case of the unstoppable user

On 29 March 2011 21:59, Guillaume Lelarge <guillaume(at)lelarge(dot)info> wrote:
> Le 29/03/2011 20:44, Thom Brown a écrit :
>> 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
>>
>
> This is because you created a role without the login attribute. IOW, it
> has nothing to do with your REVOKE statement. Proof:
>
> postgres=# CREATE DATABASE testdb;
> CREATE DATABASE
> postgres=# CREATE ROLE testrole;
> CREATE ROLE
> 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.
>>
>
> Yeah. You probably created meow as a user, with is a role with the login
> attribute. The \dg+ metacommand tells us exactly that:
>
>> stuff=> \dg+
>>                                     List of roles
>>  Role name |                   Attributes                   | Member
>> of | Description
>> -----------+------------------------------------------------+-----------+-------------
>>  meow      |                                                | {}        |
>>  testrole  | Cannot login                                   | {}        |
>>  thom      | Superuser, Create role, Create DB, Replication | {}        |
>
> So:
>
> postgres=# CREATE USER meow;
> CREATE ROLE
> postgres=# \c testdb meow
> You are now connected to database "testdb" as user "meow".
>
> Now, you not only need to revoke connect permission to meow. You need to
> do it to public too:
>
> testdb=> \c testdb postgres
> You are now connected to database "testdb" as user "postgres".
> testdb=# REVOKE ALL ON DATABASE testdb FROM meow CASCADE;
> REVOKE
> testdb=# \c testdb meow
> You are now connected to database "testdb" as user "meow".
>
> Same result as you. Now, revoke connect permission to public:
>
> testdb=> \c testdb postgres
> You are now connected to database "testdb" as user "postgres".
> testdb=# REVOKE ALL ON DATABASE testdb FROM PUBLIC CASCADE;
> REVOKE
> testdb=# \c testdb meow
> FATAL:  permission denied for database "testdb"
> DETAIL:  User does not have CONNECT privilege.
> Previous connection kept

I altered the role with NOLOGIN, then tried to connect as that user
again, and it doesn't let the user in, so you're correct.

Thanks for the explanation. I take it the access priviledges field
shown in \l+ reveals this? It must be the line that begins with =. I
need to familiarise myself with it more.

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

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