Thread: Questions on PostGreSQL Authentication mechanism...
Hi, we have latest PostGreSQL setup and it allows everyone to connect. When I do \du, it gives following output and it is same for all users.
TechDB=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
---------------+-----------+-------------+-----------+-------------+-------------
sonal | no | no | no | no limit | {from_ldap}
sundar | no | no | no | no limit | {from_ldap}
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
---------------+-----------+-------------+-----------+-------------+-------------
sonal | no | no | no | no limit | {from_ldap}
sundar | no | no | no | no limit | {from_ldap}
...
..
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?
1. When user connects to TechDB database(or any other) as a "postgres" user, it should ask for password.
2. Remove all above users(listed with \du) and create someof users and they will have only table creating privileges.
Thanks.
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
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 <dev@archonet.com> wrote:
On 01/02/10 07:35, dipti shah wrote:You'll want to read Chapter 19 of the manuals followed by Chapter 20.
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?
http://www.postgresql.org/docs/8.4/static/client-authentication.html
http://www.postgresql.org/docs/8.4/static/user-manag.htmlI 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.
> 1. When user connects to TechDB database(or any other) as a "postgres"
> user, it should ask for password.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.
> 2. Remove all above users(listed with \du) and create someof users and
> they will have only table creating privileges.
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.
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 <alvherre@commandprompt.com> wrote:
dipti shah escribió:> Thanks Richard. those chapters are very useful. I got to know most ofRun this:
> 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?
SHOW hba_file;
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Techdb=# show hba_file;
hba_file
--------------------------------------
/etc/postgresql/8.4/main/pg_hba.conf
(1 row)
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;
^
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 <shahdipti1980@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,DipOn Mon, Feb 1, 2010 at 9:43 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:dipti shah escribió:> Thanks Richard. those chapters are very useful. I got to know most ofRun this:
> 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?
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
Wow!!..that was too quick. Thanks Richard.
On Tue, Feb 2, 2010 at 3:29 PM, Richard Huxton <dev@archonet.com> wrote:
On 02/02/10 09:55, dipti shah wrote:All the configuration settings are listed in Chapter 18: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?
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
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.
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
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 <postgres@tbruce.com> wrote:
> --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
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.
That makes sense.
Thanks,
Dipti
On Wed, Feb 3, 2010 at 12:08 PM, John R Pierce <pierce@hogranch.com> wrote:
dipti shah wrote: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.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
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