Thread: alter table owner doesn't update acl information
Hi, first of all: I use Postgres-version: 7.3.9 and uname -a gives: Linux 2.4.21-286-smp4G Now here's the problem: I have several superusers in my database and some normal users that only have access to the data by views. After I've dropped one of the superusers that created and thus owns some of the views and reinstalled it again with a different usesysid the normal users cannot access their views (that are owned by the aforementioned superuser) any longer. ERROR: pg_class_aclcheck: invalid user id 113 The workaround I tried without dropping the old views and creating new views is to alter the owner of the mentioned views. The error stays the same. I would have expected that the old usesysid that obviousl is the sole reference to the owner of the view was then updated to the new usesysid. Thus it is impossible to recover from the problem/mistake without dropping and creating the views again. Probably the problem will be the same with normal tables which I didn't check. Is this intention or a bug? What else could I try? Cheers, Madeleine
Madeleine Theile wrote:
> Hi,
>
> first of all:
> I use Postgres-version: 7.3.9 and uname -a gives:
> Linux 2.4.21-286-smp4G
>
> Now here's the problem:
> I have several superusers in my database and some normal users that only
> have access to the data by views.
>
> After I've dropped one of the superusers that created and thus
> owns some of the views and reinstalled it again with a different usesysid
> the normal users cannot access their views (that are owned by the
> aforementioned superuser) any longer.
>
> ERROR: pg_class_aclcheck: invalid user id 113
>
> The workaround I tried without dropping the old views and creating new
> views is to alter the owner of the mentioned views. The error stays the same.
>
> I would have expected that the old usesysid that obviousl is the sole
> reference to the owner of the view was then updated to the new usesysid.
> Thus it is impossible to recover from the problem/mistake without
> dropping and creating the views again.
> Probably the problem will be the same with normal tables which I didn't
> check.
>
> Is this intention or a bug? What else could I try?
Recreate the user using the original user id:
test=> \h create user
Command: CREATE USER
Description: define a new database user account
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SYSID uid
^^^^^
--
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
"Madeleine Theile" <madeleine.theile@cs.uni-dortmund.de> writes:
> After I've dropped one of the superusers that created and thus
> owns some of the views and reinstalled it again with a different usesysid
So reinstall it with the same usesysid --- that's why the option exists
to specify sysid in CREATE USER.
There is work in progress that will disallow dropping a user that still
owns any objects, but I am unsure if it will be done in time for 8.1.
regards, tom lane
On Wed, 2005-05-11 at 12:04 -0400, Tom Lane wrote: > "Madeleine Theile" <madeleine.theile@cs.uni-dortmund.de> writes: > > After I've dropped one of the superusers that created and thus > > owns some of the views and reinstalled it again with a different usesysid > > So reinstall it with the same usesysid --- that's why the option exists > to specify sysid in CREATE USER. > > There is work in progress that will disallow dropping a user that still > owns any objects, but I am unsure if it will be done in time for 8.1. > > regards, tom lane Hi Tom, then I got a follow-up question: dropping the database user was done for a reason: I am building an application with web-frontend. The user management/rights-management is done on the database basis. The user only has views/rules to access the data. So a user get's dropped when he's no longer allowed to log on. In this case it should be possible to drop the user but as a solution have an other user own the appropriate views when these are still needed. Why should it be possible to do it like this? In my case: there are several superusers and all superusers work on the same views. Therefore the idea to have another superuser own these views. OK, so we solved the problem with restoring the usesysid in case the user gets recreated/reactivated. But what if he doesn't? Then the only possibility is to drop all the views and recreate them as another user in order to fix the issue with the acl rights. Is this really the intention? Cheers, Madeleine
"Madeleine Theile" <mt@theile.net> writes:
> But what if he doesn't? Then the only possibility is to drop all the
> views and recreate them as another user in order to fix the issue with
> the acl rights.
See ALTER OWNER. The intention is to make you give away all the owned
objects before getting rid of the user.
regards, tom lane
On Thu, 2005-05-12 at 16:15 -0400, Tom Lane wrote: > "Madeleine Theile" <mt@theile.net> writes: > > But what if he doesn't? Then the only possibility is to drop all the > > views and recreate them as another user in order to fix the issue with > > the acl rights. > > See ALTER OWNER. The intention is to make you give away all the owned > objects before getting rid of the user. > > regards, tom lane > Try it, it doesn't work! I can give you examples in case you need them. Cheers, Madeleine