Lists: | pgsql-patches |
---|
From: | Gevik Babakhani <pgdev(at)xs4all(dot)nl> |
---|---|
To: | pgsql-patches(at)postgresql(dot)org |
Subject: | Patch for %Allow per-database permissions to be set via GRANT |
Date: | 2006-04-26 14:23:25 |
Message-ID: | 1146061405.18394.17.camel@voyager.truesoftware.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
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.
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Gevik Babakhani <pgdev(at)xs4all(dot)nl> |
Cc: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: Patch for %Allow per-database permissions to be set via |
Date: | 2006-04-30 02:09:38 |
Message-ID: | 200604300209.k3U29cm17114@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
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(at)postgresql(dot)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. +
Attachment | Content-Type | Size |
---|---|---|
/bjm/diff | text/x-diff | 12.5 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Gevik Babakhani <pgdev(at)xs4all(dot)nl>, pgsql-patches(at)postgresql(dot)org |
Subject: | Re: Patch for %Allow per-database permissions to be set via |
Date: | 2006-04-30 19:29:08 |
Message-ID: | 3175.1146425348@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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
From: | Gevik Babakhani <pgdev(at)xs4all(dot)nl> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, pgsql-patches(at)postgresql(dot)org |
Subject: | Re: Patch for %Allow per-database permissions to be set |
Date: | 2006-04-30 22:17:56 |
Message-ID: | 1146435476.31253.15.camel@voyager.truesoftware.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
On Sun, 2006-04-30 at 15:29 -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Gevik Babakhani <pgdev(at)xs4all(dot)nl>, pgsql-patches(at)postgresql(dot)org |
Subject: | Re: Patch for %Allow per-database permissions to be set via |
Date: | 2006-05-01 02:58:39 |
Message-ID: | 200605010258.k412wds12624@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-patches |
Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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. +