X-POP3-Server: Host ([mail.hitechroboticsystemz.com]) 	by hitechgears.com (PostMaster POP3 Login [hrs@hitechroboticsystemz.com] [192.168.0.25]); 	Thu, 29 Dec 2011 12:17:11 +0530
Return-Path: <pgsql-general-owner+m183580@postgresql.org>
Received: from mx2.hub.org [200.46.204.254] by dr178.cyberspaceindia.com with SMTP;
   Thu, 29 Dec 2011 12:04:00 +0530
Received: from postgresql.org (mail.postgresql.org [200.46.204.86])
	by mx2.hub.org (Postfix) with ESMTP id 28CC1169AFD5;
	Thu, 29 Dec 2011 02:33:53 -0400 (AST)
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 02907181BC42
	for <pgsql-general@postgresql.org>; Thu, 29 Dec 2011 02:33:16 -0400 (AST)
Received: from io.link-m.de ([82.135.8.34])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <julian@mehnle.net>)
	id 1Rg9YA-0004cH-C7
	for pgsql-general@postgresql.org; Thu, 29 Dec 2011 06:33:16 +0000
Received: from [10.0.2.15] (50-0-128-85.dsl.dynamic.sonic.net [::ffff:50.0.128.85])
  (AUTH: CRAM-MD5 julian@mehnle.net, TLS: TLSv1/SSLv3,256bits,AES256-SHA)
  by io.link-m.de with esmtp; Thu, 29 Dec 2011 06:33:00 +0000
  id 00000000301A272B.000000004EFC099D.00006D67
From: Julian Mehnle <julian@mehnle.net>
To: pgsql-general@postgresql.org
Subject: [GENERAL] Restoring default privileges on a table
Date: Thu, 29 Dec 2011 06:41:11 +0000
User-Agent: KMail/1.9.9
Mime-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg=pgp-sha1; boundary="=_io-28009-1325140394-0001-2"
Message-Id: <201112290641.12156.julian@mehnle.net>
X-Pg-Spam-Score: -1.9 (-)
X-Mailing-List: pgsql-general
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org
X-Declude-Sender: pgsql-general-owner+m183580@postgresql.org [200.46.204.254]
X-Declude-Spoolname: 324775916743.eml
X-Declude-RefID: str=0001.0A090201.4EFC09E5.00B4:SCFMA507887,ss=1,fgs=0
X-Declude-Note: Scanned by Declude 4.10.79 "http://www.declude.com/x-note.htm"
X-Declude-Scan: Incoming Score [-5] at 12:04:18 on 29 Dec 2011
X-Declude-Tests: NOABUSE [2], FROMNOMATCH [2], HAM-INDICATOR [-8], FILTER-SUBJECT [2]
X-Country-Chain: GERMANY->NORWAY->PANAMA->destination
X-Declude-Code: f
X-HELO: mx2.hub.org
X-Identity: 200.46.204.254 | mx2.hub.org | postgresql.org
X-Rcpt-To: <anisha.kaul@hitechroboticsystemz.com>
X-SmarterMail-Spam: Declude: -5
X-SmarterMail-TotalSpamWeight: -5

--=_io-28009-1325140394-0001-2
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,

I just ran into a long-standing issue with the Pg privileges system.

It seems there is no way to restore a table's default privileges to what
they were directly after creating the table.

`REVOKE ALL ON table =E2=80=A6` revokes not only privileges that were expli=
citly
granted using GRANT but also all regular privileges that were in effect
due to default privileges, so the owner can't SELECT from their own table
anymore.  So this is not an option (although I *thought* it was and just
hosed my production system for an hour until I realized what had
happened).

Note that this is different from the "default privileges" managed through
`ALTER DEFAULT PRIVILEGES`.  Tom Lane implemented a way to get rid of such
sets of modified default privileges back in April 2010:

    http://archives.postgresql.org/pgsql-hackers/2010-04/msg00139.php
    http://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dcommit;h=3D60b=
d2b1941c6211e973770e69cfa33367cc5db48

What *I'm* talking about here is this:

    http://archives.postgresql.org/pgsql-sql/2010-06/msg00042.php

Whereas on Pg 8.3 it was at least possible to discern default privileges
(null, shown as an empty string) from an empty set of privileges (shown
as {}) in \z output, on Pg 9.0+ both are shown as an empty string, which
I think is particularly bad.  Could \z and \dp be changed to show
"default" rather than an empty string when default privileges are in
effect for an object?  Or, conversely, could we show "none" if there's
an empty set of privileges for an object?

Also, I would like to propose a new command for applying the current
default privileges as defined (or undefined) through `ALTER DEFAULT
PRIVILEGES` to one or more database objects:

    =3D> APPLY DEFAULT PRIVILEGES TO TABLE foo;
    =3D> APPLY DEFAULT PRIVILEGES TO ALL TABLES IN SCHEMA bar;

Alternatively, this could be made a variant on GRANT:

    =3D> GRANT DEFAULT PRIVILEGES ON TABLE foo;
    =3D> GRANT DEFAULT PRIVILEGES ON ALL TABLES IN SCHEMA bar;

But that might be misleading since it could effectively *remove*
privileges as well.

The important thing is: there should be a way to restore default
privileges on a database object.

What do you think?

=2DJulian

--=_io-28009-1325140394-0001-2
Content-Type: application/pgp-signature; name="signature.asc"
Content-Transfer-Encoding: 7bit
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAk78C4gACgkQwL7PKlBZWjttZACghxVm9sBQ9H4T52zZERZFZaQ9
mLMAoJ8uHRsiYUYcUJXO61dWlPoU3/z4
=qKZu
-----END PGP SIGNATURE-----

--=_io-28009-1325140394-0001-2--
