16

I have the following code which connects to a database on my remote server (the connection script resides on the same server):

Database::$ErrorHandle = new PDO('pgsql:host=111.222.33.44;dbname=mydatabase;', 'postgres', 'mypassword', $db_settings);

The problem is I can change the password to be anything at all and the connection is still made! Like seriously what the hell!?!

Can my database be connected to (providing you know the IP and db name) by anyone from a PHP script running on a different server?

How can I enforce passwords, I have looked at the following stack overflow page and did what they said but still no luck: How to change PostgreSQL user password?

I am running Ubuntu 12.04 server with PHP 5.5 and Apache2

Community
  • 1
  • 1
Kevin Orriss
  • 1,012
  • 3
  • 11
  • 24

1 Answers1

11

Off course your postgresql database can be properly configured to only connect with authenticated users even certain users (Roles in Postgres) from certain IPs/sockets.

Some considerations:

  • Do you see data? Or can you just connect to the server? Can you list the databases?

  • Look at your pg_hba.conf and setup the proper permissions, per role per database per source

  • Did you grant access to the mydatabase to everyone? Which roles did you grant access?

  • Does the database have its tables in the public scheme? And granted access to the public?

  • Yes, with this configuration everyone who knows your IP and database name can connect to your database.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
stUrb
  • 6,612
  • 8
  • 43
  • 71
  • Thank you. I looked in pg_hba.conf and I noticed this line `host all all 111.222.33.44/24 trust` and changed it to `host all all 111.222.33.44/24 md5` and now my passwords work as expected. I will mark your answer as correct when it lets me. – Kevin Orriss Jan 10 '14 at 21:26
  • Glad to help. Do you want to only allow local connections? Or do you want remote users to use the database (with the right credentials?) If so look into the `local` rows instead of host-based connections. – stUrb Jan 10 '14 at 21:28
  • It's only me that has access to the server (hopefully) so I am not too bothered about remote connections. What are `host` based connections like the one I have? So are you saying that the `local` rows are handling the remote connections? Seems a little bit like a lie... (Although I am pretty sure it's not a lie and I am just an idiot haha) – Kevin Orriss Jan 10 '14 at 21:39
  • Local = local connections/sockets; `host` tcp/ip connections. If you only access your database from the machine the server runs on (eg via your WP-site), don't use host-rows. So If you don't need remote IP's connecting to your database-server comment the host-rows! – stUrb Jan 10 '14 at 21:43
  • Ok, I shall do that now. What does `peer` mean? – Kevin Orriss Jan 10 '14 at 21:46
  • Do make sure you *have* a proper local row defined (other wise you lock yourself out) and your connection detail is changed to: `localhost` instead of the IP adress. – stUrb Jan 10 '14 at 21:48
  • [Peer:](http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html) Obtain the client's operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections. See Section 19.3.7 for details. – stUrb Jan 10 '14 at 21:49
  • Had similar confusion and reading this (https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7/#peer-authentication) helped me understand what was happening a bit more – lampShadesDrifter Dec 17 '19 at 23:50