Neon database roles are just PostgreSQL roles, but with some Neon-specific twists that make managing permissions a bit more nuanced than you might expect.
Let’s see it in action. Imagine you’ve got a Neon project with a main branch, and you want to grant a new user, app_user, read-only access to a specific table, products, in your public schema.
-- Connect to your Neon database as a superuser (e.g., postgres)
-- First, create the user role if it doesn't exist
CREATE ROLE app_user WITH LOGIN PASSWORD 'supersecretpassword';
-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO app_user;
-- Grant select privileges on the specific table
GRANT SELECT ON TABLE public.products TO app_user;
-- Verify the permissions
-- Connect as app_user and try to select from products
-- SELECT * FROM public.products; -- This should work
-- Try to insert or update (this should fail)
-- INSERT INTO public.products (name) VALUES ('New Gadget'); -- This should result in an "permission denied" error
The key here is that in Neon, like in PostgreSQL, roles are the fundamental building blocks for access control. You create roles, assign them privileges, and then grant those roles to users (which are also roles, but with the LOGIN attribute). The Neon platform abstracts away the underlying PostgreSQL server management, but the SQL commands for role and permission management remain the same.
The problem this solves is fine-grained access control. Instead of giving a user full superuser access to your entire database, you can create specific roles with just the permissions they need. This drastically reduces the blast radius if a user’s credentials are compromised or if an application makes an unintended modification. It’s about the principle of least privilege: a user or application should only have the minimum permissions necessary to perform its intended function.
Internally, when you execute GRANT SELECT ON TABLE public.products TO app_user;, PostgreSQL modifies its internal catalog tables (like pg_class and pg_namespace) to record this permission. When app_user attempts to query public.products, the PostgreSQL query planner consults these catalog tables to determine if app_user has the SELECT privilege on that specific table within the public schema. Neon’s infrastructure ensures that these catalog changes are persistent and correctly applied to the appropriate database instance.
The most surprising thing for many is how ownership of objects interacts with permissions. If app_user were the owner of the products table, they would implicitly have all privileges on it, overriding any GRANT statements that try to restrict them. This is why it’s crucial to manage object ownership carefully; often, you’ll want objects owned by a dedicated, non-login role, and then grant specific privileges to application users.
The next concept you’ll likely encounter is row-level security, which allows you to define policies that filter which rows a user can see or modify, even if they have SELECT or UPDATE privileges on the table.