In Part A, we created a REST API for a blogging site using Postgres and PostgREST. In this part, we're going to implement some authentication endpoints and add authorization rules.

In an HTTP request, you can send request data as a body of the request. Apart from body, you can also send headers with a HTTP request. These request headers include additional information about the request. For example, a common header that is used in REST requests is the Accept: application/json one. This header states that client is hoping to get back a JSON response. So, a server can look at this header and decide to send back data in JSON format.

Similarly, the response from the server can also include headers which are called response headers. For example, a cache header like Cache-Control: max-age=3600 in the response instructs clients to not cache the response for more than 3600 seconds.

In Insomnia, you can pass additional request headers using the Headers tab and see the response headers in the Header tab of the response. Later in this tutorial, we will be using a request header called Authorization to pass in authentication tokens.

Look at all the HTTP headers that your browser sents when it makes a web request: http://gethttp.info/

In this section, we will:

• Create two roles: non-authenticated users and authenticated users
• Non-authenticated users will be able to view all the tables
• Authenticated users will be able to view and modify all the tables

Although different projects have specific needs regarding who can view and edit what, this rule is generally applicable to all projects in this class so each group is expected to implement at least this level of authentication and authorization.

Let's start by creating these two roles and grant permissions:

CREATE ROLE authenticator NOINHERIT;
CREATE ROLE admins;
CREATE ROLE anonymous;

GRANT anonymous, admins TO authenticator;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO anonymous;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO admins;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admins;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO admins;

Then, edit postgrest.conf file and change db-anon-role to anonymous as following:

db-anon-role = "anonymous"

What this does is that postgrest initially starts by being an authenticator role and depending on whether the requests are authenticated or not, switches to different roles.

After doing this, restart postgrest and try running some of your Insomnia requests again. GET requests should work but requests which modify the tables such as POST, PUT, or DELETE should fail with permission denied for table tablename. This means PostgREST is switching to anonymous role because our request did not have any authentication parameters. In the next section, we're going to implement that.

Authentication can be implemented in API requests in lots of different ways. Token authentication is one of the most common API authentication style. In token authentication, apart from request data, a separate token is also passed normally as a HTTP request header. In the backend, this token is verified and based on this token, users and roles are identified. For example, consider the following request:

POST /articles
Headers
  Authorization: Bearer 12345
Body
  title: "New Post Title"
  description: ""

So the backend server can look at the HTTP header with key Authorization and verify that token.

Note that Bearer is just a common name for token values in HTTP headers and could be anything as long as backend knows how to grab tokens.

Tokens can be verified in many ways. You could store tokens in a separate table and check against it. An alternative way is to use JWT tokens which are based on cryptographic functions and can be verified without having to store it in the database. PostgREST supports JWT tokens out of the box so we are going to use that.

To configure PostgREST to use JWT, let's setup a private secret that can be used to generate JWT and ensure that no one without that secret can generate a fake JWT. Add the following properties to your postgrest.conf file:

jwt-secret = "reallyreallyreallyreallyverysafe123"
app.settings.jwt_secret = "reallyreallyreallyreallyverysafe123"

Replace them with something random. Both of these secrets should have the same value. Parameter jwt-secret is used by PostgREST to validate incoming requests with JWT tokens. The parameter app.settings.jwt_secret allows us to use the secret inside our custom queries which we are going to use later to create endpoints for logging in and registration.

With this in place, restart the PostgREST server. Now, any request with Authorization: Bearer <token> header will be validated. Let's try that by creating a JWT token manually and passing it along with our POST request:

• Go to https://jwt.io/
• On the right-hand side, replace payload with {"role": "admins"}, and set your secret key in the secret key box. The left-hand side should give you a JWT token.

• Go back to one of the Insomnia requests where you got "permission denied". Add a new header with key Authorization and Value Bearer <token> as shown like in the screenshot below:

• This time you should be able to create a new article (or your entity).

So, how did it work?

• Insomnia passed the token in the HTTP request
• PostgREST saw that token and tried validating it with the JWT secret that we set in postgrest.conf file
• PostgREST found out that the token is valid. So, it looked for role value inside the token and switched the database role to admins for this request.
• With the switch to the database role admins, database-level rules kicked in and we got access to read and write to all the tables that admins role had access to.

Until now, we manually created a JWT token and passed it in the request. But in a real web application, the flow happens something like the following:

• User provides their username and password
• An endpoint (say /login) is POSTed with the provided username and password. The backend server generates a JWT token after validating username/password and passes it back to the client
• In subsequent requests, client uses this JWT token that it got back from the server and passes it as Authorization header in those requests.

To implement this in PostgREST, we are going to create two functions login and register. First, start by creating some helper functions we need to generate JWT tokens:

-- Setup JWT generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;

DROP TYPE IF EXISTS jwt_token CASCADE;
CREATE TYPE jwt_token AS (
  token text
);

