Patch for %Allow per-database permissions to be set via GRANT

Documentation added, patch attached and applied. Thanks.

---------------------------------------------------------------------------

Gevik Babakhani wrote:

This patch implements the TODO Item: "%Allow per-database permissions to
be set via GRANT"

Implementation details:

1. A privilege ACL_CONNECT has been added to the ACL bits

2. The ACL_CONNECT can be recognized by character "c" in
pg_database/dataacl

3. The patch implements:

GRANT CONNECTION ON DATABASE mydatabase TO myuser

REVOKE CONNECTION ON DATABASE mydatabase FROM myuser

4. The initial condition ACL=NULL is treated as default
ACL=ACL_CREATE_TEMP | ACL_CONNECT providing backward compatibility with
the current pg_hba.conf
Notes:

As discussed :
A database owner WITHOUT SUPERUSER privileges can lock himself out from
connecting to his database. Try:

#psql -U user1 -d user1
Revoke connection on database user1 from public;
Revoke connection on database user1 from user1;

In this case no warning will be shown to the user informing he/she is
possibly locked out. This behavior is discussed in the hackers list.

The solution for a possible lockout would be to connect as a superuser
and GRANT CONNECTION ON DATABASE user1 TO <anyuser or public>

The implementation is best used for systems not wishing to change
pg_hba.conf frequently. In that case a simple host record can be added
to pg_hba.conf, providing from witch network the server is allowed to be
connected from and the database connection privilege can be granted or
revoked from withing SQL.

e.g.
CREATE USER user1 LOGIN;
CREATE USER user2 LOGIN;
CREATE DATABASE user1 OWNER user1;
REVOKE CONNECTION ON DATABASE user1 FROM PUBLIC;
GRANT CONNECTION,CREATE ON DATABASE user1 TO user2;
SELECT datname,datacl FROM pg_catalog.pg_database;

The patch can be downloaded from:

http://www.xs4all.nl/~gevik/patch/patch-0.7.diff

Many thanks to Tom Lane and Alvaro Herrera for their insight and
coaching.

Regards,
Gevik.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Documentation added, patch attached and applied. Thanks.

I just got around to reading this patch. Why is the syntax GRANT CONNECTION
and not GRANT CONNECT? Privilege names are generally verbs not nouns.
Unless someone can point to a good reason for CONNECTION, I'm going to
change it.

regards, tom lane

On Sun, 2006-04-30 at 15:29 -0400, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Documentation added, patch attached and applied. Thanks.

I just got around to reading this patch. Why is the syntax GRANT CONNECTION
and not GRANT CONNECT? Privilege names are generally verbs not nouns.
Unless someone can point to a good reason for CONNECTION, I'm going to
change it.

The main reason for this was because, in the beginning when I was
gathering information for developing this patch, I read something about
not introducing a new reserved word. So I used CONNECTION as the first
relevant word I could find in the token list from gram.y. Later on we
did not discussed anything about the *CONNECT* or *CONNECTION

Regards,
Gevik.

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Documentation added, patch attached and applied. Thanks.

I just got around to reading this patch. Why is the syntax GRANT CONNECTION
and not GRANT CONNECT? Privilege names are generally verbs not nouns.
Unless someone can point to a good reason for CONNECTION, I'm going to
change it.

Sounds good, hit the docs too, thanks.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +