Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ownership problem

Ownership problem

From
Scrap Happy
Date:
Hi,

[Annoying that there aren't *easy* ways to cut and paste all of this!
Even dialogs that *should* -- or DO! -- present the information seem
to prevent selecting the "static" text therein for a "Copy".  So,
blame any of my typos in transcribing this on that...  :< ]

Client: 1.14.1 (2 Dec 2011 REL-1_14_1)        Windows XP Pro SP3 (with updates from several months back)

Server: PostgreSQL 8.4.6 compiled from Visual C++ build 1400, 32bit        localhost:5432

User:   postgres (!!)

I stumbled on the following using the "point and click" interface.
But, to clarify what I was doing, at the time, I've cut and
pasted the contents of the "SQL" tab here, in each case (one
"command group" issued at a time):

---------8<----------8<-----------  CREATE DATABASE "Play"    WITH ENCODING='UTF8'         OWNER="SomeoneElse"
CONNECTIONLIMIT=-1;
 
.  CREATE SCHEMA "Example"         AUTHORIZATION "SomeoneElse";
.  CREATE TABLE "Example"."Association"  (  )  WITH (    OIDS = FALSE  );  ALTER TABLE "Example"."Association" OWNER TO
"SomeoneElse";
.  ALTER TABLE "Example"."Association"     ADD COLUMN id bigserial;
---------8<----------8<-----------

Yields this result from SQL "Messages" tab:

---------8<----------8<-----------
NOTICE:  ALTER TABLE will create implicit sequence "Association_id_seq"         for serial column "Association.id"


ERROR:  sequence must have same owner as table it is linked to

********** Error **********

ERROR: sequence must have same owner as table it is linked to
SQL state: 55000
---------8<----------8<-----------

Repeating the same effective operations in a slightly different order:

---------8<----------8<-----------  CREATE DATABASE "Play"    WITH ENCODING='UTF8'         OWNER="SomeoneElse"
CONNECTIONLIMIT=-1;
 
.  CREATE SCHEMA "Example"         AUTHORIZATION "SomeoneElse";
.  CREATE TABLE "Example"."Association"  (     id bigserial  )  WITH (    OIDS = FALSE  );  ALTER TABLE
"Example"."Association"OWNER TO "SomeoneElse";
 
---------8<----------8<-----------

completes with no problems.

Of course, I can (reasonably) easily work around this. But, I
suspect there is probably "a little something" missing, somewhere.

I didn't notice this problem with 1.12 -- but, I may have been
doing things differently, at that time (no incentive to reinstall
that *just* to test this)

(No doubt logging in as "SomeoneElse" would be the best "PTF".  :> )

Thanks!
--don

Re: Ownership problem

From
Guillaume Lelarge
Date:
On Thu, 2012-02-02 at 17:50 -0700, Scrap Happy wrote:
> Hi,
> 
> [Annoying that there aren't *easy* ways to cut and paste all of this!
> Even dialogs that *should* -- or DO! -- present the information seem
> to prevent selecting the "static" text therein for a "Copy".  So,
> blame any of my typos in transcribing this on that...  :< ]
> 
> Client: 1.14.1 (2 Dec 2011 REL-1_14_1)
>          Windows XP Pro SP3 (with updates from several months back)
> 
> Server: PostgreSQL 8.4.6 compiled from Visual C++ build 1400, 32bit
>          localhost:5432
> 
> User:   postgres (!!)
> 
> I stumbled on the following using the "point and click" interface.
> But, to clarify what I was doing, at the time, I've cut and
> pasted the contents of the "SQL" tab here, in each case (one
> "command group" issued at a time):
> 
> ---------8<----------8<-----------
>    CREATE DATABASE "Play"
>      WITH ENCODING='UTF8'
>           OWNER="SomeoneElse"
>           CONNECTION LIMIT=-1;
> .
>    CREATE SCHEMA "Example"
>           AUTHORIZATION "SomeoneElse";
> .
>    CREATE TABLE "Example"."Association"
>    (
>    )
>    WITH (
>      OIDS = FALSE
>    );
>    ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
> .
>    ALTER TABLE "Example"."Association"
>       ADD COLUMN id bigserial;
> ---------8<----------8<-----------
> 
> Yields this result from SQL "Messages" tab:
> 
> ---------8<----------8<-----------
> NOTICE:  ALTER TABLE will create implicit sequence "Association_id_seq"
>           for serial column "Association.id"
> 
> 
> ERROR:  sequence must have same owner as table it is linked to
> 
> ********** Error **********
> 
> ERROR: sequence must have same owner as table it is linked to
> SQL state: 55000
> ---------8<----------8<-----------
> 
> Repeating the same effective operations in a slightly different order:
> 
> ---------8<----------8<-----------
>    CREATE DATABASE "Play"
>      WITH ENCODING='UTF8'
>           OWNER="SomeoneElse"
>           CONNECTION LIMIT=-1;
> .
>    CREATE SCHEMA "Example"
>           AUTHORIZATION "SomeoneElse";
> .
>    CREATE TABLE "Example"."Association"
>    (
>       id bigserial
>    )
>    WITH (
>      OIDS = FALSE
>    );
>    ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
> ---------8<----------8<-----------
> 
> completes with no problems.
> 
> Of course, I can (reasonably) easily work around this. But, I
> suspect there is probably "a little something" missing, somewhere.
> 
> I didn't notice this problem with 1.12 -- but, I may have been
> doing things differently, at that time (no incentive to reinstall
> that *just* to test this)
> 
> (No doubt logging in as "SomeoneElse" would be the best "PTF".  :> )
> 

