Help authenticating to PostgreSQL

[NotionCommotion]

Hi,  Trying PostgreSQL  for the first time but not making much progress.  Get peer failure when not including a host and Ident error when including a host. Never heard of Ident authentication until today and don't know for sure if I even have such a server running.  Using Centos7, PHP7.4 using remi's repo, and PostgreSQL 12 from their repo.  Any thoughts?  Thanks

 

try {
    //use Unix domain sockets
    $dbh = new PDO("pgsql:dbname=postgres", 'postgres', 'secret');
}
catch(Exception $e){
    echo($e->getMessage().PHP_EOL);
}
try {
    $dbh = new PDO("pgsql:host=localhost;dbname=postgres", 'postgres', 'secret');
}
catch(Exception $e){
    echo($e->getMessage().PHP_EOL);
}
try {
    $dbh = new PDO("pgsql:host=127.0.0.1;dbname=postgres", 'postgres', 'secret');
}
catch(Exception $e){
    echo($e->getMessage().PHP_EOL);
}

 

SQLSTATE[08006] [7] FATAL:  Peer authentication failed for user "postgres"
SQLSTATE[08006] [7] FATAL:  Ident authentication failed for user "postgres"
SQLSTATE[08006] [7] FATAL:  Ident authentication failed for user "postgres"

 

[requinix]

If the database is on the local machine then you should use peer authentication. When something connects locally, PostgreSQL will ask the system for the username on the connecting end. That'll be your php-fpm pool user. It then allows a connection to the database by that name.

IIRC,

