Обсуждение: Backing up and restoring a database with the SELinux pg_user problem.
Hello everyone, I have a Postgresql 7.4 database that was created on a Fedora 3 box *prior* to turning off enforcing mode in SELinux, but then has since been used for some time in permissive mode. Since everything seemed to be working, I never knew there was a problem until I attempted to set up backups a few days later and found that the pg_user table/view did not exist. Now, I am seemingly unable to backup any database or table, since any use of pg_dump fails due to the missing pg_user table. Does anyone have any suggestions on this problem? How can I recreate pg_user? I would expect this should be possible given there should only be the default postgres super user and my gforge user installed, and the latter works fine, as this is a running GForge db that I wish to shutdown over the weekend and repair. I have read all prior discussions on the topic on various postgresql.org mailing lists and none of their suggestions helped me, nor seemed to help any previously afflicted user. E.g., these threads http://archives.postgresql.org/pgsql-general/2004-08/msg01286.php http://archives.postgresql.org/pgsql-sql/2005-01/msg00167.php I have also read the Fedora 3 bug report on this issue. https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=142607 Thanks very much, Joe Kiniry
Joseph Kiniry <kiniry@acm.org> writes:
> Does anyone have any suggestions on this problem? How can I recreate
> pg_user?
Sure, just run the CREATE VIEW command executed by initdb; it's in the
initdb shell script. Note that all the objects created that way (with
"postgres <<EOF" shell constructs) will be missing --- you'll likely
find you have to create some more missing bits too.
FWIW, I think that the latest selinux-policy and postgresql RPMs should
fix this problem.
regards, tom lane
Hello Tom, On Feb 4, 2005, at 12:37 AM, Tom Lane wrote: > Joseph Kiniry <kiniry@acm.org> writes: > >> Does anyone have any suggestions on this problem? How can I recreate >> pg_user? >> > Sure, just run the CREATE VIEW command executed by initdb; it's in the > initdb shell script. Note that all the objects created that way (with > "postgres <<EOF" shell constructs) will be missing --- you'll likely > find you have to create some more missing bits too. This is as I expected, and I have been able to recreate several tables & views necessary in my GForge database, ignoring the botched template? databases, of course, since I'll be recreating those once I have my data. I'm currently blocked on the system catalog schema "pg_catalog"; whence is it initialized? I have looked though all initdb-related scripts, SQL files, and BKI files and have found several references to pg_catalog, but I have not found its definition/initialization. > FWIW, I think that the latest selinux-policy and postgresql RPMs should > fix this problem. Yes, they do, but I still have to get my data out first. :) Thanks very much. Note that only small hints are needed for me; I have years of experience with various databases &c but obviously only months of experience with SELinux. :) Best, Joe Kiniry
Joseph Kiniry <kiniry@acm.org> writes:
> I'm currently blocked on the system catalog schema "pg_catalog";
> whence is it initialized?
That row in pg_namespace is missing, you mean? That's very odd ... what
rows do you see in pg_namespace? That should be loaded as part of the
basic bootstrap operation, and if basic bootstrap had failed you'd
definitely not have failed to notice ;-)
> I have looked though all initdb-related
> scripts, SQL files, and BKI files and have found several references to
> pg_catalog, but I have not found its definition/initialization.
The definition/initialization is basically driven from macros in
src/include/catalog/pg_namespace.h; in particular all the rows defined
by DATA macros in that file should have been created during bootstrap.
You might look in the .bki file to verify that there is a section
creating and loading pg_namespace.
regards, tom lane
Hi all,
Returning to a really old problem that is biting me again...
We upgraded our server (from Fedora Core 3 to Core 5), and thus
upgraded Postgres. Unfortunately, we did not know that the new
version of Postgres included with 5 uses a different data format than
that included with 3. Thus, we need to upgrade our database. But
one can only do that by doing a dump, and initdb, and a restore.
But... we cannot do a dump because our database is borked because the
original initdb partially failed due to the SELinux bug in FC3 (see
the discussion from back in Feb 2005 on this list and RedHat bugzilla
id 149237). Tom and I exchanged some emails about this back in
February, the last of which was:
On 4 Feb, 2005, at 15:19, Tom Lane wrote:
> Joseph Kiniry <kiniry@acm.org> writes:
>> I'm currently blocked on the system catalog schema "pg_catalog";
>> whence is it initialized?
>
> That row in pg_namespace is missing, you mean? That's very odd ...
> what
> rows do you see in pg_namespace? That should be loaded as part of the
> basic bootstrap operation, and if basic bootstrap had failed you'd
> definitely not have failed to notice ;-)
>
>> I have looked though all initdb-related
>> scripts, SQL files, and BKI files and have found several
>> references to
>> pg_catalog, but I have not found its definition/initialization.
>
> The definition/initialization is basically driven from macros in
> src/include/catalog/pg_namespace.h; in particular all the rows defined
> by DATA macros in that file should have been created during bootstrap.
> You might look in the .bki file to verify that there is a section
> creating and loading pg_namespace.
>
> regards, tom lane
As I said above, I have re-examined, and executed if necessary, by
hand, all sql commands in initdb and postgres.bki, but it seems that
pg_catalog is still screwed up. Attempting to dump, or perform
several other actions results in failures of the form:
ERROR: 42P01: relation "pg_user" does not exist
LOCATION: RangeVarGetRelid, namespace.c:193
STATEMENT: SELECT (SELECT usename FROM pg_user WHERE usesysid =
datdba) as dba\
, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database
WHERE dat\
name = 'gforge'
Tom asked what pg_namespace looks like, and here it is:
gforge=# select * from pg_namespace;
nspname | nspowner | nspacl
------------+----------+---------------------------------------
pg_toast | 1 |
pg_temp_1 | 1 |
pg_catalog | 1 | {postgres=U*C*/postgres,=U/postgres}
public | 1 | {postgres=U*C*/postgres,=UC/postgres}
(4 rows)
pg_catalog has tons of stuff in it, so it looks like bki
initialisation worked.
Just to be clear, all database operations for our GForge install work
fine, we just cannot backup our database, and thus we cannot upgrade
postgres.
So why can I see pg_user and yet pg_dump fails?
gforge=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil| useconfig
----------+----------+-------------+----------+-----------+----------
+----------+-----------
postgres | 1 | t | t | t | ********
| |
gforge | 100 | t | f | f | ********
| |
(2 rows)
How do I get my data out of this database?
Thanks,
Joe
---
Joseph Kiniry
School of Computer Science and Informatics
UCD Dublin
http://secure.ucd.ie/
http://srg.cs.ucd.ie/
Joseph Kiniry <kiniry@acm.org> writes:
> As I said above, I have re-examined, and executed if necessary, by
> hand, all sql commands in initdb and postgres.bki, but it seems that
> pg_catalog is still screwed up. Attempting to dump, or perform
> several other actions results in failures of the form:
> ERROR: 42P01: relation "pg_user" does not exist
> LOCATION: RangeVarGetRelid, namespace.c:193
> STATEMENT: SELECT (SELECT usename FROM pg_user WHERE usesysid =
> datdba) as dba\
> , pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database
> WHERE dat\
> name = 'gforge'
> So why can I see pg_user and yet pg_dump fails?
> gforge=# select * from pg_user;
> [ works ]
Hmm ... you manually recreated the pg_user view you say? I wonder if
you mistakenly put it in the public schema instead of pg_catalog.
The quoted command from pg_dump is done after issuing
set search_path = pg_catalog;
so that nothing user-created will accidentally mess it up. If you
can still manually select from pg_user after issuing that same SET
command, then something is really seriously strange ...
If you find that indeed pg_user is in public, drop it there and
re-create it in pg_catalog. You'll need to be superuser to do
that but I don't think it'll require any more pushups than that.
regards, tom lane
Hi Tom, Thank you for the very quick response. I'll let Robin followup on this for tonight, as I'm heading to bed after a very long day. On 4 Jul, 2006, at 22:12, Tom Lane wrote: > Joseph Kiniry <kiniry@acm.org> writes: >> As I said above, I have re-examined, and executed if necessary, by >> hand, all sql commands in initdb and postgres.bki, but it seems that >> pg_catalog is still screwed up. Attempting to dump, or perform >> several other actions results in failures of the form: > >> ERROR: 42P01: relation "pg_user" does not exist >> LOCATION: RangeVarGetRelid, namespace.c:193 >> STATEMENT: SELECT (SELECT usename FROM pg_user WHERE usesysid = >> datdba) as dba\ >> , pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database >> WHERE dat\ >> name = 'gforge' > >> So why can I see pg_user and yet pg_dump fails? > >> gforge=# select * from pg_user; >> [ works ] > > Hmm ... you manually recreated the pg_user view you say? I wonder if > you mistakenly put it in the public schema instead of pg_catalog. > The quoted command from pg_dump is done after issuing > set search_path = pg_catalog; > so that nothing user-created will accidentally mess it up. If you > can still manually select from pg_user after issuing that same SET > command, then something is really seriously strange ... I did notice the above command and executed it by hand. But as to whether I did it all at the right time, in the right order, to ensure that everything is in the right schemas... :) > If you find that indeed pg_user is in public, drop it there and > re-create it in pg_catalog. You'll need to be superuser to do > that but I don't think it'll require any more pushups than that. We'll double-check this. > regards, tom lane Thanks again for the pointer, Joe --- Joseph Kiniry School of Computer Science and Informatics UCD Dublin http://secure.ucd.ie/ http://srg.cs.ucd.ie/