Search for a command to run...
Yusuf Adeyemo is a DevOps Engineer from Nigeria. He loves helping startups deliver better software and provide more control over their environment and software development process with the help of modern tools and automation.
No comments yet. Be the first to comment.
We’ve all been there. You spend hours perfectly tuning your OpenWrt configuration, setting up your captive portals, and hardening your firewall. Then, six months later, the system hangs. Why? Because
This article started from a tweet. Someone on Twitter said they "lowkey want to understand the technology behind Google Authenticator" and I dropped a quick reply - explaining that it's basically TOTP: your device and the server share a secret key, b...
Have you ever sat there waiting for your CodeBuild project to rebuild your entire Docker image... again? Even though you only changed a single line of code? Yeah, me too. And it's frustrating. Today, I'm going to show you how I reduced our Docker bui...
After you complete this article, you will have a solid understanding of: Why deploying Next.js SSR on Cloudflare is different from traditional hosting The real difference between Cloudflare Pages with next-on-pages and OpenNext adapter How the fre...
After you complete this article, you will have a solid understanding of: What L1, L2, and L3 constructs actually are and when to use each Why AWS created three different abstraction levels (and the hidden benefits) How to avoid the most common CDK...
Imagine this scenario: You have a multi-tenant PostgreSQL database where different teams or customers share the same schema. The last thing you want is one user accidentally (or maliciously) seeing another user’s data. That’s exactly where Row-Level Security (RLS) steps in, acting like an invisible bouncer for each row in your database.
In this article, we’ll explore RLS in PostgreSQL through a practical example involving pg_cron, a built-in job scheduling extension for PostgreSQL. We’ll walk through the benefits of this fine-grained security model, demonstrate how pg_cron leverages it, and highlight best practices to keep your database environment both secure and efficient.
At its core, Row-Level Security is a PostgreSQL feature that allows you to enforce access policies at the most granular level: the row. Instead of trusting your application code to handle security logic, RLS shifts that responsibility to the database engine itself. Each time a user queries a table, RLS policies determine which rows they can view or modify—automatically and behind the scenes.
Multi-Tenant Isolation: Ideal for SaaS applications where multiple tenants share the same database.
Reduced Risk: Minimizes data leaks caused by application bugs or misconfigurations.
Cleaner Code: Moves security logic from the application layer to the database layer, making your code less cluttered.
Less Overhead: Users only see the rows they have access to, with no extra logic needed in queries or controllers.
To illustrate RLS, let’s look at pg_cron, PostgreSQL’s job scheduling extension. With pg_cron, you can schedule periodic tasks (like database backups or maintenance jobs) by storing job definitions inside dedicated tables.
cron.job: Stores scheduled job definitions (think of it like a cron schedule entry).
cron.job_run_details: Stores execution history for those jobs.
By default, pg_cron uses RLS to ensure that each user can only manage or view the jobs they’ve created.
Here’s a peek at how pg_cron’s built-in RLS policies look:
CREATE POLICY cron_job_policy ON cron.job
USING (username = CURRENT_USER);
CREATE POLICY cron_job_run_details_policy ON cron.job_run_details
USING (username = CURRENT_USER);
These policies effectively say: “Only show rows where username matches the currently logged-in user.” It’s a simple, yet powerful way to ensure user separation in a multi-tenant or multi-user environment.
Let’s walk through some hands-on steps to see how RLS and pg_cron work together.
By default, PostgreSQL requires you to enable RLS on a table before policies take effect. In pg_cron, this is often done for you, but if you ever need to do it manually:
ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY;
ALTER TABLE cron.job_run_details ENABLE ROW LEVEL SECURITY;
When a user creates a job—say, a daily VACUUM ANALYZE—it automatically gets tagged with their identity. For example:
SELECT cron.schedule('daily-backup', '0 0 * * *', 'VACUUM ANALYZE');
The RLS policy ensures the job’s row is “owned” by the user who created it. When another user queries the cron.job table, they won’t see this entry.
Because of the RLS policy, each user sees only their own rows:
-- Logged in as User1:
SELECT * FROM cron.job;
-- Result: Only User1’s jobs
-- Logged in as User2:
SELECT * FROM cron.job;
-- Result: Only User2’s jobs
What if you’re an admin and need to see every user’s job? You can create a policy that grants full visibility to superusers or a specific admin role:
CREATE POLICY admin_cron_job_policy ON cron.job
USING (
username = CURRENT_USER
OR CURRENT_USER IN (SELECT rolname FROM pg_roles WHERE rolsuper)
);
With this in place, admins can bypass the default policy and see all rows in cron.job.
You might have a monitoring role that needs to view all scheduled jobs but not modify them. Here’s how to give them read-only access:
-- Create a read-only policy for the monitor role:
CREATE POLICY monitor_cron_job_policy ON cron.job
FOR SELECT
TO monitor_role
USING (true);
-- Grant SELECT permissions on the cron.job table:
GRANT SELECT ON cron.job TO monitor_role;
In some organizations, teams need to share access to each other’s jobs while still isolating from other groups. You can implement a team-based policy, assuming a separate table (e.g., user_teams) stores team associations:
CREATE POLICY team_cron_job_policy ON cron.job
USING (
team_id = (
SELECT team_id
FROM user_teams
WHERE username = CURRENT_USER
)
);
Test Your Policies Thoroughly: Run queries under different user roles to confirm that policies behave as intended.
Document Everything: Clear documentation on who can see and do what saves you headaches later.
Conduct Regular Audits: Periodically review logs and access patterns to ensure policies are still aligned with your security needs.
Include Policies in Backups: Policies are part of your schema. Make sure they’re included in any disaster recovery strategy.
Performance Considerations: Very complex or large sets of RLS policies can affect query performance. Keep an eye on your query plans.
Overlapping Policies: Multiple policies can interact in unexpected ways. Always test for unintended overlaps.
Maintenance Overhead: More policies mean more complexity. Review them regularly to ensure they’re still necessary.
If you ever need a bird’s-eye view of all active RLS policies:
SELECT schemaname, tablename, policyname, roles, cmd, qual
FROM pg_policies
WHERE schemaname = 'cron';
Check Permissions:
SELECT has_table_privilege('username', 'cron.job', 'SELECT');
Examine Query Plans:
EXPLAIN (ANALYZE) SELECT * FROM cron.job;
This helps you see if policies are being applied and how they affect performance.
Row-Level Security is a game-changer for multi-tenant or multi-user PostgreSQL databases. By integrating RLS with pg_cron, you get a firsthand look at how PostgreSQL enforces strict data isolation at the row level—automatically filtering out data that a user shouldn’t see.
Whether you’re managing a small startup or a large enterprise environment, RLS helps you sleep easier by ensuring each user’s data remains exactly where it should: out of sight for everyone else. Pair this with careful monitoring, thorough testing, and clear documentation, and you’ve got a powerful, secure setup that keeps your database environment running smoothly.
Feel free to explore these resources for more in-depth information. As always, happy coding and scheduling!