Often I need to remove a user and cede their permissions to someone else.
How about something like this:

DROP USER blah PERMISSIONS TO chriskl;

or maybe

GRANT ALL USER blah TO chriskl;

???

Chris


Tue, 14 Jan 2003 10:40:34 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
copying perms to another user
On Mon, 2003-01-13 at 21:40, Christopher Kings-Lynne wrote:
> Often I need to remove a user and cede their permissions to someone else.
> How about something like this:
> 
> DROP USER blah PERMISSIONS TO chriskl;

If you check that it's a superuser doing the drop, this would be good.

However, what (and how many) databases will this command work on?  Only
the current one?  All of them?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
13 Jan 2003 21:47:21 -0500
Rod Taylor <rbt@rbt.ca>
Re: copying perms to another user
> On Mon, 2003-01-13 at 21:40, Christopher Kings-Lynne wrote:
> > Often I need to remove a user and cede their permissions to
> someone else.
> > How about something like this:
> >
> > DROP USER blah PERMISSIONS TO chriskl;
>
> If you check that it's a superuser doing the drop, this would be good.
>
> However, what (and how many) databases will this command work on?  Only
> the current one?  All of them?

Yeah good point...it wouldn't bother me if it were just current database,
except that then it wouldn't be useful to use the DROP USER command.  ALTER
USER or GRANT would be better.

BTW Rod, I now get all your emails just fine (not as attachements) - did you
change something?

Chris


Tue, 14 Jan 2003 10:50:01 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: copying perms to another user
> Yeah good point...it wouldn't bother me if it were just current database,
> except that then it wouldn't be useful to use the DROP USER command.  ALTER
> USER or GRANT would be better.

How do you ALTER USER ... after they've been dropped?

> BTW Rod, I now get all your emails just fine (not as attachements) - did you
> change something?

Not that I know of.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
13 Jan 2003 21:57:30 -0500
Rod Taylor <rbt@rbt.ca>
Re: copying perms to another user
> > Yeah good point...it wouldn't bother me if it were just current 
> database,
> > except that then it wouldn't be useful to use the DROP USER 
> command.  ALTER
> > USER or GRANT would be better.
> 
> How do you ALTER USER ... after they've been dropped?

No, I mean that we don't drop the user.  You go:

ALTER USER chriskl COPY PERMISSIONS FROM blah;

Sort of thing...

Chris


Tue, 14 Jan 2003 11:01:42 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: copying perms to another user
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> No, I mean that we don't drop the user.  You go:
> ALTER USER chriskl COPY PERMISSIONS FROM blah;

That seems cleaner to me than the DROP thingy.

You could only easily implement this in the current database --- but
since it's not a DROP, one could repeat it in each database as needed.

			regards, tom lane

Mon, 13 Jan 2003 23:13:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: copying perms to another user 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > No, I mean that we don't drop the user.  You go:
> > ALTER USER chriskl COPY PERMISSIONS FROM blah;
>
> That seems cleaner to me than the DROP thingy.
>
> You could only easily implement this in the current database --- but
> since it's not a DROP, one could repeat it in each database as needed.

