Обсуждение: revoke all from public ?

Hi,

I can't revoke things from 'public'. Do I misunderstand
something or is there a bug?:

henkvl=# create table pipo (field text);
CREATE
henkvl=# grant all on pipo to henkvl;
CHANGE
henkvl=# grant all on pipo to jack;
CHANGE
henkvl=# \z pipo
          Access permissions for database "henkvl"
 Relation |               Access permissions
----------+-------------------------------------------------
 pipo     | {"=","postgres=arwR","henkvl=arwR","jack=arwR"}
(1 row)

henkvl=# revoke all on pipo from public;
CHANGE
henkvl=# \z pipo
          Access permissions for database "henkvl"
 Relation |               Access permissions
----------+-------------------------------------------------
 pipo     | {"=","postgres=arwR","henkvl=arwR","jack=arwR"}
(1 row)

If I revoke from individual users there are no probs.


atlas:/root-# rpm -qf `which postgres`
postgresql-server-7.1.2-4PGDG
atlas:/root-# rpm -qf `which psql`
postgresql-7.1.2-4PGDG

Searching in the archives I found that Tom Lane fixed a prob like this
one year ago? But 7.1.2 is not that old...

Regards,
--
Henk van Lingen, Systems & Network Administrator              (o-      -+
Dept. of Computer Science, Utrecht University.                /\        |
phone: +31-30-2535278                                        v_/_
http://henk.vanlingen.net/             http://www.tuxtown.net/netiquette/

Henk van Lingen <henkvl@cs.uu.nl> writes:
> I can't revoke things from 'public'. Do I misunderstand
> something or is there a bug?:

Looks fine to me.  Public didn't have any privileges to begin with,
so "revoke all from public" is a no-op in your example.

            regards, tom lane

On Mon, Nov 19, 2001 at 11:02:32AM -0500, Tom Lane wrote:
  : Henk van Lingen <henkvl@cs.uu.nl> writes:
  : > I can't revoke things from 'public'. Do I misunderstand
  : > something or is there a bug?:
  :
  : Looks fine to me.  Public didn't have any privileges to begin with,
  : so "revoke all from public" is a no-op in your example.

  Aha, i understand 'public' refers only to the ' "=" ' part.
  Isn't ...../postgresql/doc/html/sql-revoke.html a bit misguiding
  saying:

   PUBLIC

      Rescind the specified privilege(s) for all users.

  Anyways, thanx, i'll look for another way to revoke all permissions.

--
Henk van Lingen, Systems & Network Administrator              (o-      -+
Dept. of Computer Science, Utrecht University.                /\        |
phone: +31-30-2535278                                        v_/_
http://henk.vanlingen.net/             http://www.tuxtown.net/netiquette/

Henk van Lingen <henkvl@cs.uu.nl> writes:
>   Isn't ...../postgresql/doc/html/sql-revoke.html a bit misguiding
>   saying:
>    PUBLIC
>       Rescind the specified privilege(s) for all users.

You're right, that's easily misread.  I've added some wording to try
to clarify:

  <para>
   <command>REVOKE</command> allows the creator of an object to revoke
   previously granted permissions from one or more users or groups of users.
   The key word <literal>PUBLIC</literal> refers to the implicitly defined
   group of all users.
  </para>

  <para>
   Note that any particular user will have the sum
   of privileges granted directly to him, privileges granted to any group he
   is presently a member of, and privileges granted to
   <literal>PUBLIC</literal>.  Thus, for example, revoking SELECT privilege
   from <literal>PUBLIC</literal> does not necessarily mean that all users
   have lost SELECT privilege on the object: those who have it granted
   directly or via a group will still have it.
  </para>

>   Anyways, thanx, i'll look for another way to revoke all permissions.

I'm afraid you have to do it retail: if you GRANTed anything to user
joe, you have to REVOKE FROM joe.  There's no easy shortcut.

Well ... hmm ... it would probably work to do
    UPDATE pg_class SET relacl = NULL WHERE relname = 'mytable';
which should revert the permissions on mytable to the default state.
Pretty ugly though, and not guaranteed to work the same in future releases.

            regards, tom lane