pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Gamache <cgg007@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: Clone a user's permissions to a group
Date: Thu, 16 Sep 2004 09:18:18 -0700 (PDT)
Message-ID: <20040916161818.75280.qmail@web13806.mail.yahoo.com> (raw)
In-Reply-To: <OFDFB8106E.B082A83C-ON85256F11.004D70C6@tavve.com>

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 



Message-ID: <20040916161818.75280.qmail@web13806.mail.yahoo.com>
Permalink:  ../20040916161818.75280.qmail@web13806.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040916161818.75280.qmail@web13806.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cgg007@yahoo.com
  Subject: Re: Clone a user's permissions to a group
  In-Reply-To: <20040916161818.75280.qmail@web13806.mail.yahoo.com>
  References: <OFDFB8106E.B082A83C-ON85256F11.004D70C6@tavve.com> <20040916161818.75280.qmail@web13806.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox