Обсуждение: Problem when reloading data from older version
Restoring a full dump from 6.4.2 into 6.5: When the owner of a database does not have usesuper privilege in pg_shadow, it is not possible to recreate items that were created with superuser privilege, even if the superuser is running the script: ======== example ============== \connect template1 select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; drop table tmp_pg_shadow; copy pg_shadow from stdin; ... user1 1005 t t f t \N \. \connect template1 user1 create database morejunk; \connect morejunk user1 ... CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C'; ... Result: QUERY: CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C'; ERROR: Only users with Postgres superuser privilege are permitted to create a function in the 'C' language. Others may use the 'sql' language or the created procedural languages. ====================== It would seem that there should be some command that operates with superuser privilege, whatever the nominal state indicated by the data. Since the above script was being run by postgres, it should all have been capable of being executed. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "There is a way that seems right to a man, but in the end it leads to death." Proverbs 16:25
"Oliver Elphick" <olly@lfix.co.uk> writes:
> Restoring a full dump from 6.4.2 into 6.5:
> When the owner of a database does not have usesuper privilege in pg_shadow,
> it is not possible to recreate items that were created with superuser
> privilege, even if the superuser is running the script:
> QUERY: CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
> '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
> ERROR: Only users with Postgres superuser privilege are permitted to create
> a function in the 'C' language. Others may use the 'sql' language or the
> created procedural languages.
Hmm. This seems wrong; if the function was created by the superuser
then it should have proowner set to the superuser, and pg_dump looks
like it does the right thing about reconnecting as the function owner
(assuming you used -z, which is now default but wasn't in 6.4.2...).
Is it possible that "plpgsql_call_handler" was somehow marked as being
owned by the database owner rather than the superuser? If so, I'd think
that that is the real bug.
> It would seem that there should be some command that operates with
> superuser privilege, whatever the nominal state indicated by the data.
I'd be worried about security holes if it's not designed very carefully...
> Since the above script was being run by postgres, it should all have
> been capable of being executed.
I wonder whether we need a notion of "effective" and "real" user ID,
such as most Unix systems have. Then it'd be possible for the system
to know "I may be creating objects on behalf of user X, but I really
am the superuser" and apply protection checks appropriately. This'd
be a much more elegant solution than \connect for pg_dump scripts,
since the whole script would run in a single superuser session and just
do a SET VARIABLE or something to indicate which user would be the owner
of created objects.
However, that's not going to happen for 6.5. For a short-term fix, we
need to look at why pg_dump didn't reconnect as superuser before trying
to create that C function.
regards, tom lane
Tom Lane wrote: >Hmm. This seems wrong; if the function was created by the superuser >then it should have proowner set tothe superuser, and pg_dump looks >like it does the right thing about reconnecting as the function owner >(assuming youused -z, which is now default but wasn't in 6.4.2...). Ah... looking back, I see that I did not use -z. Using -z, it works OK. >I wonder whether we need a notion of "effective" and "real" user ID, >such as most Unix systems have. Then it'd be possiblefor the system >to know "I may be creating objects on behalf of user X, but I really >am the superuser" and applyprotection checks appropriately. This'd >be a much more elegant solution than \connect for pg_dump scripts, >sincethe whole script would run in a single superuser session and just >do a SET VARIABLE or something to indicate whichuser would be the owner >of created objects. I definitely agree with that. It's also needed in order to restrict password manipulation of other users' passwords to the superuser alone. -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Fear not, for I am with thee; be not dismayed, for I am thy God. I will strengthen thee and I will help thee; yea, I will uphold thee with the right hand ofmy righteousness." Isaiah 41:10
> Tom Lane wrote: > >Hmm. This seems wrong; if the function was created by the superuser > >then it should have proowner set to the superuser, and pg_dump looks > >like it does the right thing about reconnecting as the function owner > >(assuming you used -z, which is now default but wasn't in 6.4.2...). > > Ah... looking back, I see that I did not use -z. > > Using -z, it works OK. -z is now default in 6.5. > > >I wonder whether we need a notion of "effective" and "real" user ID, > >such as most Unix systems have. Then it'd be possible for the system > >to know "I may be creating objects on behalf of user X, but I really > >am the superuser" and apply protection checks appropriately. This'd > >be a much more elegant solution than \connect for pg_dump scripts, > >since the whole script would run in a single superuser session and just > >do a SET VARIABLE or something to indicate which user would be the owner > >of created objects. > > I definitely agree with that. It's also needed in order to restrict > password manipulation of other users' passwords to the superuser alone. > > -- > Vote against SPAM: http://www.politik-digital.de/spam/ > ======================================== > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP key from public servers; key ID 32B8FAA1 > ======================================== > "Fear not, for I am with thee; be not dismayed, > for I am thy God. I will strengthen thee and I will > help thee; yea, I will uphold thee with the right hand > of my righteousness." Isaiah 41:10 > > > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026