CREATE OR REPLACE FUNCTION url_encode(data bytea) RETURNS text LANGUAGE sql AS $$
    SELECT translate(encode(data, 'base64'), E'+/=\n', '-_');
$$;

CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text)
RETURNS text LANGUAGE sql AS $$
WITH
  alg AS (
    SELECT CASE
      WHEN algorithm = 'HS256' THEN 'sha256'
      WHEN algorithm = 'HS384' THEN 'sha384'
      WHEN algorithm = 'HS512' THEN 'sha512'
      ELSE '' END AS id)
SELECT url_encode(hmac(signables, secret, alg.id)) FROM alg;
$$;


CREATE OR REPLACE FUNCTION sign(payload json, secret text, algorithm text DEFAULT 'HS256')
RETURNS text LANGUAGE sql AS $$
WITH
  header AS (
    SELECT url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8')) AS data
    ),
  payload AS (
    SELECT url_encode(convert_to(payload::text, 'utf8')) AS data
    ),
  signables AS (
    SELECT header.data || '.' || payload.data AS data FROM header, payload
    )
SELECT
    signables.data || '.' ||
    algorithm_sign(signables.data, secret, algorithm) FROM signables;
$$;
--

Now, let's create a function that takes in user details and adds it to our users table:

CREATE OR REPLACE FUNCTION
signup(email text, password text, name text) RETURNS VOID
AS $$
  INSERT INTO users (email, password_hash, name) VALUES
    (signup.email, signup.password, signup.name);
$$ LANGUAGE sql SECURITY DEFINER;

You should modify this function to adapt it to your requirements and table names.

and a login function which generates JWT token based on valid credentials and return a token with role "admins":

CREATE OR REPLACE FUNCTION
login(email TEXT, password TEXT) RETURNS jwt_token
  LANGUAGE plpgsql SECURITY DEFINER
  AS $$
DECLARE
  _role NAME;
  result jwt_token;
BEGIN
  SELECT users.userid FROM users WHERE users.email=login.email AND password_hash=login.password INTO _role;
  IF _role IS NULL THEN
    RAISE invalid_password USING message = 'invalid user or password';
  END IF;

  SELECT sign(
      row_to_json(r), current_setting('app.settings.jwt_secret')
    ) AS token
    from (
      SELECT 'admins' AS role, login.email AS email, _role AS user_id,
         extract(epoch from now())::integer + 3600*60*60 as exp
    ) r
    INTO result;

  RETURN result;
END;
$$

You should modify the FROM and WHERE clauses of the SELECT statement to suit your tables.

Finally, non-authenticated users should be able to execute these functions. So, add the following GRANTS:

GRANT EXECUTE ON FUNCTION
  login(text,text),
  signup(text, text, text)
  TO anonymous;

Now, let's try creating a user. Use the /rpc/signup function to create a new user as shown in the screenshot below. Since email was declared as unique in our database, notice that it doesn't let you call this function twice with the same email which is what we intended.

Login should also work now. With valid credentials, it should return back a JWT token that you can use for subsequent requests:

You can verify this token by pasting it on the left-hand side of https://jwt.io and using the same secret in the secret box.

And you're done!

In login and register functions, we are storing and checking against plaintext passwords. Modify these functions to use the pgcrypto extension to hash the password before saving and check against the hashed password. See an example here.

In the implementation above, any admins can edit articles for any of the users. So, what if we wanted to allow only the owners of the articles to take actions on it? We can do something like this:

ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS articles_policy ON articles;
CREATE POLICY articles_policy ON articles
    USING TRUE --- read policy (allow everyone to select articles)
    WITH CHECK (author_id = current_setting('request.jwt.claim.user_id')::INTEGER) --- write policy (only allow users with author_id same as the one in the row to edit it)

Implement Row-level Security in your project for at least one table to get extra credits.

• Submit the export of your Insomnia workspace again
• Submit a txt file containing your modified login and signup functions adapted to your database.
• To get extra credits for Row Level Security, also include your SQL statements you used for that

Backup your database before using these commands.

REASSIGN OWNED BY anonymous, admins TO <superuser_name_same_as_one_you_used_for_postgrest>;
DROP OWNED BY anonymous;
DROP OWNED BY admins;
DROP ROLE anonymous;
DROP ROLE admins;

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM anonymous;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM anonymous;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM anonymous;

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM admins;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM admins;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM admins;

When you run commands through a SSH connection, they are killed once you disconnect. To ensure they keep running even after you disconnect, you can run them in a persistent terminal mangagers like tmux:

• Connect to the server via SSH
• Run tmux
• Now, run your commands like postgrest

Now, you can disconnect but the commands that you ran will continue running. If you want to get to that running terminal again:

• Connect to the server via SSH
• Run tmux attach

A better but a more complex alternative is to run it as a system process but it is out of scope of this class.

python sqlmap.py -u 'http://localhost:8000/articles?articleid=eq.*' --dbms=PostgreSQL --level=5 --risk=3 -f --banner --dbs -v 1