Connecting to postgresql from windows. Postgresql Remote connection

A vulnerability (CVE-2019-18634) has been identified in the sudo utility, which is used to organize the execution of commands on behalf of other users, which allows you to increase your privileges in the system. Problem […]

The release of WordPress 5.3 improves and expands the block editor introduced in WordPress 5.0 with a new block, more intuitive interaction, and improved accessibility. New features in the editor […]

After nine months of development, the FFmpeg 4.2 multimedia package is available, which includes a set of applications and a collection of libraries for operations on various multimedia formats (recording, converting and […]

New features in Linux Mint 19.2 Cinnamon

Linux Mint 19.2 is a long-term support release that will be supported until 2023. It comes with updated software and contains improvements and many new […]

Linux Mint 19.2 distribution released

Presented is the release of the Linux Mint 19.2 distribution, the second update to the Linux Mint 19.x branch, formed on the Ubuntu 18.04 LTS package base and supported until 2023. The distribution is fully compatible [...]

New BIND service releases are available that contain bug fixes and feature improvements. New releases can be downloaded from the downloads page on the developer's website: […]

Exim is a message transfer agent (MTA) developed at the University of Cambridge for use on Unix systems connected to the Internet. It is freely available in accordance with [...]

After almost two years of development, the release of ZFS on Linux 0.8.0 is presented, an implementation of the ZFS file system, designed as a module for the Linux kernel. The module has been tested with Linux kernels from 2.6.32 to […]

The IETF (Internet Engineering Task Force), which develops Internet protocols and architecture, has completed an RFC for the ACME (Automatic Certificate Management Environment) protocol […]

The non-profit certification authority Let’s Encrypt, which is controlled by the community and provides certificates free of charge to everyone, summed up the results of the past year and talked about plans for 2019. […]

0

I'm stumped. We have a postgres 9.2 database that we have been using for some time. Now we need to be able to connect to it from outside our network.

So we configure the external IP address and test that we can connect to port 5432 from the remote host via telnet. So this proves that the network connection is working, firewalls, etc. are all good.

When I try to connect with:
PSQL -h db.host.com -d dbname -p 5432 -U user

I'll be back Psql: The server closed the connection unexpectedly.

I checked that
listen_addresses = "*" is set in postgresql.conf

And in pg_hba.conf we have a line that reads (for testing purposes only)
host all all 0.0.0.0/0 md5

And I reloaded the database to test its selected changes.
So this should allow connections from any source.

The psql version on the database server is 9.2.14, and on the client it is 9.2.13.

Any suggestions or ideas?

• 1 answer

Sorting:

Activity

1

I'd like to try a few things to get more information:

Postgres tail log into the server to see what's going on there when connected.

Run psql --version to make sure it more or less matches the server version. (I see you've already done this, but I'll leave it here for posterity.)

Run strace psql.... to see how far it goes to failure.

This still sounds like a network issue to me. What is the telnet command you are working with? "External IP" sounds the same as you would on AWS. Setting up remote access to a VPC takes many steps. Do you have any other services open to the outside that are running?

You can also troubleshoot by shutting down the Postgres server and using nc to listen on 5432 . Then telnet and see if you can send data back and forth.

1

Paul, thanks for the suggestions. I made a stretch and checked the versions. And you were right, I tried to connect from AWS. Turns out it was the tier I was using in the load balancer to only allow access to IP-2 IP ranges that AWS publishes. When I disabled irule I was able to connect immediately. So this must be surreal logic since I checked the public IP of my EC2 instance and it was indeed listed in the irule via CIDR notification. -

System Administration

This post is a brief instruction for beginners, for those who installed PostgreSQL for the first time. Here's all the information you need to get started with PostgreSQL.

Connecting to the DBMS

The first thing to do is to gain access to PostgreSQL, access as a superuser.
Authentication settings are located in the pg_hba.conf file.

1. local all postgres peers

This line indicates that the postgres user can connect to any local PostgreSQL database via a socket. There is no need to enter a password; the operating system will transmit the username, and it will be used for authentication.
Let's connect:

1. $ sudo -u postgres psql postgres postgres

To be able to connect via the network, you need to add the line to pg_hdba.conf:

1. # TYPE DATABASE USER ADDRESS METHOD
2. hostssl all all 0.0.0.0/0 md5

Authentication Method md5 means that you will have to enter a password to connect. This is not very convenient if you frequently use the psql console. If you want to automate some actions, the bad news is that psql does not accept a password as an argument. There are two ways to solve these problems: setting the appropriate environment variable and storing the password in a special .pgpass file.

Setting the PGPASSWORD environment variable

I’ll say right away that it’s better not to use this method, because some operating systems allow ordinary users to view environment variables using ps. But if you want, you need to write in the terminal:

1. export PGPASSWORD=mypasswd

The variable will be available in the current session. If you need to set a variable for all sessions, then you need to add the line from the example to the .bashrc or .bash_profile file

Storing the password in the .pgpass file

If we are talking about Linux, then the file should be located in $HOME (/home/username). Only the owner (0600) must have write and read rights. You need to write lines like this to the file:

1. hostname:port:database:username:password

You can write “*” in the first four fields, which will mean no filtering (full selection).

Getting help information

\? - will display all available commands along with their brief description,
\h - will display a list of all available queries,
\h CREATE - will provide help for a specific request.

DBMS user management

How to get a list of PostgreSQL users? Or you can query the pg_user table.

1. SELECT * FROM pg_user ;

Creating a new PostgreSQL user

From the psql shell, this can be done using the CREATE command.

1. CREATE USER username WITH password "password" ;

Or you can use the terminal.

1. createuser -S -D -R -P username

You will be prompted to enter a password.

Changing the user password

1. ALTER USER username WITH PASSWORD "password" ;

Changing user roles

To give the user permission to create databases, run the following query:

1. ALTER ROLE username WITH CREATEDB ;

Database management

Displaying a list of databases in the psql terminal: The same from the Linux terminal:

1. psql -l

Creating a database from psql (PostgreSQL Terminal)

1. CREATE DATABASE dbname OWNER dbadmin ;

Creating a new database using the terminal:

1. createdb -O username dbname;

Setting up database access rights

If the user is the owner of the database, then he has all rights. But if you want to give access to another user, you can do this using the GRANT command. The query below will allow the user to connect to the database. But don’t forget about the pg_hba.conf configuration file, it must also have the appropriate connection permissions.

1. GRANT CONNECT ON DATABASE dbname TO dbadmin ;

PostgreSQL is an open-source, cross-platform object-relational DBMS. In this article you will learn how to install PostgreSQL on Ubuntu Linux, connect to it and run a couple of simple SQL queries, as well as how to set up backups.

To install PostgreSQL 9.2 on Ubuntu 12.10, run the following commands:

sudo apt-add-repository ppa:pitti/ postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2

Let's try to work with the DBMS through the shell:

sudo -u postgres psql

Let's create a test database and a test user:

CREATE DATABASE test_database;
CREATE USER test_user WITH password "qwerty" ;
GRANT ALL ON DATABASE test_database TO test_user;

To exit the shell, enter the command \q .

Now let's try to work with the created database on behalf of test_user:

psql -h localhost test_database test_user

Let's create a new table:

CREATE SEQUENCE user_ids;
CREATE TABLE users (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL ("user_ids" ) ,
login CHAR(64) ,
password CHAR(64));

Please note that, unlike some other DBMSs, PostgreSQL does not have columns with the auto_increment property. Instead, Postgres uses sequences. For now, it is enough to know that using the nextval function we can get unique numbers for a given sequence:

SELECT NEXTVAL ("user_ids" ) ;

By setting the default value for the id field of the users table to NEXTVAL("user_ids"), we have achieved the same effect that auto_increment gives. When adding new records to the table, we may not specify an id, because a unique id will be generated automatically. Multiple tables can use the same sequence. This way we can guarantee that the values ​​of some fields in these tables do not overlap. In this sense, sequences are more flexible than auto_increment.

The exact same table can be created using just one command:

CREATE TABLE users2 (
id SERIAL PRIMARY KEY ,
login CHAR(64) ,
password CHAR(64));

In this case, the sequence for the id field is created automatically.

Now using the \d command you can see a list of all available tables, and using \d users you can see a description of the users table. If you don't get the information you're looking for, try \d+ instead of \d . You can get a list of databases with the \l command, and switch to a specific database with the \c dbname command. To display command help, say \? .

It's important to note that PostgreSQL converts table and column names to lowercase by default. If you don't want this behavior, you can use double quotes:

CREATE TABLE "anotherTable" ("someValue" VARCHAR (64 ) ) ;

Another feature of PostgreSQL that may cause difficulties when starting to work with this DBMS is the so-called “schemas”. A schema is something like a namespace for tables, like a directory with tables inside a database.

Creating a schema:

CREATE SCHEMA bookings;

Switch to scheme:

SET search_path TO bookings;

You can view a list of existing schemes using the \dn command. The default schema is named public. In principle, you can successfully use PostgreSQL without knowing about the existence of schemas. But when working with legacy code, and in some edge cases, knowing about schemas can be very useful.

Otherwise, working with PostgreSQL is not much different from working with any other relational DBMS:

INSERT INTO users (login, password)
VALUES ("afiskon" , "123456" ) ;
SELECT * FROM users;

If you now try to connect to Postgres from another machine, you will fail:

psql -h 192.168.0.1 test_database test_user

Psql: could not connect to server: Connection refused
Is the server running on host "192.168.0.1" and accepting
TCP/IP connections on port 5432?

To fix this, add the line:

listen_addresses = "localhost,192.168.0.1"

...to the /etc/postgresql/9.2/main/postgresql.conf file as well.