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