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 of 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 in 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, implementation file system ZFS, packaged 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. […]

System Administration

This post is brief instructions 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 OS allow you to view ordinary users 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 configuration file pg_hba.conf, it must also have the appropriate connection permissions.

1. GRANT CONNECT ON DATABASE dbname TO dbadmin ;

Attention : If you are working on Windows OS and have not yet installed PostgreSQL and Debit Plus V12, then you can download from the download page the Debit Plus V12 assembly with a pre-installed PostgreSQL DBMS and a connected database (with a basic configuration for Ukraine). Otherwise, you need to first install the PostgreSQL DBMS and software package“Debit Plus V12”, after which you can set up the connection and import the PostgreSQL database, following these instructions.

To connect a PostgreSQL database, you must perform the following steps:

Launch "Debit Plus V12" and add a new base ( context menu"Add new")

Leave a checkmark in the "Add existing to list" box.

"DBMS" - POSTGRE.

"Database server" - localhost.

“Database name” - specify the name of the database from the PostgreSQL DBMS in lowercase Latin letters (small letters).

Note: Usually one base is used. For special purposes, the database can be divided into several, then you need to check the box next to “Use multiple databases”, click the “Next” button and indicate the correspondence of the “tags” to the physical databases. This may be necessary, for example, if the databases of several enterprises will refer to the same directories (counterparties, items, etc.). This case is not discussed further.

“Settings directory” - specify the path to the database settings for PostgreSQL (drive\DebetPlusV12\base\pgdpbase).

Save changes made by clicking the "Done" button.

Run pgAdmin (PostgreSQL DBMS administrator), add a server (File/Add Server...) and add a new database ("New Database..." menu).

Enter the server name, localhost in the Host field, fill out the remaining fields as desired.

Enter the name of the database in the “Name” field (the same as entered in the “Server” field in the settings for connecting to the “Debit Plus V12” database).

To import a database from an archive, use the “Restore...” menu

Specify the path to the database archive dpbase.bakup (drive\DebetPlusV12\base\pgdpbase).

You can add the user dpadmin, which is the administrator in Debit Plus V12 by default (without a password).

Assign all rights to this user.

Note : You can not add the dpadmin user, then when activating the database you will need to enter the postgres administrator name.

Refresh the list of databases (menu “Refresh”).

Note : If you do not want to assign “Superuser” rights to the user, then assign rights to “Tables” and “Views”. To do this, select the corresponding object and select the “Grant Wizard” context menu item.

On the “Selection” tab, click the “Check all” button, and on the “Privileges” tab, check “ALL” and click the “Add/Change” button. Save your changes.

Activate the database connection setting in the Debit Plus V12 software (context menu “Make active”).

The system will ask for a password to connect to the database. Click Yes.

Attention! If such a message does not appear, then start updating the database structures by selecting the menu item “Tools” / “Updating database structures”.

As a result, the following window will appear:

Leave all checkboxes as default (there must be checkmarks in the “Reindex” column for all database tables).

After completing the modification, you can start working.

Launch “Debit Plus V12” and add a new base (context menu “Add new”).

Leave a checkmark in the “Add existing to list” field:

In the first field, enter an arbitrary name for the database (the name may be different on each working computer).

"DBMS" - POSTGRE.

“Database server” - the name or IP address of the server.

“Database server port” - specify the database server port, default is 5432.

“Database name” - enter the name of the database in lowercase Latin letters.

"Database schema name" - dpbase.

Check only the “Use Debit Plus authorization” box.

“Settings directory” - specify the path to the database settings for PostgreSQL (network path\DebetPlusV12\base\pgdpbase).

Save your changes by clicking the “Finish” button, and then make the database active.

Set launch options (main menu “Settings” / “Launch Options”)

In the window that appears, in the “Password for changing settings” field, enter the password 150301 and click on the “Accept” button.

Specify the path to the JDebet folder on the server and click save. Make the database active and you can start working.

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 network connection works, firewalls, etc. 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's happened telnet command who are you working with? "External IP" sounds the same as you would on AWS. Settings remote access to 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. -