Re: Editing config files which are not in database directory

Debian server standard installation places pg_hba.conf, pg_ident.conf,
postgresql.conf , start.conf files to /etc/postgresql/8.3/data
Database cluster is in /var/lib/postgresql/8.3/data

Trying to edit pg_hba.conf and postgresql.conf from pgAdmin 1.10.0 from
windows causes errors and shows empty file:

ERROR: absolute path not allowed
CONTEXT: SQL function "pg_file_length" statement 1
STATEMENT: SELECT pg_file_read('/etc/postgresql/8.3/data/pg_hba.conf', 0,
pg_file_length('/etc/postgresql/8.3/data/pg_hba.conf'))

ERROR: could not stat file "postgresql.conf": No such file or directory
CONTEXT: SQL function "pg_file_length" statement 1
STATEMENT: SELECT pg_file_read('postgresql.conf', 0,
pg_file_length('postgresql.conf'))

How to edit conf files from pgAdmin in this case?

Andrus.

2009/10/1 Andrus <kobruleht2(at)hot(dot)ee>:
> Debian server standard installation places pg_hba.conf, pg_ident.conf,
> postgresql.conf , start.conf files to  /etc/postgresql/8.3/data
> Database cluster is in /var/lib/postgresql/8.3/data
>
> Trying to edit pg_hba.conf  and postgresql.conf  from pgAdmin 1.10.0 from
> windows causes errors and shows empty file:
>
> ERROR:  absolute path not allowed
> CONTEXT:  SQL function "pg_file_length" statement 1
> STATEMENT:  SELECT pg_file_read('/etc/postgresql/8.3/data/pg_hba.conf', 0,
> pg_file_length('/etc/postgresql/8.3/data/pg_hba.conf'))
>
> ERROR:  could not stat file "postgresql.conf": No such file or directory
> CONTEXT:  SQL function "pg_file_length" statement 1
> STATEMENT:  SELECT pg_file_read('postgresql.conf', 0,
> pg_file_length('postgresql.conf'))
>
> How to edit conf files from pgAdmin in this case?

Unless you're running pgAdmin directly on the server, you can't I'm
afraid. This is a security restriction in the adminpack contrib module
in PostgreSQL itself.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

>Unless you're running pgAdmin directly on the server, you can't I'm
afraid. This is a security restriction in the adminpack contrib module
in PostgreSQL itself.

postgresql superuser has access to those files. So this is is a bug:
implementation restricts access granted by Linux file system.
Also in this case pgAdmin should *not* open bogus files for editing.

Btw. Using file functions is a joke. Probably for this reason they are not
added to postgresql core.

Can we create long-waited patch to postgres to allow read log, postgres and
pg_hba files directly, without using file system and
writing postgres and pg_hba files directly.

Long time ago I posted the idea of creating system table pg_conf

Then for reading we can use

SELECT postgresql, pg_hba
FROM pg_conf

and for writing

UPDATE pg_conf SET pg_hba= '... new value '

Then postgresql can translate those requests to file system calls itself.

Andrus.

On Thu, Oct 1, 2009 at 10:02 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
>> Unless you're running pgAdmin directly on the server, you can't I'm
>
> afraid. This is a security restriction in the adminpack contrib module
> in PostgreSQL itself.
>
> postgresql superuser has access to those files. So this is is a bug:
> implementation restricts access granted by Linux file system.

Feel free to try to convince the PostgreSQL committers of that. I've
long since given up.

> Also in this case pgAdmin should *not* open bogus files for editing.

pgAdmin doesn't know what it's opening until it looks at it - and as a
one-line config file with a single include directive or comment is
technically valid, and we intentionally don't hard-code the allowable
directives into pgAdmin (consider user defined GUCs for add-on
modules), it's not completely straightforward to work out if a file is
what you would call valid or not.

> Btw. Using file functions is a joke. Probably for this reason they are not
> added to postgresql core.
>
> Can we create long-waited patch to postgres to  allow read log, postgres and
> pg_hba files directly, without using file system and
> writing postgres and pg_hba files directly.
>
> Long time ago I posted the idea of creating system table pg_conf

It's not that simple because the system needs to be configured before
the server is brought up, and the config file needs to be readable by
the postmaster before it gets anywhere close to being able to access
the database.

> Then for reading we can use
>
> SELECT postgresql, pg_hba
> FROM pg_conf
>
> and for writing
>
> UPDATE pg_conf SET pg_hba= '... new value '
>
> Then postgresql can translate those requests to file system calls itself.

Also not that simple. This has been discussed at length in
pgsql-hackers. How do you deal with comments in the config file? What
about overriding config directives? What about situations where the
distribution breaks the config into multiple files to avoid
complications with upgrades?

There's a *lot* that would have to be done to implement such a system,
and much as I'd like to have these features, it's not something for
this forum to figure out.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

> pgAdmin doesn't know what it's opening until it looks at it - and as a
one-line config file with a single include directive or comment is
technically valid, and we intentionally don't hard-code the allowable
directives into pgAdmin (consider user defined GUCs for add-on
modules), it's not completely straightforward to work out if a file is
what you would call valid or not.

Server returns error for file operation.
pgAdmin must know that if file open or seek command returns error, file
cannot be opened and should not open editing window.
Currently it opens editing window.
No additional hard coding is required.

> It's not that simple because the system needs to be configured before
the server is brought up, and the config file needs to be readable by
the postmaster before it gets anywhere close to being able to access
the database.

data storage format should remain same, plain text files.

> Also not that simple. This has been discussed at length in
pgsql-hackers. How do you deal with comments in the config file? What
about overriding config directives?

This is implemented in pgAdmin: it allows to edit files with comments in
separate lines.
So this code can be copied from pgAdmin.

sadly, order, where (search) and grouping is not available in pgadmin
editor.

> What about situations where the
distribution breaks the config into multiple files to avoid
complications with upgrades?

In every line there can be reference to source file.
Lines with write access can written back to config files.

Andrus.