How do I revoke CREATE TABLE and other privileges?
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.
regards,
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.
--
Michael Fuhr
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,
On 6 Jul 2006 09:46:48 -0700
"Karen Hill" <karen_hill22@yahoo.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?
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"
On Thu, Jul 06, 2006 at 09:55:40AM -0700, John Purser wrote:
"Karen Hill" <karen_hill22@yahoo.com> wrote:
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.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.
The user probably needs USAGE on the schema. If a user has no
privileges on a schema then he or she can't access objects in that
schema regardless of search_path or the privileges on the objects
themselves.
--
Michael Fuhr