Ikram Mihan

Ikram Mihan

1575388723

How to Install PostgreSQL database server on Debian 10

PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. It has many powerful features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication, and more. This tutorial walks you through the steps of installing the PostgreSQL database server on Debian 10. We’ll also explore the fundamentals of basic database administration.

Installing PostgreSQL

At the time of writing this article, the latest version of PostgreSQL available from the default Debian repositories is PostgreSQL version 11.5.

To install PostgreSQL on your Debian server perform the following steps as root or user with sudo privileges:

  1. Start by updating the APT package index:

    sudo apt update
    
  2. Install the PostgreSQL server and contrib package which provides additional features for the PostgreSQL database:

    sudo apt install postgresql postgresql-contrib
    
  3. Once the installation is complete, the PostgreSQL service will start. To verify the installation, use the psql tool to print the server version:

    sudo -u postgres psql -c "SELECT version();"
    

    The output should look something like the following:

    PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
    

PostgreSQL Roles and Authentication Methods

PostgreSQL handles database access permissions using the concept of roles. Depending on how you set up the role, it can represent a database user or a group of database users.

PostgreSQL supports several authentication methods. The most frequently used methods are:

  • Trust - A role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
  • Password - A role can connect by providing a password. The passwords can be stored as scram-sha-256``md5andpassword (clear-text)
  • Ident - Only supported for TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
  • Peer - Same as Ident, but it is only supported on local connections.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. For local connections, PostgreSQL is set to use the peer authentication method.

The “postgres” user is automatically created when PostgreSQL is installed. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as “postgres”, switch to the user postgres and access a PostgreSQL prompt using the psql utility:

sudo su - postgres
psql

From here you can interact with the PostgreSQL server. To exit out of the PostgreSQL shell type:

\q

You can use the sudo command to access the PostgreSQL prompt without switching users:

sudo -u postgres psql

The postgres user is typically used only from the localhost.

Creating PostgreSQL Role and Database

The createuser command allows you to create new roles from the command line. Only superusers and roles with CREATEROLE privilege can create new roles.

In the following example, we’ll create a new role named kylo, a database named kylodb and grant privileges on the database to the role.

  1. First, create the role by issuing the following command:

    sudo su - postgres -c "createuser kylo"
    
  2. Next, create the database using the createdb command:

    sudo su - postgres -c "createdb kylodb"
    
  3. To grant permissions to the user on the database, connect to the PostgreSQL shell:

    sudo -u postgres psql
    

    Run the following query:

    grant all privileges on database kylodb to kylo;
    

Enable Remote Access to the PostgreSQL Server

By default, the PostgreSQL, server listens only on the local interface 127.0.0.1.

If you want to connect to the PostgreSQL server from remote locations, you need to set the server to listen on the public interface and edit the configuration to accept remote connections.

Open the configuration file postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section. This instruct the server to listen on all network interfaces.

sudo nano /etc/postgresql/11/main/postgresql.conf

/etc/postgresql/11/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

Save the file and restart the PostgreSQL service for changes to take effect:

sudo service postgresql restart

Verify the changes with the ss utility:

ss -nlt | grep 5432

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

The output should show that the PostgreSQL server listens on all interfaces (0.0.0.0).

The last step is to configure the server to accept remote logins by editing the pg_hba.conf file.

Below are some examples showing different use cases:

/etc/postgresql/11/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane will be able to access all databases from all locations using an md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane will be able to access only the janedb from all locations using an md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

Conclusion

We’ve shown you how to install and configure PostgreSQL on Debian 10. For more information on this topic, consult the PostgreSQL Documentation.

Thank you for reading !

#postgresql #debian #database

What is GEEK

Buddha Community

How to Install PostgreSQL database server on Debian 10
Ruth  Nabimanya

Ruth Nabimanya

1620633584

System Databases in SQL Server

Introduction

In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.

System Database

Fig. 1 System Databases

There are five system databases, these databases are created while installing SQL Server.

  • Master
  • Model
  • MSDB
  • Tempdb
  • Resource
Master
  • This database contains all the System level Information in SQL Server. The Information in form of Meta data.
  • Because of this master database, we are able to access the SQL Server (On premise SQL Server)
Model
  • This database is used as a template for new databases.
  • Whenever a new database is created, initially a copy of model database is what created as new database.
MSDB
  • This database is where a service called SQL Server Agent stores its data.
  • SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.
TempDB
  • The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
  • User can create their own version of temporary tables and those are stored in Tempdb.
  • But this database is destroyed and recreated every time when we restart the instance of SQL Server.
Resource
  • The resource database is a hidden, read only database that holds the definitions of all system objects.
  • When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.

#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database

Ikram Mihan

Ikram Mihan

1575388723

How to Install PostgreSQL database server on Debian 10

PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. It has many powerful features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication, and more. This tutorial walks you through the steps of installing the PostgreSQL database server on Debian 10. We’ll also explore the fundamentals of basic database administration.

Installing PostgreSQL

At the time of writing this article, the latest version of PostgreSQL available from the default Debian repositories is PostgreSQL version 11.5.

To install PostgreSQL on your Debian server perform the following steps as root or user with sudo privileges:

  1. Start by updating the APT package index:

    sudo apt update
    
  2. Install the PostgreSQL server and contrib package which provides additional features for the PostgreSQL database:

    sudo apt install postgresql postgresql-contrib
    
  3. Once the installation is complete, the PostgreSQL service will start. To verify the installation, use the psql tool to print the server version:

    sudo -u postgres psql -c "SELECT version();"
    

    The output should look something like the following:

    PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
    

PostgreSQL Roles and Authentication Methods

PostgreSQL handles database access permissions using the concept of roles. Depending on how you set up the role, it can represent a database user or a group of database users.

PostgreSQL supports several authentication methods. The most frequently used methods are:

  • Trust - A role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
  • Password - A role can connect by providing a password. The passwords can be stored as scram-sha-256``md5andpassword (clear-text)
  • Ident - Only supported for TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
  • Peer - Same as Ident, but it is only supported on local connections.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. For local connections, PostgreSQL is set to use the peer authentication method.

The “postgres” user is automatically created when PostgreSQL is installed. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as “postgres”, switch to the user postgres and access a PostgreSQL prompt using the psql utility:

sudo su - postgres
psql

From here you can interact with the PostgreSQL server. To exit out of the PostgreSQL shell type:

\q

You can use the sudo command to access the PostgreSQL prompt without switching users:

sudo -u postgres psql

The postgres user is typically used only from the localhost.

Creating PostgreSQL Role and Database

The createuser command allows you to create new roles from the command line. Only superusers and roles with CREATEROLE privilege can create new roles.

In the following example, we’ll create a new role named kylo, a database named kylodb and grant privileges on the database to the role.

  1. First, create the role by issuing the following command:

    sudo su - postgres -c "createuser kylo"
    
  2. Next, create the database using the createdb command:

    sudo su - postgres -c "createdb kylodb"
    
  3. To grant permissions to the user on the database, connect to the PostgreSQL shell:

    sudo -u postgres psql
    

    Run the following query:

    grant all privileges on database kylodb to kylo;
    

Enable Remote Access to the PostgreSQL Server

By default, the PostgreSQL, server listens only on the local interface 127.0.0.1.

If you want to connect to the PostgreSQL server from remote locations, you need to set the server to listen on the public interface and edit the configuration to accept remote connections.

Open the configuration file postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section. This instruct the server to listen on all network interfaces.

sudo nano /etc/postgresql/11/main/postgresql.conf

/etc/postgresql/11/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

Save the file and restart the PostgreSQL service for changes to take effect:

sudo service postgresql restart

Verify the changes with the ss utility:

ss -nlt | grep 5432

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

The output should show that the PostgreSQL server listens on all interfaces (0.0.0.0).

The last step is to configure the server to accept remote logins by editing the pg_hba.conf file.

Below are some examples showing different use cases:

/etc/postgresql/11/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane will be able to access all databases from all locations using an md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane will be able to access only the janedb from all locations using an md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

Conclusion

We’ve shown you how to install and configure PostgreSQL on Debian 10. For more information on this topic, consult the PostgreSQL Documentation.

Thank you for reading !

#postgresql #debian #database

Hal  Sauer

Hal Sauer

1591451760

How to Install Plex Media Server on Debian 10 Buster Server or Desktop

This tutorial will be showing you how to install Plex media server on Debian 10 Buster server/desktop. Plex is a free piece of software that allows you to organize your movies, TV shows, music and photos in one beautiful interface and stream those media files on your PC, tablet, phone, TV, Roku, etc on the network or over the Internet. Plex can run on Linux, FreeBSD, MacOS, Windows and various NAS systems.

#debian #debian desktop #debian server #linux #plex media server

Hal  Sauer

Hal Sauer

1591479120

How to Install LAMP Stack on Debian 10 Buster Server/Desktop

This tutorial is going to show you how to install Apache, MariaDB and PHP7.3 (LAMP stack) on Debian 10 Buster. A software stack is a set of software tools bundled together. LAMP stands for Linux, Apache, MariaDB/MySQL and PHP, all of which are open source and free to use. It is the most common software stack that powers dynamic websites and web applications. Linux is the operating system; Apache is the web server; MariaDB/MySQL is the database server and PHP is the server-side scripting language responsible for generating dynamic web pages.

All of the four components are free and open-source. However, since MySQL is now owned by Oracle and there’s a chance that Oracle turns it to a closed-source product, we will choose MariaDB instead of MySQL.

#debian #debian 10 #debian server #lamp stack #linux

How to Easily Set Up a Mail Server on Debian 10 Buster with iRedMail

Setting up your own email server on Linux from scratch is a pain in the butt, if you are not a skilled server admin. This tutorial will be showing you how to use iRedMail to quickly set up a full-featured mail server on Debian 10, saving you lots of time and headaches.

What is iRedMail?
iRedMail is a shell script that automatically installs and configures all necessary mail server components on your Linux/BSD server, thus eliminating manual installation and configuration. With iRedMail, you can easily create unlimited mailboxes and unlimited mail domains in a web-based admin panel. Mailboxes can be stored in MariaDB/MySQL, PostreSQL database or OpenLDAP. The following is a list of open-source software that will be automatically installed and configured by iRedMail.

#debian #mail server #debian server #email server #iredmail #linux