Обсуждение: Questions on PostGreSQL Authentication mechanism...

On 01/02/10 07:35, dipti shah wrote:
>
> Moreover, anyone can connect to databases as postgres user without giving
> password.
>
> I am not aware how above setup has been made but I want to get rid of them.
> Could anyone please help me in below questions?

You'll want to read Chapter 19 of the manuals followed by Chapter 20.
http://www.postgresql.org/docs/8.4/static/client-authentication.html
http://www.postgresql.org/docs/8.4/static/user-manag.html

 >    1. When user connects to TechDB database(or any other) as a "postgres"
 > user, it should ask for password.

I would guess your pg_hba.conf file is set to allow "trust" access. You
will probably want "md5" passwords. You can also GRANT access to
databases using the permissions system.

 >    2. Remove all above users(listed with \du) and create someof users and
 > they will have only table creating privileges.


You can DROP USER (or DROP ROLE) to remove users, but you'll want to
reallocate any tables they own. You can GRANT and REVOKE various
permissions, but I don't think you can just create tables without being
able to access them afterwards. You could write a function that does
that for you though.

HTH

--
   Richard Huxton
   Archonet Ltd

On 01/02/10 10:24, dipti shah wrote:
> Thanks Richard. those chapters are very useful. I got to know most of
> concepts but didn't find the location of pg_hba.conf file so that I can
> verify it. I have connected to my database using "postgres" user. Could you
> tell me how to open pg_hba.conf file?

It should be with your other configuration files: postgresql.conf,
pg_ident.conf.

Where will depend on how you installed it. If you're on Windows, it's
probably in the main PostgreSQL folder on drive C:

If a package manager on Linux/Unix look in /etc/postgresql/...

If you compiled from source, probably something like
/usr/local/postgresql/data/

It's a text file and you'll need to restart PostgreSQL to pick up your
new settings.

--
   Richard Huxton
   Archonet Ltd

dipti shah escribió:
> Thanks Richard. those chapters are very useful. I got to know most of
> concepts but didn't find the location of pg_hba.conf file so that I can
> verify it. I have connected to my database using "postgres" user. Could you
> tell me how to open pg_hba.conf file?

Run this:
    SHOW hba_file;

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 02/02/10 09:55, dipti shah wrote:
> Thanks Richard and Alvaro. The "show hba_file" is great solution. Thanks a
> ton. Could you tell me from where to get all such commands?

All the configuration settings are listed in Chapter 18:
http://www.postgresql.org/docs/8.4/static/runtime-config.html

You'll also find details in the SQL reference for SET and SHOW:
http://www.postgresql.org/docs/8.4/static/sql-set.html
http://www.postgresql.org/docs/8.4/static/sql-show.html

"SHOW ALL" will list all the settings in one big table.

--
   Richard Huxton
   Archonet Ltd

On 02/02/10 09:58, dipti shah wrote:
> Techdb=# show hba_file;
>                 hba_file
> --------------------------------------
>   /etc/postgresql/8.4/main/pg_hba.conf
> (1 row)

Ah! you're running a Debian-based system by the look of it.

> Moreover, is there anyway to view content of this file from stored in above
> location "Techdb" command prompt itself.
>
> Techdb=# cat  /etc/postgresql/8.4/main/pg_hba.conf;
> ERROR:  syntax error at or near "cat"
> LINE 1: cat  /etc/postgresql/8.4/main/pg_hba.conf;
>          ^

You would normally do so from the system shell rather than psql.
However, you can "shell out" with a backslash command in psql:
\! cat /etc/...

You might not have permission to view that file.

A full list of psql backslash commands are available in the manual
(client applications), psql man-page and by doing \? (or \h for sql help).

HTH

--
   Richard Huxton
   Archonet Ltd

dipti shah escribió:
> Techdb=# show hba_file;
>                hba_file
> --------------------------------------
>  /etc/postgresql/8.4/main/pg_hba.conf
> (1 row)
>
> Moreover, is there anyway to view content of this file from stored in above
> location "Techdb" command prompt itself.
>
> Techdb=# cat  /etc/postgresql/8.4/main/pg_hba.conf;

Probably pg_read_file():

select pg_read_file('pg_hba.conf', 0, 8192);

Note that pg_read_file only allows paths relative to $PGDATA, which is
what you get from SHOW data_directory;

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:
> dipti shah escribió:
>> Techdb=# show hba_file;
>>                hba_file
>> --------------------------------------
>>  /etc/postgresql/8.4/main/pg_hba.conf
>> (1 row)
>>
>> Moreover, is there anyway to view content of this file from stored in
>> above
>> location "Techdb" command prompt itself.
>>
>> Techdb=# cat  /etc/postgresql/8.4/main/pg_hba.conf;
>
> Probably pg_read_file():
>
> select pg_read_file('pg_hba.conf', 0, 8192);
>
> Note that pg_read_file only allows paths relative to $PGDATA, which is
> what you get from SHOW data_directory;
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Since the pg_hba.conf file is located in the /etc path, the pg_read_file
command fails with an error of

"could not open file "pg_hba.conf" for reading: No such file or direcotry"

It also won't allow use of the absolute path.

The answer I found was to use the following command:

postgres=# \! exec cat /etc/postgresql/8.3/main/pg_hba.conf

