From: Graham Vickrage <graham@gpmd.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Copy user privileges
Date: Wed, 13 Jul 2005 12:31:29 +0100
Message-ID: <001a01c5879e$69e7ab20$0c67a8c0@gvickrage> (raw)
In-Reply-To: <20050712192915.GA86199@winnie.fuhr.org>
Thanks for the suggestions but as you guessed I do need to keep the old
user and also groups would be unsuitable as I don't want either user to
be able to see data on the other database.
Thought there would be some SQL statement I could run on the sys tables
but I don't know enough about the internals to attempt it.
Thanks again,
Graham
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: 12 July 2005 20:29
To: Tom Lane
Cc: Graham Vickrage; pgsql-sql@postgresql.org
Subject: Re: [SQL] Copy user privileges
On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote:
> "Graham Vickrage" <graham@gpmd.co.uk> writes:
> > I want to keep all the privileges but assign them to a new user.
>
> > What is the best way of doing this?
>
> How about just renaming the old user to a new name?
>
> I don't think we have an ALTER command for that, but an UPDATE on
> pg_shadow would get the job done just as well.
What about ALTER USER RENAME TO?
test=# CREATE USER user1;
CREATE USER
test=# CREATE TABLE foo (x integer);
CREATE TABLE
test=# GRANT SELECT ON foo TO user1;
GRANT
test=# \z foo
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+-------+----------------------------------------------
public | foo | table | {postgres=arwdRxt/postgres,user1=r/postgres}
(1 row)
test=# ALTER USER user1 RENAME TO user2;
ALTER USER
test=# \z foo
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+-------+----------------------------------------------
public | foo | table | {postgres=arwdRxt/postgres,user2=r/postgres}
(1 row)
Renaming the user is only useful if you no longer need the old user. If
you need to keep the old user and copy its privileges, then consider
granting privileges to groups instead of to users -- then you could just
add the new user to a group.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: graham@gpmd.co.uk
Subject: Re: Copy user privileges
In-Reply-To: <001a01c5879e$69e7ab20$0c67a8c0@gvickrage>
* 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