Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
Thread:
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: /bjm/diff
Description: text/x-diff (12.5 KB)

In response to

Responses

pgsql-patches by date

Next:From: Tom LaneDate: 2006-04-30 04:31:33
Subject: Re: pgstat: delayed write of stats file
Previous:From: Bruce MomjianDate: 2006-04-30 01:09:19
Subject: Re: Patch for BUG #2073: Can't drop sequence when created

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group