Обсуждение: Updating pg_attribute - Permission denied

I was trying to update attnotnull = 't' in the pg_attribute to add Not Null
constraint to a table.  It gave me a Permission denied.

Is this standard? Am I not allowed to change system catalogues (I am using
the postgres superuser!)  I thought I had heard this would work.  Or can you
only change attnotnull to false i.e. remove a not null constraint?  I
suppose this would make some kind of sense, as there could already be Nulls
in the field.

Thanks in advance

Ben

"Trewern, Ben" <Ben.Trewern@mowlem.com> writes:
> I was trying to update attnotnull = 't' in the pg_attribute to add Not Null
> constraint to a table.  It gave me a Permission denied.
> Is this standard? Am I not allowed to change system catalogues (I am using
> the postgres superuser!)

I don't believe it --- are you *sure* you were superuser?  Or perhaps
you'd turned off pg_shadow's usecatupd for yourself?

I get this behavior:

play=> update pg_attribute set attnotnull = 't' where attrelid =  334893 and
play-> attname = 'f1';
ERROR:  pg_attribute: Permission denied.
play=> \c - postgres
You are now connected as new user postgres.
play=# update pg_attribute set attnotnull = 't' where attrelid =  334893 and
play-# attname = 'f1';
UPDATE 1
play=#

> Or can you only change attnotnull to false i.e. remove a not null
> constraint?  I suppose this would make some kind of sense, as there
> could already be Nulls in the field.

It's up to you to worry about that sort of consistency issue if you
reach in and hack pg_attribute directly.  Certainly the permission
check is not concerned with it.

            regards, tom lane

Looks like I have turned off usecatupd in pg_shadow. Doh!  Was mucking
around with permissions some time ago but not had any trouble up till now.

Any ideas how to switch it back? Update pg_shadow SET usecatupd .....  gives
me Permission denied!!!

Ben.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 22 October 2000 04:08
> To: Trewern, Ben
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] Updating pg_attribute - Permission denied
>
>
> "Trewern, Ben" <Ben.Trewern@mowlem.com> writes:
> > I was trying to update attnotnull = 't' in the pg_attribute
> to add Not Null
> > constraint to a table.  It gave me a Permission denied.
> > Is this standard? Am I not allowed to change system
> catalogues (I am using
> > the postgres superuser!)
>
> I don't believe it --- are you *sure* you were superuser?  Or perhaps
> you'd turned off pg_shadow's usecatupd for yourself?
>
> I get this behavior:
>
> play=> update pg_attribute set attnotnull = 't' where
> attrelid =  334893 and
> play-> attname = 'f1';
> ERROR:  pg_attribute: Permission denied.
> play=> \c - postgres
> You are now connected as new user postgres.
> play=# update pg_attribute set attnotnull = 't' where
> attrelid =  334893 and
> play-# attname = 'f1';
> UPDATE 1
> play=#
>
> > Or can you only change attnotnull to false i.e. remove a not null
> > constraint?  I suppose this would make some kind of sense, as there
> > could already be Nulls in the field.
>
> It's up to you to worry about that sort of consistency issue if you
> reach in and hack pg_attribute directly.  Certainly the permission
> check is not concerned with it.
>
>             regards, tom lane
>

"Trewern, Ben" <Ben.Trewern@mowlem.com> writes:
> Looks like I have turned off usecatupd in pg_shadow. Doh!  Was mucking
> around with permissions some time ago but not had any trouble up till now.
> Any ideas how to switch it back? Update pg_shadow SET usecatupd .....  gives
> me Permission denied!!!

Catch-22, ain't it?  I think the only workaround at the moment is to
create a new user with superuser privileges (who will have usecatupd
true), become him, and fix your own usecatupd via UPDATE.  Then you
can discard the new superuser identity.

A cleaner solution would be to have an ALTER USER variant that allows
setting/clearing usecatupd.  Item for TODO list ...

            regards, tom lane

> "Trewern, Ben" <Ben.Trewern@mowlem.com> writes:
> > Looks like I have turned off usecatupd in pg_shadow. Doh!  Was mucking
> > around with permissions some time ago but not had any trouble up till now.
> > Any ideas how to switch it back? Update pg_shadow SET usecatupd .....  gives
> > me Permission denied!!!
>
> Catch-22, ain't it?  I think the only workaround at the moment is to
> create a new user with superuser privileges (who will have usecatupd
> true), become him, and fix your own usecatupd via UPDATE.  Then you
> can discard the new superuser identity.
>
> A cleaner solution would be to have an ALTER USER variant that allows
> setting/clearing usecatupd.  Item for TODO list ...

Added:

    * Add ALTER USER command to change user db attributes

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026