Common Security Setup Mistakes and How to Prevent Them

"Database security is like a good password: you can come up with the most complicated key, but if you write it on a sticky note and slap it on your monitor—it's pointless." So our job is not just to set up protection mechanisms, but also to avoid the usual mistakes that can ruin all your efforts.

1. Using Roles with Excessive Privileges

Developers are often afraid to restrict access and create roles with broad privileges, like giving SUPERUSER or ALL PRIVILEGES. Their argument is: "Well, what if I need it later!" But roles with too many privileges are a huge security hole.

Example of excessive privileges:

GRANT ALL PRIVILEGES ON DATABASE university TO student_role;

Here, student_role gets full access to all the data in the database. Even if the role was only supposed to read data, now it can delete tables, change structure, and even take away admin access.

How to avoid this?

Create roles with the minimum set of privileges. This is called the principle of least privilege. For example, a read-only role should look like this:

GRANT CONNECT ON DATABASE university TO student_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO student_role;

This approach makes it clear what student_role can do: connect to the database and only read data.

2. Not Encrypting Sensitive Data

Imagine a users table where we store passwords as plain text:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

If an attacker gets access to this table, they get all users' passwords. It's like keeping your apartment keys under the doormat.

To avoid this, use password encryption with pgcrypto. For example:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO users (username, password)
VALUES ('johndoe', pgp_sym_encrypt('secure_password', 'encryption_key'));

To check a password, you can use decryption:

SELECT username
FROM users
WHERE pgp_sym_decrypt(password::BYTEA, 'encryption_key') = 'secure_password';

Never store sensitive info in plain text!

3. Ignoring SQL Injections

SQL injections are still one of the most common attack methods, and that's because devs keep building queries using string interpolation. Here's an example:

DO $$
DECLARE
    username TEXT := 'John';
    query TEXT;
BEGIN
    query := 'SELECT * FROM users WHERE username = ''' || username || ''';';
    EXECUTE query;
END $$;

If an attacker sends John' OR '1'='1 instead of a username, you'll end up leaking all data from the users table.

How to avoid this? Use parameterized queries:

PREPARE user_query (TEXT) AS
SELECT * FROM users WHERE username = $1;

EXECUTE user_query('John');

Here, the variable is safely substituted, so injection isn't possible.

4. Misconfigured pg_hba.conf

pg_hba.conf is the main tool for controlling access by IP address. Mistakes in its setup can open up access way more than you want.

Example of a bad config:

host    all     all     0.0.0.0/0       trust

This line lets any user connect to any database from any IP address without a password.

How to avoid this? Set up access only for specific IP addresses and use the md5 or scram-sha-256 authentication method:

host    university    student_role    192.168.1.0/24    md5

This limits student_role access to just the local network and requires a password.

After changing pg_hba.conf, don't forget to apply the changes with:

pg_ctl reload

5. Incorrect Use of ROW LEVEL SECURITY

RLS is a powerful tool, but it's useless if you set it up wrong or forget to turn it on. For example, even after writing an access policy, it won't work if RLS is off:

CREATE POLICY my_policy ON users
USING (username = current_user);

-- But RLS is not enabled!
SELECT * FROM users; -- You'll get all the rows!

How to avoid this? Don't forget to enable RLS:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

And check how the policy works:

SET ROLE student_role;

SELECT * FROM users; -- Only rows matching the policy are visible.

6. Unaccounted Admin Actions

Sometimes database admins have full access to all data, even if they don't need it for their job. This adds extra risk if the admin account gets compromised.

How to avoid this? Use role separation. For admin tasks, create a separate role with no data access:

CREATE ROLE admin_role WITH LOGIN CREATEDB CREATEROLE;

For data access, create another role with minimal privileges:

CREATE ROLE data_analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst_role;

Assign roles to users based on their tasks:

GRANT admin_role TO some_user;
GRANT data_analyst_role TO another_user;

7. Insufficient Logging

If you don't set up logging, you won't know about suspicious activity until it's too late.

Example of no logging:

-- No settings in postgresql.conf
log_statement = 'none';

How to avoid this? Turn on at least basic logging:

log_statement = 'all'
log_connections = on
log_disconnections = on

This lets you see all executed queries, connections, and disconnections.

You can also set up auditing with the pgAudit extension for more detailed control:

CREATE EXTENSION pgaudit;

8. Using Outdated Authentication Methods

Using outdated authentication methods like password doesn't provide enough protection.

How to avoid this? Switch to more secure methods like scram-sha-256:

ALTER SYSTEM SET password_encryption = 'scram-sha-256';

And update user passwords:

ALTER USER student_role WITH PASSWORD 'new_secure_password';

These issues might seem minor, but each one can turn into a serious security hole. Your job is to run your database like every user trying to connect is suspicious. Like they say, "trust, but verify." Now you've got the tools not just to set up security, but to avoid the most common mistakes. Go grab that luck by the tail, and keep your data safe!