Re: Curious case of the unstoppable user - Mailing list pgsql-general

Le 29/03/2011 23:12, Thom Brown a écrit :
> On 29 March 2011 21:59, Guillaume Lelarge <guillaume@lelarge.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