PgConf.Russia 2020

Role-based access control (RBAC) is one of the main mechanisms used for access control in many DBMS, including PostgreSQL. This model is a sub-type of traditional discretionary access control with its restrictions. In addition to DAC, many operating systems also use mandatory access control (MAC) based on security labels. This additional security mechanism is obligatory for protecting information that demands higher levels of security. Naturally, we would like to use MAC within DBMS when working in OS with mandatory access control switched on.
In this talk, we'll give an overview of existing MAC implementations in DBMS, as well as share our approach to using security mechanisms provided by SELinux, the sepgsql extension for PostgreSQL, and the standard mechanism of row-level security (RLS), which has been available in PostgreSQL starting from version 9.5.
In our presentation, we will use the "Airlines" demo database provided by the Postgres Professional company to show how to protect sensitive information and personal data, compare different ways of storing security labels, and assess performance of our solution.

Today no one is surprised by cloud infrastructure anymore, but not all its components are easy to deploy in cloud. For example, the database is always very demanding in terms of performance and resources. Scaling and fault tolerance are the most acute problems, that's why we have been observing rapid development of alternative DBMS in the recent years. However, traditional relational DBMS have already accumulated a lot of various features, so they often remain the first choice. Besides, they are constantly evolving and offer a wide variety of scaling tools. I will mainly speak about PostgreSQL, when you should consider scaling, and how to do it right.

We will touch upon the following topics:
- Streaming replication and balancing read/write workloads
- Logical replication and data sharding
- High availability and fault tolerance

This talk should be interesting to DBAs, system administrators, team leads, infrastructure architects, as well as wider audience dealing with PostgreSQL.

Locking is critical for providing high concurrency for any database — you cannot fully utilize your hardware if locking is throttling its use. This talk explores all aspects of locking in Postgres by showing queries and their locks; covered lock types include row, table, shared, exclusive, and advisory lock types. The high concurrency provided by Multiversion Concurrency Control (MVCC) is also covered.

Slides are at https://momjian.us/main/writings/pgsql/locking.pdf

We will discuss stored procedures and functions, as well as the code within the database in general. We'll take a look at stored procedures from different angles: as a DBA, a database developer, and a backend developer. We will try to understand the specifics of stored procedures that often go against the common coding guidelines, and discuss best practices for working with stored procedures.
This is mainly a motivational talk for database and backend developers, not an advanced tech talk.