Thread: Checkpoint request failed, permission denied

Cory Isaacson <cory.isaacson@compuflex.com> writes:
> When I try and manually perform a checkpoint with version 8.3 on CentOS 5 I
> get this error:

> ERROR:  could not link file "pg_xlog/000000010000000000000000" to
> "pg_xlog/000000010000000000000002" (initialization of log file 0, segment
> 2): Permission denied

> Any ideas what is causing this? The pg_xlog dir is owned by the postgres
> user, as are the log files. Also I have temporarily disabled SELinux to make
> sure that is not causing it.

It's very hard to see how that could be anything except an
ownership/permissions problem on the pg_xlog directory.  Are you sure
the directory is mode 700 as well as being owned by postgres?
(For that matter, are you sure the postmaster is running as postgres?)
Are you *really* sure selinux is disabled?

            regards, tom lane

They look right to me. Below are the permissions and process list. I ended
up rebuilding the data directory since it was just a test database, so far
so good. The permissions and setup were exactly the same before I did this.

I was repeatedly dropping and rebuilding this database, could that have
caused this? I also had some user processes stuck so frequently I would
restart postgresql as well.

Cory

[root@ittdev1 data]# ls -l pg_xlog
total 16416
-rw------- 1 postgres postgres 16777216 Sep 13 23:16
000000010000000000000000
drwx------ 2 postgres postgres     4096 Sep 13 22:19 archive_status
[root@ittdev1 data]# ps -ef|grep postgres
postgres 25752     1  0 Sep13 ?        00:00:00 /usr/bin/postmaster -p 5432
-D /var/lib/pgsql/data
postgres 25754 25752  0 Sep13 ?        00:00:00 postgres: logger process
postgres 25756 25752  0 Sep13 ?        00:00:00 postgres: writer process
postgres 25757 25752  0 Sep13 ?        00:00:00 postgres: wal writer process
postgres 25758 25752  0 Sep13 ?        00:00:00 postgres: autovacuum
launcher process
postgres 25759 25752  0 Sep13 ?        00:00:00 postgres: stats collector
process
postgres 26877 25752  0 00:22 ?        00:00:00 postgres: itt itt_dev
127.0.0.1(49593) idle
postgres 26879 25752  0 00:22 ?        00:00:00 postgres: itt itt_dev
127.0.0.1(49595) idle


> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Mon, 14 Sep 2009 10:29:54 -0400
> To: Cory Isaacson <cory.isaacson@compuflex.com>
> Cc: <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Checkpoint request failed, permission denied
>
> Cory Isaacson <cory.isaacson@compuflex.com> writes:
>> When I try and manually perform a checkpoint with version 8.3 on CentOS 5 I
>> get this error:
>
>> ERROR:  could not link file "pg_xlog/000000010000000000000000" to
>> "pg_xlog/000000010000000000000002" (initialization of log file 0, segment
>> 2): Permission denied
>
>> Any ideas what is causing this? The pg_xlog dir is owned by the postgres
>> user, as are the log files. Also I have temporarily disabled SELinux to make
>> sure that is not causing it.
>
> It's very hard to see how that could be anything except an
> ownership/permissions problem on the pg_xlog directory.  Are you sure
> the directory is mode 700 as well as being owned by postgres?
> (For that matter, are you sure the postmaster is running as postgres?)
> Are you *really* sure selinux is disabled?
>
> regards, tom lane

On Mon, Sep 14, 2009 at 8:52 AM, Cory Isaacson
<cory.isaacson@compuflex.com> wrote:
> [root@ittdev1 data]# ls -l pg_xlog
> total 16416
> -rw------- 1 postgres postgres 16777216 Sep 13 23:16
> 000000010000000000000000
> drwx------ 2 postgres postgres     4096 Sep 13 22:19 archive_status

What odes ls -ld pg_xlog say?

Cory Isaacson <cory.isaacson@compuflex.com> writes:
> They look right to me. Below are the permissions and process list. I ended
> up rebuilding the data directory since it was just a test database, so far
> so good. The permissions and setup were exactly the same before I did this.

