getting off the ground with postgresql

sneakyimp

ok I have managed to install postgresql on my server and i've successfully added a database and user. HOWEVER, i can't figure out how to setup Postgresql and phppgadmin to work together.

Ivisit the phppgadmin page which seems to work and prompts me for login but then i cannot log in no matter what. I checked the postgresql log file and it appears that postgresql is trying to use an ident server which i don't think i have.

I've seen some docs that talk about making changes to pg_hba.conf but this looks a bit scary to me. I'm accustomed to the mysql/phpmyadmin approach which has a root user who can create databases and user accounts with access to them. I'd rather not have 'trust mode' on if I can help it as that sounds a bit like a security risk.

Sxooter

Try setting your connection to use md5 authentication.

The reason PostgreSQL has pg_hba.conf is so that you can't lock yourself out of your own database, and, even if someone were to hack into the db via, say SQL injection, they couldn't change the authentication methods or IP addresses accepted without hacking into the box.

Trust mode isn't a huge security risk as long as it's only set to let, say localhost or local unix sockets in. Ident will only generally work if pgsql is on the same box as the programs accessing it.

sneakyimp

I have been looking at the pg_hba.conf and i'm still wondering what line might give any LOCAL user access to their own databases and nothing else. I'm really hoping to avoid having to maintain a list of users/groups/passwords and who has the right to which database. i can't figure out what line to put in the conf file that will do this.

I'm also concerned about denying the superuser access to template1 - the postgre docs say this is particularly bad to do. however, as far as i know, the superuser has no password. how would i log in as superuser to even test this?

I'm tempted to put in a line like this:

local all all md5

but i'm concerned that userX might see the db info belong to userY

Here is my current conf which is basically the default

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
# Database administrative login by UNIX sockets
local   all         postgres                                        ident sameuser
#
# All other connections by UNIX sockets
local   all         all                                             ident sameuser
#
# All IPv4 connections from localhost
host    all         all         127.0.0.1         255.255.255.255   ident sameuser
#
# All IPv6 localhost connections
host    all         all         ::1               ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff        ident sameuser
host    all         all         ::ffff:127.0.0.1/128                ident sameuser
#
# reject all other connection attempts
host    all         all         0.0.0.0           0.0.0.0           reject

Sxooter

Change this line
local all postgres ident sameuser
to this:
local all postgres ident all

The postgres superuser needs access to all the databases to do things like backup and create users etc...

If someone on the local machine has convinced the OS that they're postgres, you've already lost, as they now have access to the postgres user's files.

sneakyimp

ok i changed that line and i had already changed this one:

# JTA - original line
#host    all         all         127.0.0.1         255.255.255.255   ident sameuser

to this:

# JTA - my attempt to enable phppgadmin login
host    all         all         127.0.0.1         255.255.255.255   md5

even without your change sxooter, i seemed to have success logging into psql and viewing my tables. The other change I made also let me login to postgresql using phppgadmin. but here is my question: WHY CAN I NOT SEE MY DATABASE?

I created a database called db1 using psql as user postgres. i then granted all privileges to user1 on db db1 like this:

GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;

When I login using phppgadmin i am permitted to login but no databases appear. I see a folder with my server's name there and when i click on the server name the right frame loads and says "NO DATABASES FOUND".

what do i need to do to get this working?

sneakyimp

The change you just recommended prevented me from using psql to login so i changed it back to the original:
local all postgres ident sameuser

and i can once again login to psql as postgres.

still having my problem seeing any databases when i use phppgadmin. i have posted their forum as well.

grrrrr...

Sxooter

Not sure what to tell you on phppgadmin, as I don't use it. I generally just use psql.

If you log in as postgres and do \d can you see your databases?

sneakyimp

\d doesn't give me anything:

devserver:~# su - postgres
postgres@devserver:~$ psql template1
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# \d
No relations found.

however, \l does show me my databases:

template1=# \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
    db1    | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(3 rows)

template1=#

Sxooter

sorry, I meant /l. I'm tired. Been a long week and it's only tuesday.

anyway, you should be able to just get rid of the sameuser after the ident line for the postgres user and be fine.

csn

Hey, what's up Sxooter?!? Long week??

Sxooter wrote:
Try setting your connection to use md5 authentication.

The reason PostgreSQL has pg_hba.conf is so that you can't lock yourself out of your own database, and, even if someone were to hack into the db via, say SQL injection, they couldn't change the authentication methods or IP addresses accepted without hacking into the box.

Trust mode isn't a huge security risk as long as it's only set to let, say localhost or local unix sockets in. Ident will only generally work if pgsql is on the same box as the programs accessing it.

I've wondered about pg_hba.conf ever since I started using Postgresql, and thought I liked mysql's approach somewhat better (permissions in the database itself). I still get somewhat confused every time I mess with PG's config files (mainly remembering what all the options are -- like IDENT, etc.). But I guess the security reasons make sense.

As for getting locked out of a database, mysql, for example, has a startup option that skips loading of permissions so you can log in as anybody. Had to do that myself a few time, mostly for clients who forgot/didn't know their root password.

sneakyimp

i discovered the problem i was having was due to the fact that i would log in as user_x which didn't own the database. i can't remember which setting it is (pgadmin or postgre) but i had turned on the 'owned db only' setting.

i deleted the db and created a new one owned by user_x and it is now visible.

i like mysql's approach better i think too....but postgre has some more advanced features i think i'll need - like partitioning (thanks sxooter!)