Well, I don't know what to say. This isn't an issue with pgAdmin.
PostgreSQL doesn't allow to add a column with a serial pseudo-type if
the table belongs to another user because PostgreSQL requires that the
sequence associated with the serial pseudo-type has the same owner than
the table. Not much we can do here.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org

Re: Ownership problem

From
Scrap Happy
Date:
On 2/4/2012 11:13 AM, Guillaume Lelarge wrote:
> On Thu, 2012-02-02 at 17:50 -0700, Scrap Happy wrote:

>> ---------8<----------8<-----------
>>     CREATE DATABASE "Play"
>>       WITH ENCODING='UTF8'
>>            OWNER="SomeoneElse"
>>            CONNECTION LIMIT=-1;
>> .
>>     CREATE SCHEMA "Example"
>>            AUTHORIZATION "SomeoneElse";
>> .
>>     CREATE TABLE "Example"."Association"
>>     (
>>     )
>>     WITH (
>>       OIDS = FALSE
>>     );
>>     ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
>> .
>>     ALTER TABLE "Example"."Association"
>>        ADD COLUMN id bigserial;
>> ---------8<----------8<-----------
>>
>> Yields this result from SQL "Messages" tab:
>>
>> ---------8<----------8<-----------
>> NOTICE:  ALTER TABLE will create implicit sequence "Association_id_seq"
>>            for serial column "Association.id"
>>
>>
>> ERROR:  sequence must have same owner as table it is linked to
>>
>> ********** Error **********
>>
>> ERROR: sequence must have same owner as table it is linked to
>> SQL state: 55000
>> ---------8<----------8<-----------
>>
>> Repeating the same effective operations in a slightly different order:
>>
>> ---------8<----------8<-----------
>>     CREATE DATABASE "Play"
>>       WITH ENCODING='UTF8'
>>            OWNER="SomeoneElse"
>>            CONNECTION LIMIT=-1;
>> .
>>     CREATE SCHEMA "Example"
>>            AUTHORIZATION "SomeoneElse";
>> .
>>     CREATE TABLE "Example"."Association"
>>     (
>>        id bigserial
>>     )
>>     WITH (
>>       OIDS = FALSE
>>     );
>>     ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
>> ---------8<----------8<-----------
>>
>> completes with no problems.
>>
>> (No doubt logging in as "SomeoneElse" would be the best "PTF".  :>  )
>
> Well, I don't know what to say. This isn't an issue with pgAdmin.
> PostgreSQL doesn't allow to add a column with a serial pseudo-type if
> the table belongs to another user because PostgreSQL requires that the
> sequence associated with the serial pseudo-type has the same owner than
> the table. Not much we can do here.

Understood.  The point is, there is no way to /add/ a serial pseudo-type
to a table owned by another user in pgadmin3.

Adding a column of type *serial won't work because the serial has
to inherit the owner of the table to which it is being added.

