Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
Alvaro Herrera alvhe...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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? Thanks, Dip On Mon, Feb 1, 2010 at 9:43 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: 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/ http://www.commandprompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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; ERROR: syntax error at or near cat LINE 1: cat /etc/postgresql/8.4/main/pg_hba.conf; ^ Thanks, Dipti On Tue, Feb 2, 2010 at 3:25 PM, dipti shah shahdipti1...@gmail.com 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? Thanks, Dip On Mon, Feb 1, 2010 at 9:43 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: 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/ http://www.commandprompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
Wow!!..that was too quick. Thanks Richard. On Tue, Feb 2, 2010 at 3:29 PM, Richard Huxton d...@archonet.com wrote: 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
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 Herrerahttp://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
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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
Techdb=# select pg_read_file('pg_hba.conf', 0, 8192);
ERROR: could not open file pg_hba.conf for reading: No such file or
directory
Thanks,
Dipti
On Wed, Feb 3, 2010 at 12:14 AM, Tim Bruce - Postgres
postg...@tbruce.comwrote:
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/ 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
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
That makes sense. Thanks, Dipti On Wed, Feb 3, 2010 at 12:08 PM, John R Pierce pie...@hogranch.com wrote: 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.
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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? Thanks. On Mon, Feb 1, 2010 at 3:06 PM, Richard Huxton d...@archonet.com wrote: 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
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
