In computer science, garbage in, garbage out is where bad, or nonsense input data produces nonsense output or "garbage". How do you protect your database from allowing garbage in? Using a constraint is an excellent method for keeping your data clean. Constraints are essentially rules that your data has to follow.
Datatypes are not something people think of as a constraint (because their not), but it's probably the most basic way we constrain data we use. If you want to store an integer and only integers then you would choose the INTEGER datatype. PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses. You could use TEXT to store these values, but TEXT will not ensure the values are valid network values.
The most common constraints are not null, check, unique, primary keys, and foreign Keys. Check out the PostgreSQL constraint documentation for the common usage of these constraints. There are also less common ways to use these constraints. For example, partial unique indexes are less common. Let's say you want to have a unique constraint on an email address column, but you also want to have a soft delete concept. You may choose to create a partial unique index like this:
|
1 2 3 |
CREATE UNIQUE INDEX ON students (email_address) WHERE is_deleted is null; |
Beyond very basic checks such as checking for positive prices, you could expand that to multiple columns. The PostgreSQL documentation provides a good example (you want to make sure sale prices are cheaper than the original price):
|
1 2 3 4 5 6 7 |
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), sale_price numeric CHECK (sale_price > 0), CHECK (price > sale_price) ); |
There are three data rules using a check constraint in this example, the price must be greater than 0, the sale price must also be greater than 0, and the price must be greater than the sale price. Another uncommon usage of constraints is to use a function to constrain your data. Take for example a DATE column that you only want to store dates that are the first day of the month. First, create a function that will do just that.
|
1 2 3 4 5 6 7 8 |
create function start_of_month(p_date date) returns date as $$ select date_trunc('month', mydate)::date; $$ language SQL immutable; |
Then add the function as a constraint.
|
1 |
create unique index on mytable ((start_of_month(mydate))); |
Constraining your data is extremely important to your data quality (and sometimes performance). Don't let bad data get into your database, it can be very difficult to remove. Don't rely on application code to keep your data clean. Your database is the foundation of your application and the database needs to prevent bad data.
PostgreSQL connections are process based not threaded like some other databases. Like everything else there are pros and cons to this method. One of the cons is that each PostgreSQL connection consumes more resources when compared to threaded connections. If you have a need to have a lot of connections to your database you may want to look at a connection pooler like pgBouncer.
PostgreSQL has views that allow you to see the details surrounding your sessions. Run the following PostgreSQL query to see how many sessions you have and what they are doing.
|
1 2 3 |
SELECT count(*), state FROM pg_stat_activity GROUP BY 2; |
Here is the result from one of my production 9.5 database clusters and the definition for each status.
|
1 2 3 4 5 6 7 |
+-------+---------------------+ | count | state | +-------+---------------------+ | 331 | idle | | 61 | idle in transaction | | 1 | active | +-------+---------------------+ |
In my example above you can see, there is only one active session (mine) and there are 331 idle sessions and 61 idle in transaction sessions. The number of idle and idle in transaction is a concern. The application that uses this database is quite old with a great deal of technical debt. Don't let your applications do this to your database. If you are running PostgreSQL 10 or later you may also see sessions with a NULL state. In that case you may want to use the following query.
|
1 2 3 4 5 |
SELECT count(*), state, backend_type FROM pg_stat_activity GROUP BY 2, 3 ORDER BY 2,3; |
Here are the result for a different production cluster.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+-------+--------+---------------------+ | count | state | backend_type | +-------+--------+---------------------+ | 1 | active | client backend | | 1 | active | walsender | | 4 | idle | client backend | | 1 | [NULL] | autovacuum launcher | | 1 | [NULL] | background worker | | 1 | [NULL] | background writer | | 1 | [NULL] | checkpointer | | 1 | [NULL] | walwriter | +-------+--------+---------------------+ |
The sessions with a NULL state are all background PostgreSQL sessions. Also, notice 1 of my active sessions is a walsender. Your application will be backend_type = client backend. On this database, the numbers are much more reasonable for a database that is not during a great deal of work currently.
It's critical to keep your PostgreSQL sessions in check. Fortunately, there are a couple of settings that will keep your users and applications behaving well. The first is statement_timeout. A statement timeout will automatically kill queries that run longer than the allotted time. You can set this at both a global level and for a specific session. To do this at the database level you’d run this:
|
1 2 |
ALTER DATABASE mysuperdb SET statement_timeout = 60000; |
This will not allow a query to be active longer than 60 seconds. This is generally a very long time for an OLTP database, but may be far too short of a duration for an OLAP or analytics data store. To deal with idle in transaction that has been running too long you can set idle_in_transaction_session_timeout (on Postgres 9.6 and later). This setting is similar to statement_timeout, but act on any session that is idle in transaction.
Quick Tip: Want to create a table just like another table (without data). It's simple to do with this command:
|
1 |
CREATE TABLE billing.orders_new (like billing.orders including all); |
If you are making modifications to the PostgreSQL configuration file (postgresql.conf) you will need to reload the file for your changes to take effect. Some configs need a full database cluster restart but many just require a reload of the configuration file.
Option 1 - From the OS (Linux) command line
|
1 2 |
su - postgres /usr/bin/pg_ctl reload |
Option 2 - From a database session
|
1 |
SELECT pg_reload_conf(); |
Both options will reload the configuration file without restarting the database or interrupt any active sessions or open connections.
Do you want to hide column values in PostgreSQL? We had that exact need, allow our DevOps engineers to run selects from a table and obfuscate some of the column values that are returned. For us, this masking of data generally applies to Personally Identifiable Information (PII). There are a couple of methods to achieve this goal. The first is to only grant SELECT on the columns you want them to be able to read. In this method not only will you hide column values, but you will also hide the column itself. Below is an example of limiting which columns a user can read.
|
1 2 3 4 5 |
CREATE TABLE public.test_column_priv (id serial PRIMARY KEY, yes_read text, no_read text); CREATE USER test_user; REVOKE ALL ON TABLE public.test_column_priv FROM test_user; INSERT INTO test_column_priv (yes_read, no_read) VALUES ('Yes I can read this','No I cannot read this'); |
Now we should not be able to read from the table at all when we log in as the test_user role.
|
1 2 |
[local] test_user@dm1=> select * from public.test_column_priv; ERROR: 42501: permission denied for relation test_column_priv |
Next, we can grant SELECT only on the ID and YES_READ columns.
|
1 |
GRANT SELECT (id, yes_read) ON public.test_column_priv TO test_user; |
Now we can select the ID and YES_READ columns, but not the NO_READ columns. In my opinion, the resulting error message can be confusing because we do have permissions on the table, just not on one of the columns. This methods will hide column values and is more secure than method 2.
|
1 2 3 4 5 6 7 8 9 10 11 |
[local] test_user@dm1=> select id, yes_read from public.test_column_priv; +----+---------------------+ | id | yes_read | +----+---------------------+ | 1 | Yes I can read this | +----+---------------------+ (1 row) Time: 0.721 ms [local] test_user@dm1=> select id, yes_read, no_read from public.test_column_priv; ERROR: 42501: permission denied for relation test_column_priv |
This solution was less than desirable for our DevOps engineers because they wanted to run queries "as-is" without any modifications. So we came up with a different option that's not perfect but closer. We obfuscated the data with search path, schemas, and views. Like in the previous example we will create a table that we want to mask and a new user that we want to make the data from.
|
1 2 3 4 5 |
CREATE TABLE public.test_column_priv (id serial PRIMARY KEY, yes_read text, no_read text); CREATE USER test_user; REVOKE ALL ON TABLE public.test_column_priv FROM test_user; INSERT INTO test_column_priv (yes_read, no_read) VALUES ('Yes I can read this','No I cannot read this'); |
Alternatively, you could revoke the privileges at the schema level if you are applying this logic to a broad set of tables.
|
1 |
REVOKE ALL privileges ON SCHEMA public from test_user; |
Next, we’re going to create a view that has the restricted view of the data.
|
1 |
CREATE VIEW protect_pii.test_column_priv AS SELECT id, yes_read, 'XXXXX'::TEXT as no_read from public.test_column_priv; |
Now we can grant the permissions necessary to read the data for our DevOps engineers. Here I am granting access to everything in the protect_pii schema, but you could also grant select on just the one table as well.
|
1 2 |
GRANT USAGE ON SCHEMA protect_pii to test_user; GRANT SELECT ON ALL TABLES IN SCHEMA protect_pii TO test_user; |
Finally, if you set the user's search_path they will try your new non-PII schema first and select from your non-PII view. The code below tells the user's session for queries that do not include a schema name try finding the object in the protect_pii schema first followed by the user's own schema and then finally in the public schema.
|
1 |
ALTER ROLE test_user SET search_path = protect_pii, "$user", public; |
Now your users can run selects against the protected tables, see all the columns, but not be able to view the PII dataset.
|
1 2 3 4 5 6 |
[local] test_user@dm1=> select * from test_column_priv; +----+---------------------+---------+ | id | yes_read | no_read | +----+---------------------+---------+ | 1 | Yes I can read this | XXXXX | +----+---------------------+---------+ |
Optionally you could not include the PII column in your view, but then your query could not be run "as-is" when the select explicitly calls out column names. Also be aware of the protected column's datatype. In this example, we set it to text with ::TEXT, but you may need to set a different datatype depending on the underlying table.
