Database Security Basics: Managing Users and Permissions

Previously in this course, we covered exporting and importing data to move information in and out of our store database. Now that our data is populated, we need to ensure that only authorized entities can interact with it.

In professional database administration, we operate under the "Principle of Least Privilege." This means every user—whether a person or an application service—should have exactly the permissions required to do its job, and nothing more. Using a single "superuser" account for everything is a major security risk, as a compromised application could then delete your entire schema.

Creating a New User (Role)

In PostgreSQL, users and groups are managed as "roles." A role can be a login account (a user) or a collection of permissions (a group).

To create a new user for your store application, use the CREATE ROLE command. We will add a LOGIN attribute so this role can connect to the database.

SQL
-- Create a new user specifically for our store application
CREATE ROLE store_app_user WITH LOGIN PASSWORD 'secure_password_123';

Note: Always use strong, unique passwords in production. For local development, keep your credentials documented in a safe, encrypted password manager.

Granting Permissions

Once the user exists, they cannot "see" your tables yet. By default, a new user has no access to your existing schema. You must explicitly grant them rights to interact with your data.

Permissions are categorized into two levels: Database-level (connecting to the database) and Schema-level (interacting with tables).

1. Database Connection

First, give the user permission to connect to your specific store database:

SQL
GRANT CONNECT ON DATABASE store_db TO store_app_user;

2. Schema and Table Access

Next, grant permissions to the tables. For an application that only needs to read and write data, you would grant SELECT, INSERT, UPDATE, and DELETE.

SQL
-- Grant access to the public schema
GRANT USAGE ON SCHEMA public TO store_app_user;

-- Grant specific table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE products TO store_app_user;

If you have sequence objects (which we covered in using sequences for ids), you must also grant usage on them so the application can generate new IDs:

SQL
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO store_app_user;

Revoking Access

Security is dynamic. If an employee leaves your team or an application service is retired, you must revoke their access immediately. The REVOKE command removes permissions without deleting the user.

SQL
-- Remove update and delete privileges from the user
REVOKE UPDATE, DELETE ON TABLE products FROM store_app_user;

-- Completely disconnect the user from the database
REVOKE CONNECT ON DATABASE store_db FROM store_app_user;

Hands-on Exercise

1. Create a read-only role named store_viewer that only has SELECT access to the products table.
2. Verify the permissions by connecting to the database using this new user via psql (or your preferred GUI tool).
3. Try to perform an INSERT with the store_viewer role; it should fail with a "permission denied" error.

Common Pitfalls

• Forgetting Schema Usage: You can grant table permissions all day, but if you don't GRANT USAGE ON SCHEMA public, the user won't be able to "see" the tables inside that schema.
• Superuser Overuse: Avoid using the default postgres superuser for your application code. If the application is hacked, the attacker gains full control over the database cluster.
• Granting ALL PRIVILEGES: While convenient, it violates the principle of least privilege. Always be explicit about what the user needs.

FAQ

Q: What is the difference between a Role and a User? A: In PostgreSQL, they are technically the same thing. A "user" is just a role that has the LOGIN attribute.

Q: Should I grant permissions to each table individually? A: For small apps, yes. For larger apps, it is better to group permissions into roles and grant those roles to users, a concept explored in implementing roles and permissions: RBAC for SaaS databases.

Q: How do I see who has access to a table? A: You can use the \dp command in psql to see the access control list (ACL) for tables.

Recap

We’ve moved beyond data management into system security. We learned that:

• Users are created with CREATE ROLE and assigned a LOGIN attribute.
• Access is granted in layers: Connection → Schema → Table → Sequence.
• We use REVOKE to adhere to the principle of least privilege, ensuring no entity has more power than it needs.

Up next: We will learn how to use EXPLAIN to look under the hood of our queries and start analyzing query plans to identify performance bottlenecks.