Apache/Nginx configuration - How to link postgrest api with my domain name ?

[GeoGyro]

As documentation suggests, how to set up Nginx or Apache with PostgREST API ?

After following tutorial, no problem to run queries like curl http://localhost:3000/todos, but how to deploy and redirect this set up to web requests like https://www.mywebsite.com/api/todos ?

I read this post, and I'm looking for examples and user's feedbacks.

Apache is set up for https:

SSLEngine On
SSLProtocol All -SSLv3 -SSLv2
SSLCipherSuite HIGH:!anNULL:MD5:!ADH:!RC4:!DH
SSLHonorCipherOrder on
SSLCertificateFile "/etc/letsencrypt/live/mywebsite.com/fullchain.pem"
SSLCertificateKeyFile "/etc/letsencrypt/live/mywebsite.com/privkey.pem"

postgREST service is running on port 3000:

[Unit]
Description=REST API for any PostgreSQL database
After=postgresql.service

[Service]
ExecStart=/bin/postgrest /etc/postgrest/config/api.conf
ExecReload=/bin/kill -SIGUSR1 $MAINPID

[Install]
WantedBy=multi-user.target

/etc/postgrest/config/api.conf

db-uri = "postgres://myuser:[email protected]:5432/my_database"
db-schema = "my_schema"
db-anon-role = "my_anonymous_user"

Nginx configuration file /etc/nginx/sites-enabled/api.conf :

upstream postgrest {
    server localhost:3000;
}

server {    
    listen 443 ssl;
    server_name mywebsite.com;
    root /var/www/html/api;   

# SSL configuration
    ssl_certificate     /etc/letsencrypt/live/mywebsite.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/mywebsite.com/privkey.pem;
    ssl_protocols       TLSv1 TLSv1.1 TLSv1.2;
    ssl_ciphers         HIGH:!aNULL:!MD5;   
	
# PostgREST API
    location /api/ {
        default_type  application/json;
        proxy_hide_header Content-Location;
        add_header Content-Location  /api/$upstream_http_content_location;
        proxy_set_header  Connection "";
        proxy_http_version 1.1;        
        proxy_pass http://localhost:3000;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    location ~ /\.ht {
        deny all;
    }
}

But something is wrong or missing, any ideas ?

PS: also asked here

[j-ova]

Hello,

Can you give more details on the encountered error? What browser, what behavior, error codes, logs, ...?

Also, only one app can listen to a given port on a given IP.
You are giving conf details for Apache and Nginx, but unless you have a very particular network infrastructure, only one of them can receive your request on https://www.mywebsite.com/api/todos. Are you sure it is Nginx ?

As a side note, in your Nginx api.conf file, I would use the upstream you set up in the proxy_pass instruction, and maybe add the request uri (not sure if that one is necessary) :
proxy_pass http://postgrest$request_uri;

Edit: Not sure if it is another issue, but your server_name is set to answer on mywebsite.com, but your request calls for **www.**mywebsite.com.

Regards,

[GeoGyro]

Hello and thanks for your answer.

Error encountered is 404 when I try https://www.mywebsite.com/api/todos web request. My setup isn't good.

I tried Nginx, as documentation suggests, but Apache was already installed on server. Actually both services are running. It says :

We rely on a reverse proxy like Nginx for additional safeguards.

Do you think I can configure Apache with postgREST ? How to translate /etc/nginx/sites-enabled/api.conf instructions to Apache syntax ?

If so, I must turn off Nginx and update my Apache configuration file with postgREST API proxy parameters (and use the upstream in the proxy_pass instruction as you said). In /etc/apache2/sites-enabled, I have mywebsite.com-ssl.conf, I suppose I have to complete it or create an other file like /etc/apache2/sites-enabled/api.conf ?

About server_name: where do you see a request like **www.**mywebsite.com. ?

[j-ova]

About server_name: where do you see a request like **www.**mywebsite.com. ?

I just wanted to emphasize the "www." in your web request, while your Nginx configuration is listening on the server name without the "www."

Can you see in the response headers of your browser wether it is Apache or Nginx that is giving you the 404 response?

• If it is Apache, you can either :
  • Remove it if you don't really need it, and make sure Nginx catches the request,
  • Or translate the Nginx configuration in apache as you suggested. I am not comfortable enough with apache configuration to translate anything from nginx though, so I won't be able to help on that one
• If it is Nginx, try and update the server_name and proxy_pass as suggested earlier and see what happens

[GeoGyro]

I removed Nginx and try to translate configuration for Apache.

In /etc/apache2/sites-avalaible/default-ssl.conf I add:

# PostgreREST
<VirtualHost *:443>

# Info
ServerAdmin webmaster@localhost
ServerName mywebsite.com
DocumentRoot /var/www/html/

# Header
Header set Content-Type "application/json"

# Reverse Proxy
ProxyPreserveHost On
ProxyPass / http://localhost:3000/
ProxyPassReverse / http://localhost:3000/

# Log
ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined

# SSL
SSLEngine On
SSLProtocol All -SSLv3 -SSLv2
SSLCipherSuite HIGH:!aNULL:!MD5:!ADH:!RC4:!DH
SSLHonorCipherOrder on
SSLCertificateFile "/etc/letsencrypt/live/mywebsite.com/fullchain.pem"
SSLCertificateKeyFile "/etc/letsencrypt/live/mywebsite.com/privkey.pem"

</VirtualHost>

Restarting Apache and service is running good. postgrest service is also running and when I try curl "http://localhost:3000/todos?select=name" from server, I get a json response. But when I try https://mywebsite.com/todos?select=name on the web I still have an error 404. I looked in Apache logs, nothing seems wrong but in /var/log/postgresql/postgresql-11-main.log I found (I translate message from french):

authenticator@my_database can't get data from client : Connection reinitialised by correpondant

Something wrong with PostgreSQL you think ?

My pg_hba.conf file looks like

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 md5

[j-ova]

I would guess the correct translation of your error message is connection reset by peer, though even then I don't see where the problem lies...

If the timestamps of the postgresql logs does match your browser attempt, it means the Apache rule does indeed redirects the traffic.
Maybe you can try and simplify your Apache conf. Some parameters may confuse the redirection or the target app (DocumentRoot, Header Content-Type and ProxyPreserveHost are my best guess).

Other than that, I don't really see, sorry.

[GeoGyro]

I changed my Apache conf file, and everything works fine:

<VirtualHost *:443>
...
# postgREST
ProxyPass /api http://localhost:3000/
ProxyPassReverse /api http://localhost:3000/
...
</VirtualHost>