Updating pg_attribute - Permission denied

Started by Trewern, Benover 25 years ago5 messagesgeneral

Ben.Trewern@mowlem.com

over 25 years ago

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

Tom Lane

tgl@sss.pgh.pa.us

over 25 years ago

In reply to: Trewern, Ben (#1)

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

over 25 years ago

In reply to: Tom Lane (#2)

RE: Updating pg_attribute - Permission denied

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.

Show quoted text

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

Import Notes

Resolved by subject fallback

Tom Lane

tgl@sss.pgh.pa.us

over 25 years ago

In reply to: Trewern, Ben (#3)

Re: Updating pg_attribute - Permission denied

"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

Bruce Momjian

bruce@momjian.us

over 25 years ago

In reply to: Tom Lane (#4)

Re: Updating pg_attribute - Permission denied

"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