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
else
acl = acl in pg_database
if (has_permission(acl, user, ACL_CONNECT))
can connect
else
can't connect

To revoke a privilege you do this:

if (acl in pg_datbase == NULL)
acl = acldefault
else
acl = 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.