Thread: 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