Results 1 to 6 of 6

Thread: postgresql setup

  1. #1

    Default postgresql setup

    I have postgresql 10 and pgadmin 3 installed. Created a user (other than postgres user) and permission is set to Superuser.

    Code:
    systemctl status postgresql
     postgresql.service - PostgreSQL database server
       Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disab>
       Active: active (running) since Sun 2018-11-25 09:23:45 EST; 8h ago
      Process: 1393 ExecStart=/usr/share/postgresql/postgresql-script start (code=exited, status>
     Main PID: 1475 (postgres)
        Tasks: 8 (limit: 4915)
       Memory: 39.8M
       CGroup: /system.slice/postgresql.service
               ├─1475 /usr/lib/postgresql10/bin/postgres -D /var/lib/pgsql/data
               ├─1526 postgres: logger process    
               ├─1546 postgres: checkpointer process    
               ├─1547 postgres: writer process    
               ├─1548 postgres: wal writer process    
               ├─1549 postgres: autovacuum launcher process    
               ├─1550 postgres: stats collector process    
               └─1551 postgres: bgworker: logical replication launcher    
    
    Nov 25 09:23:44 linux-s6kv systemd[1]: Starting PostgreSQL database server...
    Nov 25 09:23:44 linux-s6kv postgresql-script[1393]: 2018-11-25 09:23:44.778 EST   [1475]LOG:>
    Nov 25 09:23:44 linux-s6kv postgresql-script[1393]: 2018-11-25 09:23:44.778 EST   [1475]LOG:>
    Nov 25 09:23:44 linux-s6kv postgresql-script[1393]: 2018-11-25 09:23:44.785 EST   [1475]LOG:>
    Nov 25 09:23:44 linux-s6kv postgresql-script[1393]: 2018-11-25 09:23:44.793 EST   [1475]LOG:>
    Nov 25 09:23:44 linux-s6kv postgresql-script[1393]: 2018-11-25 09:23:44.812 EST   [1475]LOG:>
    Nov 25 09:23:44 linux-s6kv postgresql-script[1393]: 2018-11-25 09:23:44.812 EST   [1475]HINT>
    Nov 25 09:23:45 linux-s6kv systemd[1]: Started PostgreSQL database server.
    
    
    Now, in pgadmin3, I'm trying to connect to a db:

    Name: localhost
    Address: 168.0.0.1
    Port: 5432
    Maintenance DB: postgres
    Username: whatever I created in command line
    Password: whatever

    I get this error:


    Server doesn't listen
    The server doesn't accept connections: the connection library reports
    could not connect to server: Connection refused Is the server running on host "168.0.0.1" and accepting TCP/IP connections on port 5432?

  2. #2
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,394
    Blog Entries
    2

    Default Re: postgresql setup

    Not just in this situation,
    Whenever you get a "connection refused" error,
    That means that you were active rejected by something, it's not just a "did not answer."

    The first thing you should check in all such situations is your firewall (determine whether you connection is using your localhost or network address, then make sure the port is open).

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  3. #3

    Default Re: postgresql setup

    Quote Originally Posted by tsu2 View Post
    Not just in this situation,
    Whenever you get a "connection refused" error,
    That means that you were active rejected by something, it's not just a "did not answer."

    The first thing you should check in all such situations is your firewall (determine whether you connection is using your localhost or network address, then make sure the port is open).

    TSU
    how do I do that, I don't have access to routers or their settings?

  4. #4
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,394
    Blog Entries
    2

    Default Re: postgresql setup

    Quote Originally Posted by ultra99 View Post
    how do I do that, I don't have access to routers or their settings?
    Unless your connection pass through one a network device, you shouldn't have to worry about those.

    Am talking about the local "personal" firewall on your openSUSE where your PostgreSQL is installed.
    I haven't looked at pgadmin specifically, but it's very common even when both the management and the database server applications are on the same machine that the two apps talk to each other through the network interface as though each were on a different machine.

    So, on your openSUSE,
    YaST > Security > Firewall

    The first time you open that module, it'll probably prompt you to install firewalld with its graphical management app, firewall-config.
    If you haven't ever configured a personal firewall, firewall-config might appear a bit daunting at first, but all firewalls have some basic concepts...

    Typically, a network interface is assigned to a firewall zone which gives you a head start on default settings. If your machine has only one network adapter, your network interface will be assigned to the external zone (the most restrictive where almost everything is denied by default).
    You will need to open the specified port your error described, you can first look to see if there is a pre-defined pgadmin service, if it exists you can simply enable it. But, if it's not available then you only need to manually open the port.

    Some references
    The openSUSE LEAP reference, the firewall section should be OK to use with Tumbleweed without any alterations
    https://doc.opensuse.org/documentati....firewall.html
    The official firewalld documentation
    https://firewalld.org/documentation/

    If things are still unclear and you can't set up your firewall, post here again.

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  5. #5

    Default Re: postgresql setup

    Quote Originally Posted by tsu2 View Post
    Unless your connection pass through one a network device, you shouldn't have to worry about those.

    Am talking about the local "personal" firewall on your openSUSE where your PostgreSQL is installed.
    I haven't looked at pgadmin specifically, but it's very common even when both the management and the database server applications are on the same machine that the two apps talk to each other through the network interface as though each were on a different machine.

    So, on your openSUSE,
    YaST > Security > Firewall

    The first time you open that module, it'll probably prompt you to install firewalld with its graphical management app, firewall-config.
    If you haven't ever configured a personal firewall, firewall-config might appear a bit daunting at first, but all firewalls have some basic concepts...

    Typically, a network interface is assigned to a firewall zone which gives you a head start on default settings. If your machine has only one network adapter, your network interface will be assigned to the external zone (the most restrictive where almost everything is denied by default).
    You will need to open the specified port your error described, you can first look to see if there is a pre-defined pgadmin service, if it exists you can simply enable it. But, if it's not available then you only need to manually open the port.

    Some references
    The openSUSE LEAP reference, the firewall section should be OK to use with Tumbleweed without any alterations
    https://doc.opensuse.org/documentati....firewall.html
    The official firewalld documentation
    https://firewalld.org/documentation/

    If things are still unclear and you can't set up your firewall, post here again.

    TSU
    I haven't the port thing yet as I'm getting this error, though I'm sure of my password.

    Code:
    Ident authentication failed The server doesn't accept the current user: The server reports FATAL: Ident authentication failed for user "ultra99" If this message appears, the pg_hba.conf entry found for your client / user / database combination is set to "ident" authentication. Some distributions, e.g. Debian, have this by default. To perform ident based authentication successfully, you need additional setup; see the PostgreSQL help for this. For a beginner, it might be more appropriate to use a different authentication method; MD5 encrypted passwords are a good choice, which can be configured by an entry in pg_hba.conf like this: host all all 192.168.0.0/24 md5 This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24. You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

  6. #6
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,394
    Blog Entries
    2

    Default Re: postgresql setup

    Quote Originally Posted by ultra99 View Post
    I haven't the port thing yet as I'm getting this error, though I'm sure of my password.

    Code:
    Ident authentication failed The server doesn't accept the current user: The server reports FATAL: Ident authentication failed for user "ultra99" If this message appears, the pg_hba.conf entry found for your client / user / database combination is set to "ident" authentication. Some distributions, e.g. Debian, have this by default. To perform ident based authentication successfully, you need additional setup; see the PostgreSQL help for this. For a beginner, it might be more appropriate to use a different authentication method; MD5 encrypted passwords are a good choice, which can be configured by an entry in pg_hba.conf like this: host all all 192.168.0.0/24 md5 This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24. You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.
    The error you posted seems to be pretty descriptive including the recommended solution.
    Maybe you didn't understand the error, or did you try the recommended solution and still have an issue?

    In plain words, it looks like the recommendation is to configure the postgresql configuration to accept any connection using a LAN IP address.

    If you don't understand what is happening, you'll need to post your IP address info with the following command
    Code:
    ip addr
    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •