By fusing PostgreSQL native row-level security
with external to the database authentication providers, Neon Authorize offers a new, efficient and transparent way for securing access for database-driven applications.

The key concept to take away is that Neon handles the authorization part; the authentication part of managing user identities, passwords, and security tokens is still handled by third-party JWT-based authentication providers like Auth0 and Clerk.

What Neon does is integrating that external authentication with the internal authorization required by the database in order for a user to access his own underlying rows, by extending PostgreSQL's native Row Level Security. Let's see an example in action by enabling RLS on a table called 'departments' :

ALTER TABLE departments
ENABLE ROW LEVEL SECURITY;

--Next we create the policy

CREATE POLICY department_managers
ON departments
TO managers
USING (manager = current_user);

Now you no longer have to write application level queries such as

select * from departments
where manager = 'Alice'

as this is handled by the database itself automatically and as such

select * from departments

issued by Alice can retrieve only her rows and no one else's.

Of course for it to work you have to authenticate/ log the user in the user by any means necessary, so that current_user becomes 'Alice'.

With Neon Authorize this process goes a step beyond. Since nowadays most applications authenticate with the use of JWTs, that JWT is passed on to Neon which then can make use of the validated user identity directly in Postgres. To facilitate the integration with an authentication provider, you will have to add your provider's JWT discovery URL to your Neon project. This lets Neon retrieve the necessary keys to validate the JWTs:

As such, when your code calls:

const sql = neon(process. env. DATABASE_AUTHENTICATED_URL, { authToken: myAuthProvider. getJWT() });

this derives the user id from the token, which Neon's open source pg_session_jwt extension makes it available to Postgres for RLS to take place.

Furthermore, you’ll also have access to a few utility functions such as auth. session() and auth.user_id() which will help you use the JWTs that are coming from your auth provider, like :

SELECT
u.name,
u.email
FROM
users u
WHERE
auth.user_id() = u.user_id

At the time of writing Neon Authorize is compatible with the following Auth providers:

• Clerk
• Stack Auth
• Auth0
• Stytch
• AWS Cognito
• Azure AD
• Descope Auth
• Supertokens
• Firebase Auth
• Keycloak
• GCP Cloud Identity

but that list keeps expanding.

With that in place we can remove access control from the application layer to rely solely on RLS at the database level
which renders the authorization layer for your application declarative rather than imperative, with the advantages this has. In theory and in some simple cases you could even forgo having backend code and handle everything client side, talking straight to the database.

You can run Neon locally or if you prefer cloud managed then you can start right away with the free offering for up to 10 projects and upgrade later if necessary. There's also a good tutorial to get you started, which uses Clerk as the authentication provider.

More Information

About Neon Authorize
Neon Authorize + Clerk Example (SQL from the Backend)

Related Articles

Discover PostgreSQL How-Tos

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info