Could someone perhaps add it to TODO then (so I don't forget about it)?  I
can't promise that I can implement it...

Chris


Tue, 14 Jan 2003 12:23:59 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: copying perms to another user 
Christopher Kings-Lynne writes:

> Often I need to remove a user and cede their permissions to someone else.

If this happens to you a lot, the solution is to implement roles, grant
privileges to a role, grant the role to a user, and when you remove the
user you grant the role to someone else.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 14 Jan 2003 18:15:41 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: copying perms to another user
On Tue, Jan 14, 2003 at 12:23:59PM +0800, Christopher Kings-Lynne wrote:
> > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > > No, I mean that we don't drop the user.  You go:
> > > ALTER USER chriskl COPY PERMISSIONS FROM blah;
> >
> > That seems cleaner to me than the DROP thingy.
> >
> > You could only easily implement this in the current database --- but
> > since it's not a DROP, one could repeat it in each database as needed.
> 
> Could someone perhaps add it to TODO then (so I don't forget about it)?  I
> can't promise that I can implement it...

In this scenario, 'blah' is the user who will eventually be dropped, and
chriskl is taking over ownership of his 'stuff' right? How about doing it
the other way:

ALTER USER blah COPY PERMISSIONS TO chriskl;

Hmm, in fact, I can imagine uses for both forms: creating a 'template'
user who you COPY PERMISSIONS FROM when creating a new user of that type,
who will then be customized, so you can't use GROUPs. Hmm, what about
GROUP membership? Those get copied as well?

Ross

Tue, 14 Jan 2003 11:43:45 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: copying perms to another user
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> On Tue, Jan 14, 2003 at 12:23:59PM +0800, Christopher Kings-Lynne wrote:
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> No, I mean that we don't drop the user.  You go:
> ALTER USER chriskl COPY PERMISSIONS FROM blah;

> In this scenario, 'blah' is the user who will eventually be dropped, and
> chriskl is taking over ownership of his 'stuff' right? How about doing it
> the other way:
> ALTER USER blah COPY PERMISSIONS TO chriskl;

That seems a bit bizarre to me.  The user whose permissions are actually
being altered by this command is chriskl, not blah, so ISTM chriskl is
the name that should be specified as being ALTERed.  In other words I
like FROM, not TO.

But: nearby, Peter E. complains that this is a poor substitute for
implementing SQL-spec roles.  He's got a point --- especially since
roles could be made installation-wide, and thus they'd get around the
problem that ALTER USER COPY couldn't realistically do anything about
permissions in other databases.  GRANT ROLE foo TO USER bar (or whatever
the spec syntax is) would be an installation-wide change and so would
indirectly grant any privileges the role has in other databases.

While I haven't thought about it very hard, it seems to me that a role
might be equivalent or nearly so to a group.  If so, we might be able
to support roles with little more than some syntactic-sugar work ...

			regards, tom lane

Tue, 14 Jan 2003 13:10:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: copying perms to another user 
> While I haven't thought about it very hard, it seems to me that a role
> might be equivalent or nearly so to a group.  If so, we might be able
> to support roles with little more than some syntactic-sugar work ...

A few other changes, like allowing ownership of an object to be a group
(role) rather than strictly a user.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
14 Jan 2003 13:22:01 -0500
Rod Taylor <rbt@rbt.ca>
Re: copying perms to another user
From: "Rod Taylor" <rbt@rbt.ca>

>> While I haven't thought about it very hard, it seems to me that a role
>> might be equivalent or nearly so to a group.  If so, we might be able
>> to support roles with little more than some syntactic-sugar work ...

>A few other changes, like allowing ownership of an object to be a group
>(role) rather than strictly a user.

Also, at least in Oracle, one can grant ROLEs to other ROLEs. I don't know if that is what the SQL standard says though:

GRANT role1 TO role2;

Mike Mascari
mascarm@mascari.com



Tue, 14 Jan 2003 13:28:07 -0500
"Mike Mascari" <mascarm@mascari.com>
Re: copying perms to another user

createlang plpgsql template1
ERROR:  stat failed on file '$libdir/plpgsql': No such file or directory
createlang: language installation failed

is the above error normal in 7.3.1 on linux?

thanks.

johnl

Tue, 14 Jan 2003 14:55:02 -0600
"John Liu" <johnl@synthesys.com>
7.3.1 on linux
On Tue, 2003-01-14 at 20:55, John Liu wrote:
> createlang plpgsql template1
> ERROR:  stat failed on file '$libdir/plpgsql': No such file or directory
> createlang: language installation failed
> 
> is the above error normal in 7.3.1 on linux?

I find I'm getting the same.  

This will happen if the plpgsql.so language file is not in the directory
specified by `pg_config --pkglibdir'.  That directory's path is
substituted for '$libdir' by the backend.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For I know that my redeemer liveth, and that he shall 
      stand at the latter day upon the earth"             
                                       Job 19:25 


15 Jan 2003 00:41:59 +0000
Oliver Elphick <olly@lfix.co.uk>
Re: 7.3.1 on linux
We have roles?

> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net]
> Sent: Wednesday, 15 January 2003 1:16 AM
> To: Christopher Kings-Lynne
> Cc: Hackers
> Subject: Re: [HACKERS] copying perms to another user
> 
> 
> Christopher Kings-Lynne writes:
> 
> > Often I need to remove a user and cede their permissions to 
> someone else.
> 
> If this happens to you a lot, the solution is to implement roles, grant
> privileges to a role, grant the role to a user, and when you remove the
> user you grant the role to someone else.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 


Wed, 15 Jan 2003 09:30:47 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: copying perms to another user
Christopher Kings-Lynne writes:

> We have roles?

Until two days ago I was under the impression that roles were schema
objects, but apparently this is not the case, and it seems that roles are
really just an extension of our group concept.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 15 Jan 2003 20:05:43 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: copying perms to another user
Peter Eisentraut wrote:
> Christopher Kings-Lynne writes:
> 
> > We have roles?
> 
> Until two days ago I was under the impression that roles were schema
> objects, but apparently this is not the case, and it seems that roles are
> really just an extension of our group concept.

Yep.  We have already beefed up group handling quite a bit in the past
few releases, so if we can take it the extra steps needed, we can just
make ROLE and GROUP synonymous and be done with it.

I think the one missing item mentioned was for group ownership of an
object.  However, if we give group _permission_ to the object, I am not
sure why ownership is an issue.  Are there certain permission we can't
give to the group?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 15 Jan 2003 16:03:18 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: copying perms to another user
Bruce Momjian writes:

> I think the one missing item mentioned was for group ownership of an
> object.  However, if we give group _permission_ to the object, I am not
> sure why ownership is an issue.  Are there certain permission we can't
> give to the group?

Privilege to rename or drop the object, and the right to grant privileges
in the first place.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 16 Jan 2003 22:55:50 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: copying perms to another user

Added to TODO:

* Add group object ownership, so groups can rename/drop/grant on objects,
  so we can implement roles

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > I think the one missing item mentioned was for group ownership of an
> > object.  However, if we give group _permission_ to the object, I am not
> > sure why ownership is an issue.  Are there certain permission we can't
> > give to the group?
> 
> Privilege to rename or drop the object, and the right to grant privileges
> in the first place.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 25 Jan 2003 20:00:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: copying perms to another user