Re: Please advice TODO Item pg_hba.conf

Gevik Babakhani wrote:

> > If I'm not mistaken, the general principle for creating objects is leave
> > their ACLs as NULLs.  Later, when a privilege is going to be checked, a
> > NULL is treated as if it contained whatever default privilege the object
> > class has.  So you should leave this code alone, and have the checking
> > code replace the default ACL when it gets a NULL (this way, it's even
> > more backwards compatible).
> 
> Personally I think this would create an conflict only in case of the
> CONNECT privilege. If the ACL is NULL and we treat NULL as default and
> the CONNECT privilege is part of default privileges then how do we
> distinguish between someone NOT HAVING THE CONNECT PRIVILEGE to connect
> to a certain database. This was the reason I thought when someone
> connects to a database NULL ACL will not do because you cannot know the
> user connecting does or does not have the CONNECT privilege. The problem
> is I think when you revoke a privilege from ACL, the current code
> regarding this actually removes/deletes the privilege from the ACL bits.

I don't understand.  The code should look like this:

if (acl in pg_database == NULL)acl = acldefault
elseacl = acl in pg_database
if (has_permission(acl, user, ACL_CONNECT))can connect
elsecan't connect


To revoke a privilege you do this:

if (acl in pg_datbase == NULL)acl = acldefault
elseacl = acl in pg_database
newacl = revoke_privilege_from(acl)
store newacl in pg_database



You can very easily know if someone does have the connect privilege,
even if the ACL is null, because the NULL acl has a very well-defined
meaning (only OWNER can connect, or maybe OWNER and PUBLIC can connect,
or whatever).


> Personally I think it would be better for the database owner not have
> the option to REVOKE himself from the CONNECTION privilege of his own
> database. 

Why?  A table owner can revoke privileges from himself.

alvherre=# create user foo;
CREATE ROLE
alvherre=# grant create on schema public to foo;
GRANT
alvherre=# set session AUTHORIZATION foo;
SET
alvherre=> create table foo (a int);
CREATE TABLE
alvherre=> revoke all on foo from foo;
REVOKE
alvherre=> \z foo
Privilegios para base de datos «alvherre»Schema | Nombre | Tipo  | Privilegios 
--------+--------+-------+-------------public | foo    | tabla | {}
(1 fila)

alvherre=> select * from foo;
ERROR:  permiso denegado para la relación foo
(english: permission denied ...)


> > Also I'm not sure if we have discussed what's the default (initial)
> > privilege state.  Do we want PUBLIC to have CONNECT privilege?
> 
> If I where a DBA I would rather explicitly give people connect
> permission to my database rather than having to explicitly block them
> from connecting to the database. It is like all the doors are closed to
> the public unless the doorman opens the door for the person he knows.
> But then again this is my personal opinion. We can always discuss this
> of course.

I understand your point, but we give a lot of privileges by default (I
think we give CREATE on the PUBLIC schema, for example).  You can
propose to change that behavior, but I feel that's a different
discussion than what you are working on ATM.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.