Tim
--
Timothy J. Bruce

Registered Linux User #325725

Tim Bruce - Postgres escribió:
> On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:

> > Probably pg_read_file():
> >
> > select pg_read_file('pg_hba.conf', 0, 8192);
> >
> > Note that pg_read_file only allows paths relative to $PGDATA, which is
> > what you get from SHOW data_directory;
>
> Since the pg_hba.conf file is located in the /etc path, the pg_read_file
> command fails with an error of
>
> "could not open file "pg_hba.conf" for reading: No such file or direcotry"

Hmm, yeah, that's a shortcoming of the debian packaging, no doubt.  Does
it not install symlinks in the actual data directory?  If not, that
should be reported as a bug ...


> It also won't allow use of the absolute path.

That's by design, yes.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, 2010-02-02 at 16:09 -0300, Alvaro Herrera wrote:
> Tim Bruce - Postgres escribió:
> > On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:
>
> > > Probably pg_read_file():
> > >
> > > select pg_read_file('pg_hba.conf', 0, 8192);
> > >
> > > Note that pg_read_file only allows paths relative to $PGDATA, which is
> > > what you get from SHOW data_directory;
> >
> > Since the pg_hba.conf file is located in the /etc path, the pg_read_file
> > command fails with an error of
> >
> > "could not open file "pg_hba.conf" for reading: No such file or direcotry"
>
> Hmm, yeah, that's a shortcoming of the debian packaging, no doubt.  Does
> it not install symlinks in the actual data directory?  If not, that
> should be reported as a bug ...

I disagree. Debian/Ubuntu use the postgresql.conf facilities to have a
different postgresql.conf.

This is a limitation within PostgreSQL.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

Joshua D. Drake escribió:
> On Tue, 2010-02-02 at 16:09 -0300, Alvaro Herrera wrote:
> > Tim Bruce - Postgres escribió:
> > > On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:
> >
> > > > Probably pg_read_file():
> > > >
> > > > select pg_read_file('pg_hba.conf', 0, 8192);
> > > >
> > > > Note that pg_read_file only allows paths relative to $PGDATA, which is
> > > > what you get from SHOW data_directory;
> > >
> > > Since the pg_hba.conf file is located in the /etc path, the pg_read_file
> > > command fails with an error of
> > >
> > > "could not open file "pg_hba.conf" for reading: No such file or direcotry"
> >
> > Hmm, yeah, that's a shortcoming of the debian packaging, no doubt.  Does
> > it not install symlinks in the actual data directory?  If not, that
> > should be reported as a bug ...
>
> I disagree. Debian/Ubuntu use the postgresql.conf facilities to have a
> different postgresql.conf.

That's all very good but in doing so they disabled the ability to edit
the files through pg_file_read and pg_file_write, so this patch needs
more thought or handling on their part.  It works fine in pristine
PostgreSQL, so it's not our bug.  I assume pgAdmin is unable to edit the
config files in Debian due to this problem.

For example, perhaps there could be a new pair of functions
pg_read_hba_file/pg_write_hba_file that would work even if the files are
placed in other directories, but they (Debian) would need to propose it.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, 2010-02-02 at 16:09 -0300, Alvaro Herrera wrote:
> Tim Bruce - Postgres escribió:
> > On Tue, February 2, 2010 08:23, Alvaro Herrera wrote:
>
> > > Probably pg_read_file():
> > >
> > > select pg_read_file('pg_hba.conf', 0, 8192);
> > >
> > > Note that pg_read_file only allows paths relative to $PGDATA, which is
> > > what you get from SHOW data_directory;
> >
> > Since the pg_hba.conf file is located in the /etc path, the pg_read_file
> > command fails with an error of
> >
> > "could not open file "pg_hba.conf" for reading: No such file or direcotry"
>
> Hmm, yeah, that's a shortcoming of the debian packaging, no doubt.  Does
> it not install symlinks in the actual data directory?  If not, that
> should be reported as a bug ...

I disagree. Debian/Ubuntu use the postgresql.conf facilities to have a
different postgresql.conf.

This is a limitation within PostgreSQL.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

dipti shah wrote:
> I am connected to database as postgres user.
>
> '\!exec ..' doesn't work if I connect to the database from other host
> but it does work if I connect to the database from server where I have
> PostGreSQL installed. pg_read_file doesn't work in any case.
>
> Techdb=# \! exec cat /etc/postgresql/8.4/main/pg_hba.conf
> cat: cannot open /etc/postgresql/8.4/main/pg_hba.conf

thats because psql runs the command on the LOCAL server that the user is
running psql on.   would be all kinda security problems if a user could
run commands on the remote server without having logged onto it as a
regular user.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> For example, perhaps there could be a new pair of functions
> pg_read_hba_file/pg_write_hba_file that would work even if the files are
> placed in other directories, but they (Debian) would need to propose
> it.

I don't remember they had to provide those GUCs:

  http://www.postgresql.org/docs/8.4/static/runtime-config-file-locations.html

  hba_file (string)
    Specifies the configuration file for host-based authentication
    (customarily called pg_hba.conf). This parameter can only be set at
    server start

The bug certainly is on PostgreSQL for providing the facility to
relocate the hba_file without providing any way for pgadmin and other
utilities to handle the situation?

Regards,
--
dim