Skip site navigation (1) Skip section navigation (2)

Re: Curious case of the unstoppable user

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
To: Thom Brown <thom(at)linux(dot)com>
Cc: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Curious case of the unstoppable user
Date: 2011-03-29 21:27:11
Message-ID: 4D924EAF.2010204@lelarge.info (view raw or flat)
Thread:
Lists: pgsql-general
Le 29/03/2011 23:12, Thom Brown a écrit :
> 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.
> 

it doesn't tell you about the LOGIN attribute, but it tells you about
the priviledges, and among them, the CONNECT one. When the line begins
with =, it's the priviledges for public. If you have a user name before
the equal sign, then it's the priviledges for this user. For example:

testdb=# grant connect on database testdb to testrole;
GRANT
testdb=# \l+
     Name     |    Access privileges
--------------+-------------------------
 b1           |
 testdb       | guillaume=CTc/guillaume+
                testrole=c/guillaume

On b1, anyone with the LOGIN attribute can connect. On testdb, only
guillaume and testrole can connect, but only guillaume can create objects.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

In response to

pgsql-general by date

Next:From: Vibhor KumarDate: 2011-03-29 21:29:21
Subject: Re: Curious case of the unstoppable user
Previous:From: wepwepDate: 2011-03-29 21:20:33
Subject: Totally new, two main problems.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group