PostGraphile is an open-source tool that allows you to automatically generate a powerful and secure GraphQL API from your PostgreSQL database. It serves as a GraphQL schema and API middleware for PostgreSQL databases, making it easier to interact with your data using GraphQL queries.
- PostGraphile automatically introspects your PostgreSQL database and generates a GraphQL schema based on the tables, views, and relationships found in the database. This saves developers from writing boilerplate code for their GraphQL API.
- It supports real-time data updates through GraphQL subscriptions. This allows clients to subscribe to specific data changes and receive updates whenever relevant data in the database is modified.
- PostGraphile allows you to implement fine-grained access controls and authentication mechanisms to secure your GraphQL API and data.
- PostGraphile is designed with security in mind, and it automatically handles SQL injection prevention, access control, and input validation to protect against common security vulnerabilities.
Overall, PostGraphile is a powerful tool for rapidly creating GraphQL APIs on top of PostgreSQL databases, streamlining the development process and making it easier to build efficient and secure applications that interact with your data.
I built a simple CRUD blog API with a working authentication and user access management simply with SQL (plpgsql). I used db-migrate npm package for database migration
Registration Function
CREATE FUNCTION blog.register_user(
name TEXT,
email TEXT,
password TEXT,
title TEXT,
description TEXT
) RETURNS blog.user AS
$$
DECLARE
userRec blog.user;
BEGIN
INSERT INTO
blog.user (name, title, description)
VALUES
(name, title, description) RETURNING * INTO userRec;
INSERT INTO
auth.user_auth (user_id, email, password_hash)
VALUES
(
userRec.id,
email,
crypt(password, gen_salt('bf'))
);
RETURN userRec;
EXCEPTION
WHEN unique_violation THEN RAISE 'User with this email already exists';
END;
$$
LANGUAGE plpgsql
STRICT
SECURITY DEFINER;
-- 🍲 By AnthonyLogin Function
CREATE FUNCTION blog.login(email TEXT, password TEXT) RETURNS blog.jwt AS
$$
DECLARE
userAuth auth.user_auth;
BEGIN
SELECT
* INTO userAuth
FROM
auth.user_auth
WHERE
user_auth.email = login.email;
IF userAuth.password_hash = crypt(password, userAuth.password_hash) THEN RETURN (
'blog_user',
userAuth.user_id,
extract(epoch from(now() + interval '3 days'))
) :: blog.jwt;
ELSE RAISE 'Wrong Password';
END IF;
END;
$$
LANGUAGE plpgsql
STRICT
SECURITY DEFINER;
-- 🍲 By AnthonyAccess Control
ALTER DEFAULT privileges REVOKE EXECUTE ON FUNCTIONS FROM public;
GRANT usage ON SCHEMA blog TO blog_user, blog_guest;
GRANT EXECUTE ON FUNCTION blog.register_user(TEXT, TEXT, TEXT, TEXT, TEXT) TO blog_guest;
GRANT EXECUTE ON FUNCTION blog.login(TEXT, TEXT) TO blog_user, blog_guest;
GRANT ALL privileges ON TABLE blog.user TO blog_user;
GRANT ALL privileges ON TABLE blog.post TO blog_user;
GRANT usage ON SEQUENCE blog.post_id_seq TO blog_user;
-- Allow unregistered guests to only list and get users and posts
GRANT SELECT ON TABLE blog.user TO blog_guest;
GRANT SELECT ON TABLE blog.post TO blog_guest;
-- 🍲 By Anthony