Similarly, you can /create/ the serial in pgadmin3.  And, you can
declare its owner to coincide with the owner of the table.  But,
you can't "move" it into the existing table (i.e., a drag 'n' drop
style operation).

Yet, if you add a serial column to the table WHILE YOU ARE CREATING
THE TABLE (in pgadmin3), all will magically work as the serial gets
created with the proper owner (as the table).

The purpose of my post, here, is for future googlers that might
find themselves in a similar situation (hence the suggested "fix",
above)

Re: Ownership problem

From
Guillaume Lelarge
Date:
On Mon, 2012-02-06 at 23:27 -0700, Scrap Happy wrote:
> On 2/4/2012 11:13 AM, Guillaume Lelarge wrote:
> > On Thu, 2012-02-02 at 17:50 -0700, Scrap Happy wrote:
> 
> >> ---------8<----------8<-----------
> >>     CREATE DATABASE "Play"
> >>       WITH ENCODING='UTF8'
> >>            OWNER="SomeoneElse"
> >>            CONNECTION LIMIT=-1;
> >> .
> >>     CREATE SCHEMA "Example"
> >>            AUTHORIZATION "SomeoneElse";
> >> .
> >>     CREATE TABLE "Example"."Association"
> >>     (
> >>     )
> >>     WITH (
> >>       OIDS = FALSE
> >>     );
> >>     ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
> >> .
> >>     ALTER TABLE "Example"."Association"
> >>        ADD COLUMN id bigserial;
> >> ---------8<----------8<-----------
> >>
> >> Yields this result from SQL "Messages" tab:
> >>
> >> ---------8<----------8<-----------
> >> NOTICE:  ALTER TABLE will create implicit sequence "Association_id_seq"
> >>            for serial column "Association.id"
> >>
> >>
> >> ERROR:  sequence must have same owner as table it is linked to
> >>
> >> ********** Error **********
> >>
> >> ERROR: sequence must have same owner as table it is linked to
> >> SQL state: 55000
> >> ---------8<----------8<-----------
> >>
> >> Repeating the same effective operations in a slightly different order:
> >>
> >> ---------8<----------8<-----------
> >>     CREATE DATABASE "Play"
> >>       WITH ENCODING='UTF8'
> >>            OWNER="SomeoneElse"
> >>            CONNECTION LIMIT=-1;
> >> .
> >>     CREATE SCHEMA "Example"
> >>            AUTHORIZATION "SomeoneElse";
> >> .
> >>     CREATE TABLE "Example"."Association"
> >>     (
> >>        id bigserial
> >>     )
> >>     WITH (
> >>       OIDS = FALSE
> >>     );
> >>     ALTER TABLE "Example"."Association" OWNER TO "SomeoneElse";
> >> ---------8<----------8<-----------
> >>
> >> completes with no problems.
> >>
> >> (No doubt logging in as "SomeoneElse" would be the best "PTF".  :>  )
> >
> > Well, I don't know what to say. This isn't an issue with pgAdmin.
> > PostgreSQL doesn't allow to add a column with a serial pseudo-type if
> > the table belongs to another user because PostgreSQL requires that the
> > sequence associated with the serial pseudo-type has the same owner than
> > the table. Not much we can do here.
> 
> Understood.  The point is, there is no way to /add/ a serial pseudo-type
> to a table owned by another user in pgadmin3.
> 

Saying "in pgadmin" is not completely true. You also cannot do it in
phppgadmin, in psql... IOW, with any PostgreSQL client.

> Adding a column of type *serial won't work because the serial has
> to inherit the owner of the table to which it is being added.
> 

Exactly. It has nothing to do with pgAdmin3.

> Similarly, you can /create/ the serial in pgadmin3.  And, you can
> declare its owner to coincide with the owner of the table.  But,
> you can't "move" it into the existing table (i.e., a drag 'n' drop
> style operation).
> 
> Yet, if you add a serial column to the table WHILE YOU ARE CREATING
> THE TABLE (in pgadmin3), all will magically work as the serial gets
> created with the proper owner (as the table).
> 
> The purpose of my post, here, is for future googlers that might
> find themselves in a similar situation (hence the suggested "fix",
> above)
> 

OK.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com