1. Use a different user than "postgres". Create a new system user and update your php-fpm pool to use that username (which should mean creating a new pool if you have more than one site running). Restart, obviously.
2. With PostgreSQL, set up authentication for that new user.
3. Create a matching database and move all your data into it. Be careful about ownerships.
4. Update your PDO connection: no host so it connects locally, and drop the username (it's automatic) and password (not used).

[NotionCommotion]

Thanks requinix,  Your reply was very helpful.

I am still a little confused about users and roles, schemas and databases, and clusters.  Guess that is why you said "Be careful about ownerships".

Are not roles and (postgresql) owners the same thing?  Owners are operating system owners that are assigned postgresql roles, true?

php-fpm was originally set up as follows but I received an error and discovered that I needed to change user from apache to $pool.  Probably same thing for group?  listen.owner and listen.group remains as is, right?  Rest look okay?

/etc/httpd/conf.d/php.conf

...
Define PHP7_POOL_TESTING "proxy:unix:/run/php-fpm/testing.sock|fcgi://localhost"
...

/etc/php-fpm.d/testing.conf

[testing]
prefix = /run/php-fpm
user = apache    ;need to change to $pool
group = apache
listen.owner = apache
listen.group = apache
listen.mode = 0660
listen = $pool.sock
pm = ondemand
pm.max_children = 50
php_value[session.save_handler] = files
php_value[session.save_path]    = /var/lib/php/session
php_value[soap.wsdl_cache_dir]  = /var/lib/php/wsdlcache
slowlog = /var/log/php-fpm/testing-slow.log

Using superuser user/role postgres, I created role michael and then created a database for that role (all role's must have a database of same name, right?).  My desire was for this role to be the primary administrator but not quite have superuser status.  I then created role testing which is the same as the php-fpm pool user, and then tried to create the database under role michael but couldn't.  Shouldn't I be able to?  Ended up doing so using role postgres.

[michael@devserver www]$ sudo -u michael psql
psql (12.2)
Type "help" for help.

michael=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 michael   | michael  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

michael=> \du
                                    List of roles
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 testing    |                                                            | {}
 michael    | Create role, Create DB                                     | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

michael=> CREATE DATABASE testing OWNER testing;
ERROR:  must be member of role "testing"
michael=>

Eventually, was able to access the database via PDO.  Why is the Schema shown as "public"

[michael@devserver www]$ sudo -u testing psql
psql (12.2)
Type "help" for help.

testing=> \l
                                   List of databases
    Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
------------+------------+----------+-------------+-------------+-----------------------
 testing    | testing    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ... same as above
(5 rows)

testing=> \d
                    List of relations
 Schema |          Name           |   Type   |   Owner
--------+-------------------------+----------+------------
 public | playground              | table    | testing
 public | playground_equip_id_seq | sequence | testing
(2 rows)

testing=>

Thanks again!

[requinix]

- A role is a user.
- Easy mode: a database is a database, ignore schemas and just use "public".
- Don't worry about clusters.
- Ownership is... ownership. I mean, it's the same thing that it means in English. Owning.
- Apache and php-fpm can run as different users, if you want.
- No, roles don't need to have database with their name. It's just easy that way.
- You can't create a database and give ownership to somebody your role isn't associated with.

Use the postgres user to create the michael database, owner michael. Then reconnect as michael and do whatever you want.

[NotionCommotion]

Thanks again requinix,

A little off topic for the php coding help forum but relevant to this specific post.  I also installed pgAdmin on the same machine as PostgreSQL, however, cannot connect as shown by /var/lib/pgsql/12/data/log/postgresql-Tue.log

2020-03-17 12:24:49.921 UTC [2272] FATAL:  Ident authentication failed for user "michael"
2020-03-17 12:24:49.921 UTC [2272] DETAIL:  Connection matched pg_hba.conf line 82: "host    all             all             127.0.0.1/32            ident"

Since I am on the same machine, I used host 127.0.0.1.  But then the user running the webserver and thus pgAmin is apache and not michael, true?  I considered using PostgreSQL to set up authentication for apache but don't believe it is a good idea.  Was thinking of adding my remote client's IP or the server's IP to pg_hba.conf and use some authentication other than ident, however, this too doesn't seem right.

Maybe I should be using "service" instead of a username in the pgAdmin prompt but not sure what to include.

Any advise would be much appreciated.

 

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

 

[NotionCommotion]

Don't think it is ideal, but am able to connect after changing ident to trust for both local and host in /var/lib/pgsql/12/data/pg_hba.conf.

[requinix]

Take a look at the authentication configuration for PostgreSQL. "local" connections are peer (system username), while the "host" connections from the local machine are ident (username and password).

If you have a password set up for the michael user/role then this should work. But you shouldn't need one: remove the host from the pgAdmin configuration to connect by socket, keep the database and user (but you may want to switch back to postgres/postgres for those), drop the password, and I think drop either the username or role.

[requinix]

1 minute ago, NotionCommotion said:

Don't think it is ideal, but am able to connect after changing ident to trust for both local and host in /var/lib/pgsql/12/data/pg_hba.conf.

It's not. You're effectively dropping all authentication.

[NotionCommotion]

I previously didn't have permissions set up correctly for pgsql's log file so I didn't get the first log.

2020-03-17 20:32:45.678 UTC [2758] LOG:  could not connect to Ident server at address "127.0.0.1", port 113: Connection refused
2020-03-17 20:32:45.678 UTC [2758] FATAL:  Ident authentication failed for user "postgres"
2020-03-17 20:32:45.678 UTC [2758] DETAIL:  Connection matched pg_hba.conf line 83: " host    all             all             127.0.0.1/32            ident"

If connection was refused, I must have an Ident server, but cannot find it.  I've looked for ident, identd, authd, xinetd, and others but no luck.  I do have "ident - identify RCS keyword strings in files"  but I don't think this is applicable.  Also checked whether anything is listening to port 113 and can't find anything.  Anything else it might be called?

Edited March 17, 2020 by NotionCommotion

[requinix]

Again: don't use 127.0.0.1 as the host. Remove the configuration entirely so it uses a socket. You know, like how MySQL connections often work.

[NotionCommotion]

10 hours ago, requinix said:

Again: don't use 127.0.0.1 as the host. Remove the configuration entirely so it uses a socket. You know, like how MySQL connections often work.

Oh, I thought you were talking about one of the conf files and not the login prompt.  I cannot leave the host field blank unless I include a service.  Not sure but I think they are referring to a service as some pre-configured host/port/username thing.

Did my indent server question make any sense?  Evidently, all unix type systems have one running on port 113.  I looked at all my services running on the machine and couldn't find one that seemed like this purpose.

I couldn't find any options for socket connections described by https://www.pgadmin.org/docs/pgadmin4/development/config_py.html.

Have you ever used pgadmin and if so configured to connect via a unix socket and not a host?

Thanks

 

[requinix]

Ah, I was reading pgAdmin 3 documentation. For the hostname, put the path to the socket file.

Not all *nix systems are running an authentication system on port 113. You don't need to use it unless you have something special running there that provides some specific form of authentication - which I'm sure you don't.

[Phi11W]

On 3/15/2020 at 5:33 PM, NotionCommotion said:

I am still a little confused about users and roles, schemas and databases, and clusters.

Roles are hooks that you can hang permissions off. 

Users are just Roles that can log into the database. 

"Cluster" is just PostgreSQL-speke for the PostgreSQL "instance".  Nothing to do with multiple machines or multiple databases.

Databases are .. well .. databases. 

Schemas are logical subdivisions of databases, but not widely used, in my experience. 

 

On 3/15/2020 at 5:33 PM, NotionCommotion said:

I created role michael and then created a database for that role (all role's must have a database of same name, right?).  

Not at all.  You can have many, many Roles, all doing different things, all in the one database or across many databases.  

 

On 3/17/2020 at 12:44 PM, NotionCommotion said:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the # replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

All of the settings above are there for PostgreSQL itself to work.  Let well alone.

Read up on the Host Based Authentication file and how it works.  Getting this wrong can leave your database wide open to attack.

Start adding your own rules to allow access for you and your Application. 

host   all   all   1.2.3.4/32   md5   # Application Host 
host   me   all   2.3.4.5/32   md5   # development machine

 

On 3/17/2020 at 1:40 PM, NotionCommotion said:

am able to connect after changing ident to trust

As requinix quite rightly says, that's removed all authentication from connections matching that Rule. 

Quoting from the Documentation: 

Quote

This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. 

Personally, I'd say never user "trust" in the pg_hba.conf. 

 

Regards, 
   Phill  W.

 

[NotionCommotion]

13 hours ago, requinix said:

Ah, I was reading pgAdmin 3 documentation. For the hostname, put the path to the socket file.

Not all *nix systems are running an authentication system on port 113. You don't need to use it unless you have something special running there that provides some specific form of authentication - which I'm sure you don't.

I should know this but I don't.  How does one find the path to a desired socket file?

I too am sure I don't need an authentication system on port 113.  But I appear to have one and after spending a fair amount of time  trying to figure out what it is, still am not closer.  Should it have some service name?  If so, please let me know what I might try.

[NotionCommotion]

Thanks Phill,  Appreciate your great response.  Reading it once, twice, and three times.  I think all is good.

[requinix]

1 minute ago, NotionCommotion said:

I should know this but I don't.  How does one find the path to a desired socket file?

Not sure. Look in /var or /var/run for something appropriate.

1 minute ago, NotionCommotion said:

I too am sure I don't need an authentication system on port 113.  But I appear to have one and after spending a fair amount of time  trying to figure out what it is, still am not closer.  Should it have some service name?  If so, please let me know what I might try.

Based on the "connection refused" error message from earlier, you don't have one.

I'm not sure what the "service" is. I would guess it's the name of the system service, but I'm not sure why pgAdmin would care about that. Anyway, don't worry about it for now.

[NotionCommotion]

9 hours ago, requinix said:

Not sure. Look in /var or /var/run for something appropriate.

Based on the "connection refused" error message from earlier, you don't have one.

I'm not sure what the "service" is. I would guess it's the name of the system service, but I'm not sure why pgAdmin would care about that. Anyway, don't worry about it for now.

Ah, I interpreted "connection refused" as actively having an ident server.  https://www.postgresql.org/docs/12/auth-ident.html's claim Virtually every Unix-like operating system ships with an ident server that listens on TCP port 113 by default throw me off.  Reading that document more closely makes me feel using ident is not the best choice and it is strange that the default config uses it.  Now it makes sense that I couldn't find it.

The socket is located at /var/run/postgresql.  Thanks.

Per https://www.postgresql.org/docs/12/auth-pg-hba-conf.html, peer authentication will be used instead of ident for local connections.  This appears to require me to either run httpd as postgres or add apache as a postgres user.  Agree?  Either of these reasonable choices, or maybe give this crusade up and utilize host authentication as Phi11W suggested?  Any suggestions how to run a single virtual host as a different user?

2020-03-19 12:05:35.761 UTC [47784] LOG:  provided user name (postgres) and authenticated user name (apache) do not match
2020-03-19 12:05:35.761 UTC [47784] FATAL:  Peer authentication failed for user "postgres"
2020-03-19 12:05:35.761 UTC [47784] DETAIL:  Connection matched pg_hba.conf line 80: "local   all             all                                     peer"

 

[requinix]

24 minutes ago, NotionCommotion said:

This appears to require me to either run httpd as postgres or add apache as a postgres user.  Agree?

Almost.

Are you using php-fpm or mod_php?

[NotionCommotion]

1 hour ago, requinix said:

Almost.

Are you using php-fpm or mod_php?

php-fpm

[requinix]

Then it doesn't matter what Apache is running as. Because Apache is not connecting to the database. php-fpm is.

Set up a php-fpm pool specifically for you to use pgAdmin with - a pool separate from your normal one. It can run as the postgres user so that it has full access to the database. Then modify your Apache/virtualhost/whatever configuration so that it runs pgAdmin URLs through this dedicated pool. Ideally you'd have this as a completely separate virtualhost...

Your regular pool will be with the michael user.