Re: How do I revoke CREATE TABLE and other privileges?

On 6 Jul 2006 09:46:48 -0700
"Karen Hill" <karen_hill22(at)yahoo(dot)com> wrote:

>
> Michael Fuhr wrote:
> > On Wed, Jul 05, 2006 at 02:27:19PM -0700, Karen Hill wrote:
> > > I would like for one role to be able to login, and execute a
> > > couple of functions and nothing else. I've tried to revoke
> > > access to CREATE on the database, schema, and tablespace but when
> > > I tested it, the user was still allowed to create tables.
> >
> > From the REVOKE documentation:
> >
> > Note that any particular role will have the sum of privileges
> > granted directly to it, privileges granted to any role it is
> > presently a member of, and privileges granted to PUBLIC.
> >
> > If PUBLIC still has privileges on the objects then the role still
> > has privileges, even if you've attempted to revoke them. You'll
> > probably need to alter the privileges that PUBLIC has, which might
> > also require altering other roles' privileges to compensate.
> >
>
> Hi,
>
> Revoking PUBLIC worked. I can now login to the database and it will
> not allow me to create new tables. However when I gave (as postgres)
> the restricted user permission to execute one function it says it
> cannot find the function when I try to execute it.
>
> regards,
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faq
Karen,

You hopefully just need to edit your search path. See page 56 of the
manual for details. Type 'show search_path;' into pgsql and see what
the value is currently set to. Then use set to make it include the
schema with your function.

John Purser

--
I must have a prodigious quantity of mind; it takes me as much as a
week sometimes to make it up.
-- Mark Twain, "The Innocents Abroad"