> [root@ittdev1 data]# ls -l pg_xlog

This doesn't actually show the permissions or ownership of pg_xlog
itself, only its contents ...

            regards, tom lane

Here are the permissions on pg_xlog:

drwx------ 3 postgres postgres  4096 Sep 13 22:19 pg_xlog

And here is ls -ld:

[root@ittdev1 data]# ls -ld pg_xlog
drwx------ 3 postgres postgres 4096 Sep 13 22:19 pg_xlog

Thanks,

Cory


> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Mon, 14 Sep 2009 11:59:21 -0400
> To: Cory Isaacson <cory.isaacson@compuflex.com>
> Cc: <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Checkpoint request failed, permission denied
>
> Cory Isaacson <cory.isaacson@compuflex.com> writes:
>> They look right to me. Below are the permissions and process list. I ended
>> up rebuilding the data directory since it was just a test database, so far
>> so good. The permissions and setup were exactly the same before I did this.
>
>> [root@ittdev1 data]# ls -l pg_xlog
>
> This doesn't actually show the permissions or ownership of pg_xlog
> itself, only its contents ...
>
> regards, tom lane

Cory Isaacson <cory.isaacson@compuflex.com> writes:
> Here are the permissions on pg_xlog:
> drwx------ 3 postgres postgres  4096 Sep 13 22:19 pg_xlog

Well, that certainly looks right.  I'm back to suspecting selinux ...
have you tried "ls -Z"?  I'm not totally sure about RHEL5, but in
recent Fedora it should look like

drwx------. postgres postgres unconfined_u:object_r:postgresql_db_t:s0 pg_xlog

the "postgresql_db_t" bit being the actually critical part.

            regards, tom lane

Tom,

I think you may be right. There were some audit access denied messages. I
had SELinux in permissive mode, but its tricky to work with.

I generated a new SELinux rule using audit2allow, here is what it looks like
now. Do you think this is adequate?

Thanks,

Cory

[root@ittdev1 data]# ls -Z pg_xlog
-rw-------  postgres postgres root:object_r:postgresql_db_t
000000010000000000000000
drwx------  postgres postgres root:object_r:postgresql_db_t
archive_status



> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Mon, 14 Sep 2009 12:09:48 -0400
> To: Cory Isaacson <cory.isaacson@compuflex.com>
> Cc: <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Checkpoint request failed, permission denied
>
> Cory Isaacson <cory.isaacson@compuflex.com> writes:
>> Here are the permissions on pg_xlog:
>> drwx------ 3 postgres postgres  4096 Sep 13 22:19 pg_xlog
>
> Well, that certainly looks right.  I'm back to suspecting selinux ...
> have you tried "ls -Z"?  I'm not totally sure about RHEL5, but in
> recent Fedora it should look like
>
> drwx------. postgres postgres unconfined_u:object_r:postgresql_db_t:s0 pg_xlog
>
> the "postgresql_db_t" bit being the actually critical part.
>
> regards, tom lane

Cory Isaacson <cory.isaacson@compuflex.com> writes:
> I think you may be right. There were some audit access denied messages. I
> had SELinux in permissive mode, but its tricky to work with.

> I generated a new SELinux rule using audit2allow, here is what it looks like
> now. Do you think this is adequate?

If you're keeping the PG data directory in the standard place
(/var/lib/pgsql/data) then you shouldn't need any custom selinux rules.
What is more likely is that the directory accidentally acquired the
wrong selinux label while you were fooling around.  "restorecon" is the
easiest way to fix mistakes like that.

If you're trying to put the data directory in a nonstandard place then
you might need some custom rules.  This is beyond my personal experience
with selinux, but I seem to recall being told that as long as everything
in the data directory is labeled "postgresql_db_t" then it will work
no matter where it is.  What you would want the custom rule for is to
make sure that "restorecon" doesn't relabel the data directory to
something else if someone blindly runs it over the whole filesystem.

            regards, tom lane