securing pg_catalog

[M***@tavve.com]

I want to create a user that has read only access to the tables in a
schema. I was able to configure that fine, but that user can see all of
the information in the pg_catalog schema. I do not want this user to be
able to see anything other than the one schema, I tried revoking rights to
schema pg_catalog but the user could still see data from the pg_catalog.
Is there a way to prevent access to pg_catalog schema for a specified user?

TIA,
Mark

Mark Miller Tavve Software Co.
Tavve Software Co. One Copley Parkway, Suite 480
www.tavve.com Morrisville, NC 27560
***@tavve.com [phone]
fax [phone]






---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Chris Gamache]

Can I run something by y'all to see if I'm on the right track, or have fallen
off my rocker?

... Postgresql 7.4 ...

Essentially I want to convert a user to a group. I want this new group to have
the same privileges to the same objects that the original user had. I can then
remove privileges from the original user, add it to the new group, and create
additional users within that group.

My thought was to be able to select all the objects and permissions to the
objects in the database that the original user has, and modify the ACL to
replace the user with the group. I just don't want to destroy my configuration
by experimentation, or a a malformed update query. GRANT and REVOKE are well
documented but, like every command, they obscure the actions that take place at
the system-table level.

I've also looked at the information_schema, which is something new to me.
Perhaps I could write a function that would EXECUTE a statement like this:

SELECT 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' ||
table_name || ' TO GROUP ' || $2 || CASE WHEN is_grantable = 'YES' THEN ' WITH
GRANT OPTION;' else ';' END from table_privileges WHERE grantee = $1;

Where $1 is the old user, and $2 is the new user. This doesn't cover sequences,
though. I'd have to take the view definition for table_privileges and add "OR
c.relkind = 'S'::char" to the SQL to include sequences.

I could do the same thing to REVOKE the user's privileges.

Is there an easier/better/safer way?

CG



__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings