PostgreSQL, also known as postgres, is a popular database management system that is used to handle the data of many websites and applications.
In this guide, we will discuss some ways that you can secure your PostgreSQL databases. This will help prevent unauthorized or malicious use of your data.
We will be completing the steps in this tutorial on an Ubuntu 12.04 VPS, but almost every modern distribution should function in a similar fashion.
If you do not already currently have PostgreSQL installed, you can install it with the following commands:
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
The database software should now be installed on your system.
By default, PostgreSQL handles authentication by associating Linux user accounts with PostgreSQL accounts. This is called "peer" authentication.
Upon installation, Postgres creates a Linux user called "postgres" which can be used to access the system. We can change to this user by typing:
sudo su - postgres
From here, we can connect to the system by typing:
psql
Notice how we can connect without a password. This is because Postgres has authenticated by username, which it assumes is secured.
Do not use the Linux "postgres" user for anything other than accessing the database software. This is an important security consideration.
Exit out of PostgreSQL and the postgres user by typing the following:
\q exit
One simple way to remove a potential attack vector is to not allow remote connections to the database. This is the current default when installing PostgreSQL from the Ubuntu repositories.
We can double check that no remote connections are allowed by looking in the host based authentication file:
sudo nano /etc/postgresql/9.1/main/pg_hba.conf
local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5
I have removed the comments from the output above.
As you can see, the first two security lines specify "local" as the scope that they apply to. This means they are using Unix/Linux domain sockets.
The second two declarations are remote, but if we look at the hosts that they apply to (127.0.0.1/32 and ::1/128), we see that these are interfaces that specify the local machine.
To access PostgreSQL from a remote location, consider using SSH to connect to the database machine and then using a local connection to the database from there.
It is also possible to tunnel access to PostgreSQL through SSH so that the client machine can connect to the remote database as if it were local. You can learn how to tunnel PostgreSQL through SSH here.
Another option is to configure access using SSL certificates. This will allow encrypted transfer of information. You can learn to set up SSL with PostgreSQL with this link.
While securing access to the prompt is important, it is also essential that you secure your data within the PostgreSQL environment. PostgreSQL accomplishes this through the use of "roles".
Log into PostgreSQL to follow along with this section:
sudo su - postgres psql
One way to ensure that your users and data can be separated if necessary is to assign a distinct role for each application.
To create a new role, type the following:
CREATE ROLE role_name WITH optional_permissions;
To see the permissions you can assign, type:
\h CREATE ROLE
You can alter the permissions of any role by typing:
ALTER ROLE role_name WITH optional_permissions;
List the current roles and their attributes by typing:
\du
List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- hello | Create DB | {} postgres | Superuser, Create role, Create DB, Replication | {} testuser | | {}
Create a new user and assign appropriate permissions for every new application that will be utilizing PostgreSQL.
Roles are a flexible way of handling permissions. They share some aspects of users and groups, and can be made to work like either. Roles can have membership in other roles.
This gives us some unique ways of addressing permissions.
We can assign users login roles (such as the applications roles we spoke about above), and then we can assign those roles membership in access roles to perform actual functions on data.
This separation of privileges allows us to manage what each user can do on a more fine-grained level.
To test this, let's create two roles:
CREATE ROLE login_role WITH login; CREATE ROLE access_role; \du
List of roles Role name | Attributes | Member of -------------+------------------------------------------------+----------- access_role | Cannot login | {} login_role | | {} postgres | Superuser, Create role, Create DB, Replication | {}
As you can see, we have two new roles, one of which cannot login.
We can now create a database owned by "access_role":
CREATE DATABASE demo_application WITH OWNER access_role;
We can now connect to the database and lock down the permissions to only let "access_role" create tables:
\c demo_application REVOKE ALL ON SCHEMA public FROM public; GRANT ALL ON SCHEMA public TO access_role;
We can test this by changing users to "login_role" and trying to create a table:
SET ROLE login_role; CREATE TABLE test_table( name varchar(25));
ERROR: permission denied for schema public
Finally, we can add "login_role" as a member to "access_role". This will allow it access to the same functionality that "access_role" has.
We will reset the role to "postgres", grant "login_role" membership within "access_role", and then re-try the process:
RESET ROLE; GRANT access_role TO login_role; SET ROLE login_role; CREATE TABLE test_table( name varchar(25));
CREATE TABLE
This works.
We can now log in using "login_role" and administer the database. This makes it easy to add or revoke the ability to work on this database.
The methods discussed in this article are only a jumping off point for developing your own security strategies. Your security needs will be unique depending on the different database users and the amount and type of traffic you need to cater to.
It is recommended that you research the benefits and shortcomings of any security measures prior to implementing them on production systems. It is essential to conduct thorough testing to ensure that you have implemented the control you are looking for, and that you have not accidentally restricted legitimate use of your software.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Database systems are rich with attack vectors to exploit. This presentation explores the many potential PostgreSQL external vulnerabilities and shows how they can be secured. Includes concepts from Magnus Hagander Data loss or theft of data is a very common incident that people notices now. Sometimes people fail to bid or become unsuccessful to draw a tender organized in online ways. For More information :- https://www.datasunrise.com/datasunrise-for-postgresql/