Postgrest: Inexplicable failed token verification with Keycloak RSA
I'm following your docs and the previously referenced #1191 in trying to get a Keycloak client to generate RSA tokens for use with Postgrest and not having any luck getting auth to work.
Environment
- PostgreSQL version: 12
- PostgREST version: 7.0.1
- Operating system: Ubuntu 20.04
Description of issue
db-uri = "postgres://apiuser:cccccccccccccccc@/api"
db-schema = "public"
db-anon-role = "web_anon"
jwt-secret = "{ \"kty\":\"RSA\",\"e\":\"AQAB\",\"n\":\"generated on https://8gwifi.org/jwkconvertfunctions.jsp using public key below\" }"
role-claim-key = ".resource_access.apiclient.roles[0]"
jwt-aud = "apiclient"
public RSA key from keycloak is:
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqkRL2PimLMDxvdvY3KqwyyBGPs0kxIhDInXhGHBsKyT815V6VvrPSpd9KjUFsE+hk3qgjLzjgTIqwOc0FmC4qooZRwXDIBXS9BVolFtS7GM7sG9sZdlGbj0M9CTEKBW4KGve6k2jEE0KGhU0ZvQa9VTovG/Rh9PAiK1FndKiBTxawCGLQLnlL6ydYxK2znz8/owFFsBsRlA5otU3+en3KZvsRXDlGpmN40CHKaFhkYEh6eMktR5R7aBTVo2Wwp1ucMN/UlDzC/DqH7Sj7UKAaIVHrY1vX5ltvzPx89KbGrZsGAitgTMI0/E9jEADaEoIHXKAKOc7JHper/ntMpS9lwIDAQAB
The above configured Postgrest install is connecting to a schema with three roles besides the owner...
CREATE ROLE web_anon NOLOGIN;
GRANT web_anon TO apiuser;
CREATE ROLE web_user NOLOGIN;
GRANT USAGE ON SCHEMA public TO web_user;
GRANT web_user TO apiuser;
CREATE ROLE web_admin NOLOGIN;
GRANT USAGE, CREATE ON SCHEMA public TO web_admin;
GRANT web_admin TO apiuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO web_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT ON TABLES TO web_user;
I've got a single table without web_anon access in that schema to test auth (web_user and web_admin have select and full privs respectively on that table), and have yet to see it once in the swagger output from Postgrest while connecting with a valid token.
I've got the referenced Keycloak client set up to map those roles, and the web_admin role from that client is applied to the master realm "admin" user, this is a fresh install of Keycloak.
Here's a PGSQL log with an expired token in it...
[1031] [5f0683f9.407] [2020-07-09 03:35:06.816 UTC] [0]: LOG: execute S_6: BEGIN
[1031] [5f0683f9.407] [2020-07-09 03:35:06.816 UTC] [0]: LOG: execute S_5: select credential0_.ID as ID1_20_, credential0_.CREATED_DATE as CREATED_2_20_, credential0_.CREDENTIAL_DATA as CREDENTI3_20_, credential0_.PRIORITY as PRIORITY4_20_, credential0_.SALT as SALT5_20_, credential0_.SECRET_DATA as SECRET_D6_20_, credential0_.TYPE as TYPE7_20_, credential0_.USER_ID as USER_ID9_20_, credential0_.USER_LABEL as USER_LAB8_20_ from CREDENTIAL credential0_ where credential0_.USER_ID=$1 order by credential0_.PRIORITY
[1031] [5f0683f9.407] [2020-07-09 03:35:06.816 UTC] [0]: DETAIL: parameters: $1 = '928726d6-c9b1-48b4-aa49-5b10d4858fe5'
[1031] [5f0683f9.407] [2020-07-09 03:35:06.898 UTC] [0]: LOG: execute S_5: select credential0_.ID as ID1_20_, credential0_.CREATED_DATE as CREATED_2_20_, credential0_.CREDENTIAL_DATA as CREDENTI3_20_, credential0_.PRIORITY as PRIORITY4_20_, credential0_.SALT as SALT5_20_, credential0_.SECRET_DATA as SECRET_D6_20_, credential0_.TYPE as TYPE7_20_, credential0_.USER_ID as USER_ID9_20_, credential0_.USER_LABEL as USER_LAB8_20_ from CREDENTIAL credential0_ where credential0_.USER_ID=$1 order by credential0_.PRIORITY
[1031] [5f0683f9.407] [2020-07-09 03:35:06.898 UTC] [0]: DETAIL: parameters: $1 = '928726d6-c9b1-48b4-aa49-5b10d4858fe5'
[1031] [5f0683f9.407] [2020-07-09 03:35:06.907 UTC] [0]: LOG: execute S_8: select clientscop0_.ID as ID1_13_0_, clientscop0_.DESCRIPTION as DESCRIPT2_13_0_, clientscop0_.NAME as NAME3_13_0_, clientscop0_.PROTOCOL as PROTOCOL4_13_0_, clientscop0_.REALM_ID as REALM_ID5_13_0_ from CLIENT_SCOPE clientscop0_ where clientscop0_.ID=$1
[1031] [5f0683f9.407] [2020-07-09 03:35:06.907 UTC] [0]: DETAIL: parameters: $1 = '32e3e057-7c78-4961-9cec-8262672889db'
[1031] [5f0683f9.407] [2020-07-09 03:35:06.919 UTC] [0]: LOG: execute S_2: COMMIT
[1767] [5f069075.6e7] [2020-07-09 03:35:17.297 UTC] [0]: LOG: statement: SET client_encoding = 'UTF8';SET client_min_messages TO WARNING;
[1767] [5f069075.6e7] [2020-07-09 03:35:17.297 UTC] [0]: LOG: execute 0: BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY
[1767] [5f069075.6e7] [2020-07-09 03:35:17.298 UTC] [0]: LOG: statement: SET LOCAL search_path = 'public', 'public';SET LOCAL "role" = 'web_anon';SET LOCAL "request.jwt.claim.role" = 'web_anon';SET LOCAL "request.method" = 'GET';SET LOCAL "request.path" = '/';SET LOCAL "request.header.host" = 'postgrest';SET LOCAL "request.header.authorization" = 'bearer eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJYNUtMdlhlbXZvam1SLTh0T0FmM1NvMnZ0VGJsUHBjWVNXVk94VVVzT1lJIn0.eyJleHAiOjE1OTQyNjU3NjYsImlhdCI6MTU5NDI2NTcwNiwiYXV0aF90aW1lIjowLCJqdGkiOiIwY2Q4ODZkMi1jYWE2LTRhMjgtYTM2My05YjdlMmZiZjM4ODQiLCJpc3MiOiJodHRwczovL25nb2JveC5yb2JlcnRuY2xheXRvbi5uZXQvYXV0aGVudGljYXRpb24vYXV0aC9yZWFsbXMvbWFzdGVyIiwiYXVkIjoiYXBpY2xpZW50Iiwic3ViIjoiOTI4NzI2ZDYtYzliMS00OGI0LWFhNDktNWIxMGQ0ODU4ZmU1IiwidHlwIjoiSUQiLCJhenAiOiJhcGljbGllbnQiLCJzZXNzaW9uX3N0YXRlIjoiYjY1NjZlMjktY2U4Mi00NGUzLTg1ZTMtNGFkMDkxYTZiNWFlIiwiYWNyIjoiMSIsInJlc291cmNlX2FjY2VzcyI6eyJhcGljbGllbnQiOnsicm9sZXMiOiJbd2ViX2FkbWluXSJ9LCJtYXN0ZXItcmVhbG0iOnsicm9sZXMiOiJbdmlldy1yZWFsbSwgdmlldy1pZGVudGl0eS1wcm92aWRlcnMsIG1hbmFnZS1pZGVudGl0eS1wcm92aWRlcnMsIGltcGVyc29uYXRpb24sIGNyZWF0ZS1jbGllbnQsIG1hbmFnZS11c2VycywgcXVlcnktcmVhbG1zLCB2aWV3LWF1dGhvcml6YXRpb24sIHF1ZXJ5LWNsaWVudHMsIHF1ZXJ5LXVzZXJzLCBtYW5hZ2UtZXZlbnRzLCBtYW5hZ2UtcmVhbG0sIHZpZXctZXZlbnRzLCB2aWV3LXVzZXJzLCB2aWV3LWNsaWVudHMsIG1hbmFnZS1hdXRob3JpemF0aW9uLCBtYW5hZ2UtY2xpZW50cywgcXVlcnktZ3JvdXBzXSJ9LCJhY2NvdW50Ijp7InJvbGVzIjoiW21hbmFnZS1hY2NvdW50LCBtYW5hZ2UtYWNjb3VudC1saW5rcywgdmlldy1wcm9maWxlXSJ9fSwiZW1haWxfdmVyaWZpZWQiOmZhbHNlLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhZG1pbiJ9.b84n5lziHv0_9BtBYOaLCU43zHIp7Qm9L5o5OV3tMCs4W6JH0cvoBoBxcu6iVafoEfFtHjZDiX14_VIP6ndxqaOk9Us7ep57m2BcQAzcZprGvbNPfkgXk47oCVwgBscsS19pt8Prk0wm8S_-I9Ayzs79G1YbCX-Zb7A978Y4Qod2IGeAo1UwPwVH4JlVSER1JJpqQMpLqbKT7LAU222u8iRhbDKOx247vp12by1lNS6k2HfRG7aFpWhpi49CLl3vPY5unY2NUsrumQ6oWWgzk-Jv1-7L0CUFos3OQtEOeQ9tIicHfg47BMPhAzykuZldkWVpvrZlBXc33KrT_WkLDg';SET LOCAL "request.header.user-agent" = 'PostmanRuntime/7.26.1';SET LOCAL "request.header.accept" = '*/*';SET LOCAL "request.header.postman-token" = '3a3bec05-9654-49dd-b2b0-eb6dcb037242';SET LOCAL "request.header.accept-encoding" = 'gzip, deflate, br';SET LOCAL "request.header.referer" = 'https://ngobox.robertnclayton.net/api';
[1767] [5f069075.6e7] [2020-07-09 03:35:17.300 UTC] [0]: LOG: execute 1:
select
n.nspname as table_schema,
relname as table_name,
d.description as table_description,
(
c.relkind in ('r', 'v', 'f')
and (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8
-- The function `pg_relation_is_updateable` returns a bitmask where 8
-- corresponds to `1 << CMD_INSERT` in the PostgreSQL source code, i.e.
-- it's possible to insert into the relation.
or (exists (
select 1
from pg_trigger
where
pg_trigger.tgrelid = c.oid
and (pg_trigger.tgtype::integer & 69) = 69)
-- The trigger type `tgtype` is a bitmask where 69 corresponds to
-- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
-- in the PostgreSQL source code.
)
) as insertable
from
pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_description as d on d.objoid = c.oid and d.objsubid = 0
where
c.relkind in ('v', 'r', 'm', 'f')
and n.nspname = $1
and (
pg_has_role(c.relowner, 'USAGE')
or has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
)
order by relname
[1767] [5f069075.6e7] [2020-07-09 03:35:17.300 UTC] [0]: DETAIL: parameters: $1 = 'public'
[1767] [5f069075.6e7] [2020-07-09 03:35:17.300 UTC] [0]: LOG: execute 2:
select
description
from
pg_catalog.pg_namespace n
left join pg_catalog.pg_description d on d.objoid = n.oid
where
n.nspname = $1
[1767] [5f069075.6e7] [2020-07-09 03:35:17.300 UTC] [0]: DETAIL: parameters: $1 = 'public'
[1767] [5f069075.6e7] [2020-07-09 03:35:17.301 UTC] [0]: LOG: execute 3:
SELECT
pn.nspname as proc_schema,
p.proname as proc_name,
d.description as proc_description,
pg_get_function_arguments(p.oid) as args,
tn.nspname as rettype_schema,
coalesce(comp.relname, t.typname) as rettype_name,
p.proretset as rettype_is_setof,
t.typtype as rettype_typ,
p.provolatile
FROM pg_proc p
JOIN pg_namespace pn ON pn.oid = p.pronamespace
JOIN pg_type t ON t.oid = p.prorettype
JOIN pg_namespace tn ON tn.oid = t.typnamespace
LEFT JOIN pg_class comp ON comp.oid = t.typrelid
LEFT JOIN pg_catalog.pg_description as d on d.objoid = p.oid
WHERE pn.nspname = $1 AND has_function_privilege(p.oid, 'execute')
[1767] [5f069075.6e7] [2020-07-09 03:35:17.301 UTC] [0]: DETAIL: parameters: $1 = 'public'
[1767] [5f069075.6e7] [2020-07-09 03:35:17.308 UTC] [0]: LOG: execute 4: COMMIT
That token checks out fine on jwt.io, and the public key matches what I see for Keycloak's auto-generated RSA public key for the master realm.
{
"swagger": "2.0",
"info": {
"version": "7.0.1 (UNKNOWN)",
"title": "PostgREST API",
"description": "standard public schema"
},
"host": "ngobox.robertnclayton.net:443",
"basePath": "/",
"schemes": [
"https"
],
"consumes": [
"application/json",
"application/vnd.pgrst.object+json",
"text/csv"
],
"produces": [
"application/json",
"application/vnd.pgrst.object+json",
"text/csv"
],
"paths": {
"/": {
"get": {
"tags": [
"Introspection"
],
"summary": "OpenAPI description (this document)",
"produces": [
"application/openapi+json",
"application/json"
],
"responses": {
"200": {
"description": "OK"
}
}
}
}
},
"parameters": {
"preferParams": {
"name": "Prefer",
"description": "Preference",
"required": false,
"in": "header",
"type": "string",
"enum": [
"params=single-object"
]
},
"preferReturn": {
"name": "Prefer",
"description": "Preference",
"required": false,
"in": "header",
"type": "string",
"enum": [
"return=representation",
"return=minimal",
"return=none"
]
},
"preferCount": {
"name": "Prefer",
"description": "Preference",
"required": false,
"in": "header",
"type": "string",
"enum": [
"count=none"
]
},
"select": {
"name": "select",
"description": "Filtering Columns",
"required": false,
"in": "query",
"type": "string"
},
"on_conflict": {
"name": "on_conflict",
"description": "On Conflict",
"required": false,
"in": "query",
"type": "string"
},
"order": {
"name": "order",
"description": "Ordering",
"required": false,
"in": "query",
"type": "string"
},
"range": {
"name": "Range",
"description": "Limiting and Pagination",
"required": false,
"in": "header",
"type": "string"
},
"rangeUnit": {
"name": "Range-Unit",
"description": "Limiting and Pagination",
"required": false,
"default": "items",
"in": "header",
"type": "string"
},
"offset": {
"name": "offset",
"description": "Limiting and Pagination",
"required": false,
"in": "query",
"type": "string"
},
"limit": {
"name": "limit",
"description": "Limiting and Pagination",
"required": false,
"in": "query",
"type": "string"
}
},
"externalDocs": {
"url": "https://postgrest.org/en/v7.0/api.html",
"description": "PostgREST Documentation"
}
}
Here's the Keycloak client details:
RNCTX
All 11 comments
Here's an attempted insert after adding \"alg\":\"RS256\" and \"key_ops\":[\"verify\"] to the configuration per the docs and restarting Postgrest, "web_admin" has full privs on "test_table":
curl --location --request POST 'https://ngobox.robertnclayton.net/api/test_table' \
--header 'Authorization: bearer tokentokentoken-see-below-token-in-PGSQL-log' \
--header 'Content-Type: application/json' \
--data-raw '{
"id": "1",
"name": "neal",
"phone": 1234567890
}'
{
"hint": null,
"details": null,
"code": "42501",
"message": "permission denied for table test_table"
}
[1031] [5f0683f9.407] [2020-07-09 11:35:43.873 UTC] [0]: LOG: execute S_6: BEGIN
[1031] [5f0683f9.407] [2020-07-09 11:35:43.873 UTC] [0]: LOG: execute S_5: select credential0_.ID as ID1_20_, credential0_.CREATED_DATE as CREATED_2_20_, credential0_.CREDENTIAL_DATA as CREDENTI3_20_, credential0_.PRIORITY as PRIORITY4_20_, credential0_.SALT as SALT5_20_, credential0_.SECRET_DATA as SECRET_D6_20_, credential0_.TYPE as TYPE7_20_, credential0_.USER_ID as USER_ID9_20_, credential0_.USER_LABEL as USER_LAB8_20_ from CREDENTIAL credential0_ where credential0_.USER_ID=$1 order by credential0_.PRIORITY
[1031] [5f0683f9.407] [2020-07-09 11:35:43.873 UTC] [0]: DETAIL: parameters: $1 = '928726d6-c9b1-48b4-aa49-5b10d4858fe5'
[1031] [5f0683f9.407] [2020-07-09 11:35:43.952 UTC] [0]: LOG: execute S_5: select credential0_.ID as ID1_20_, credential0_.CREATED_DATE as CREATED_2_20_, credential0_.CREDENTIAL_DATA as CREDENTI3_20_, credential0_.PRIORITY as PRIORITY4_20_, credential0_.SALT as SALT5_20_, credential0_.SECRET_DATA as SECRET_D6_20_, credential0_.TYPE as TYPE7_20_, credential0_.USER_ID as USER_ID9_20_, credential0_.USER_LABEL as USER_LAB8_20_ from CREDENTIAL credential0_ where credential0_.USER_ID=$1 order by credential0_.PRIORITY
[1031] [5f0683f9.407] [2020-07-09 11:35:43.952 UTC] [0]: DETAIL: parameters: $1 = '928726d6-c9b1-48b4-aa49-5b10d4858fe5'
[1031] [5f0683f9.407] [2020-07-09 11:35:43.957 UTC] [0]: LOG: execute S_8: select clientscop0_.ID as ID1_13_0_, clientscop0_.DESCRIPTION as DESCRIPT2_13_0_, clientscop0_.NAME as NAME3_13_0_, clientscop0_.PROTOCOL as PROTOCOL4_13_0_, clientscop0_.REALM_ID as REALM_ID5_13_0_ from CLIENT_SCOPE clientscop0_ where clientscop0_.ID=$1
[1031] [5f0683f9.407] [2020-07-09 11:35:43.957 UTC] [0]: DETAIL: parameters: $1 = '32e3e057-7c78-4961-9cec-8262672889db'
[1031] [5f0683f9.407] [2020-07-09 11:35:43.962 UTC] [0]: LOG: execute S_2: COMMIT
[6188] [5f070128.182c] [2020-07-09 11:36:08.305 UTC] [0]: LOG: statement: SET client_encoding = 'UTF8';SET client_min_messages TO WARNING;
[6188] [5f070128.182c] [2020-07-09 11:36:08.305 UTC] [0]: LOG: execute 0: BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE
[6188] [5f070128.182c] [2020-07-09 11:36:08.306 UTC] [0]: LOG: statement: SET LOCAL search_path = 'public', 'public';SET LOCAL "role" = 'web_anon';SET LOCAL "request.jwt.claim.role" = 'web_anon';SET LOCAL "request.method" = 'POST';SET LOCAL "request.path" = '/test_table';SET LOCAL "request.header.host" = 'postgrest';SET LOCAL "request.header.content-length" = '31';SET LOCAL "request.header.authorization" = 'bearer eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJYNUtMdlhlbXZvam1SLTh0T0FmM1NvMnZ0VGJsUHBjWVNXVk94VVVzT1lJIn0.eyJleHAiOjE1OTQyOTQ2MDMsImlhdCI6MTU5NDI5NDU0MywiYXV0aF90aW1lIjowLCJqdGkiOiJiNzMwMjE0My04Y2NkLTQ1ZGUtYjczNy0zNDZmZTlmMWY5ODkiLCJpc3MiOiJodHRwczovL25nb2JveC5yb2JlcnRuY2xheXRvbi5uZXQvYXV0aGVudGljYXRpb24vYXV0aC9yZWFsbXMvbWFzdGVyIiwiYXVkIjoiYXBpY2xpZW50Iiwic3ViIjoiOTI4NzI2ZDYtYzliMS00OGI0LWFhNDktNWIxMGQ0ODU4ZmU1IiwidHlwIjoiSUQiLCJhenAiOiJhcGljbGllbnQiLCJzZXNzaW9uX3N0YXRlIjoiMmQ1ZDU1ZmMtNDdlMi00YWFhLTlkM2YtY2NhMWYxODFmZDVjIiwiYWNyIjoiMSIsInJlc291cmNlX2FjY2VzcyI6eyJhcGljbGllbnQiOnsicm9sZXMiOiJbd2ViX2FkbWluXSJ9LCJtYXN0ZXItcmVhbG0iOnsicm9sZXMiOiJbdmlldy1yZWFsbSwgdmlldy1pZGVudGl0eS1wcm92aWRlcnMsIG1hbmFnZS1pZGVudGl0eS1wcm92aWRlcnMsIGltcGVyc29uYXRpb24sIGNyZWF0ZS1jbGllbnQsIG1hbmFnZS11c2VycywgcXVlcnktcmVhbG1zLCB2aWV3LWF1dGhvcml6YXRpb24sIHF1ZXJ5LWNsaWVudHMsIHF1ZXJ5LXVzZXJzLCBtYW5hZ2UtZXZlbnRzLCBtYW5hZ2UtcmVhbG0sIHZpZXctZXZlbnRzLCB2aWV3LXVzZXJzLCB2aWV3LWNsaWVudHMsIG1hbmFnZS1hdXRob3JpemF0aW9uLCBtYW5hZ2UtY2xpZW50cywgcXVlcnktZ3JvdXBzXSJ9LCJhY2NvdW50Ijp7InJvbGVzIjoiW21hbmFnZS1hY2NvdW50LCBtYW5hZ2UtYWNjb3VudC1saW5rcywgdmlldy1wcm9maWxlXSJ9fSwiZW1haWxfdmVyaWZpZWQiOmZhbHNlLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhZG1pbiJ9.S_jVzLrfLzsh48lbysshlKTnyNJb5yMG6QfG0ddkX1v9kkrOpFZ4kFykHf9e59eYpeVILFNLaMeG6qPCcIe3dxp-Hf2IfjRcoy7m2PXNN0BRp-MPuuP1G0JB9iITkdKt481lqfNeAa526Y2XwOibgncx8SkhAQEhkzz5CM-m851NYeBGsJbA6MoLqUuiIpT6GE2CtOS8jwP_UnKVjEXXI0TW3TsY8XgtGJ8-nQKb5EuIdDaUXOdQWX_FqCK4ylWmpqxMd1kTWEs3DF16OiVdYWIyHXXDB75MoxKHxg23J9jl5nVDOa_XSh28ucgToTsPLXRnEd-bfl9MRZfhQyU9HA';SET LOCAL "request.header.content-type" = 'application/x-www-form-urlencoded';SET LOCAL "request.header.user-agent" = 'PostmanRuntime/7.26.1';SET LOCAL "request.header.accept" = '*/*';SET LOCAL "request.header.postman-token" = 'a0fdc7cf-650c-48a3-b2fc-1574d0804275';SET LOCAL "request.header.accept-encoding" = 'gzip, deflate, br';
[6188] [5f070128.182c] [2020-07-09 11:36:08.308 UTC] [0]: LOG: execute 1:
WITH
pgrst_source AS (WITH pgrst_payload AS (SELECT $1::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload) INSERT INTO "public"."test_table" ("id", "name", "phone") SELECT "id", "name", "phone" FROM json_populate_recordset (null:: "public"."test_table" , (SELECT val FROM pgrst_body)) _ RETURNING "public"."test_table".*)
SELECT
'' AS total_result_set,
pg_catalog.count(_postgrest_t) AS page_total,
CASE WHEN pg_catalog.count(_postgrest_t) = 1 THEN coalesce((
WITH data AS (SELECT row_to_json(_) AS row FROM pgrst_source AS _ LIMIT 1)
SELECT array_agg(json_data.key || '=' || coalesce('eq.' || json_data.value, 'is.null'))
FROM data CROSS JOIN json_each_text(data.row) AS json_data
WHERE json_data.key IN ('id')
), array[]::text[]) ELSE array[]::text[] END AS header,
'' AS body,
coalesce(nullif(current_setting('response.headers', true), ''), '[]') AS response_headers
FROM (SELECT * FROM pgrst_source) _postgrest_t
[6188] [5f070128.182c] [2020-07-09 11:36:08.308 UTC] [0]: DETAIL: parameters: $1 = '{"phone":"1234567890","name":"neal","id":"1"}'
[6188] [5f070128.182c] [2020-07-09 11:36:08.308 UTC] [0]: ERROR: permission denied for table test_table
[6188] [5f070128.182c] [2020-07-09 11:36:08.308 UTC] [0]: STATEMENT:
WITH
pgrst_source AS (WITH pgrst_payload AS (SELECT $1::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload) INSERT INTO "public"."test_table" ("id", "name", "phone") SELECT "id", "name", "phone" FROM json_populate_recordset (null:: "public"."test_table" , (SELECT val FROM pgrst_body)) _ RETURNING "public"."test_table".*)
SELECT
'' AS total_result_set,
pg_catalog.count(_postgrest_t) AS page_total,
CASE WHEN pg_catalog.count(_postgrest_t) = 1 THEN coalesce((
WITH data AS (SELECT row_to_json(_) AS row FROM pgrst_source AS _ LIMIT 1)
SELECT array_agg(json_data.key || '=' || coalesce('eq.' || json_data.value, 'is.null'))
FROM data CROSS JOIN json_each_text(data.row) AS json_data
WHERE json_data.key IN ('id')
), array[]::text[]) ELSE array[]::text[] END AS header,
'' AS body,
coalesce(nullif(current_setting('response.headers', true), ''), '[]') AS response_headers
FROM (SELECT * FROM pgrst_source) _postgrest_t
[6188] [5f070128.182c] [2020-07-09 11:36:08.308 UTC] [0]: LOG: execute 2: ABORT
Role and privs output:
api=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------------------------
admin | Superuser, Create role, Create DB | {}
apiuser | No inheritance +| {web_anon,web_user,web_admin}
| Password valid until infinity |
kcuser | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
web_admin | Cannot login | {}
web_anon | Cannot login | {}
web_user | Cannot login | {}
api=# \z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+----------+----------------------+-------------------+----------
public | test_table | table | admin=arwdDxt/admin +| |
| | | web_user=arw/admin +| |
| | | web_admin=arwd/admin | |
public | test_table_id_seq | sequence | | |
(2 rows)
FWIW "admin" is my phpPgAdmin user, I'm wondering if there's something I'm missing here in regard to PostgreSQL defaults, since "admin" was used to create the db in question and owns it?
RNCTX
on 9 Jul 2020
Hey @RNCTX. I haven't understood your issue fully yet, but in the meantime I recommend you check this tutorial for PostgREST + Keycloak: https://www.mathieupassenaud.fr/codeless_backend.
Let me know if that helps.
steve-chavez
on 9 Jul 2020
Thanks Steve, I realize this is complexity outside of your wheelhouse. I did find that guide before posting here, and am able to auth by just sharing the public key directly (HS256 mode) but that's obviously not preferable ;).
In digging around on StackOverflow I did find that there's an endpoint in Keycloak for JWK public key configuration, which returns:
{
"kid": "X5KLvXemvojmR-8tOAf3So2vtTblPpcYSWVOxUUsOYI",
"kty": "RSA",
"alg": "RS256",
"use": "sig",
"n": "qkRL2PimLMDxvdvY3KqwyyBGP...,
"e": "AQAB",
"x5c": [ "MIICmzCCAYMCBgFzL3YG/zANBgkqhkiG...TATmE="
],
"x5t": "j7T3rUdCc1GBfV7cgxXd81KTy2w",
"x5t#S256": "Q6GjoRYEq3pe-7SBJHl--q4yBB7-DAbR8qZfdJGuUxs"
}
gonna go try variations of that with your docs examples and see what I get.
If another Keycloak issue pops up the URL to that endpoint as of Keycloak v10 is:
http://host:port/auth/realms/(realm-name)/protocol/openid-connect/certs
In the default install an RS256 key is the only one that Keycloak automatically creates, so it will be the only one returned on a fresh setup.
RNCTX
on 9 Jul 2020
Update to this:
After looking at alternative open source auth servers, I came across this one, Glewlwyd which seems very nice for small setups since it's written in C, and therefore not so demanding in terms of memory usage versus Keycloak running in a JVM.
As I was searching its issues for a recommended Nginx setup, I came across this issue in which the user said he had to explicitly declare the Authorization header in his Nginx configuration because Nginx would ignore it if it wasn't part of the initial request.
So lets retrace a typical auth flow in which all of the services are on one server being served by the same Nginx instance as a proxy to all of them...
- User hits Keycloak or other auth backend (my.api.server/auth) and gets a token. Because this is the user's first request and the user is authenticating a local account with an email and password or some sort of third party (AD, LDAP), there is no
Authorizationheader, all of the user's credentials are in the body of the initial auth request. - User's client gets a token back from Keycloak.
- User's client takes the id token and passes it along to Postgrest on the same machine (my.api.server/api), but the token never got there, not even once.
What if Nginx doesn't know that /auth and /api are completely different things? I suspect it does not infer that just because they proxy to different backend listeners. What if, as far as Nginx knows or cares, one request to /auth and another to /api on the same domain are all the same, just going to different subfolders which connect to different proxy backends, and it never sent the token? Because it ignores headers not present in the initial request as the linked issue suggests?
Will investigate more.
RNCTX
on 12 Jul 2020
Wanted to recommend that you try using the JWK in a file instead of setting it inline. Check https://github.com/PostgREST/postgrest/issues/1192#issuecomment-446632584.
As I was searching its issues for a recommended Nginx setup, I came across this issue in which the user said he had to explicitly declare the Authorization header in his Nginx configuration because Nginx would ignore it if it wasn't part of the initial request.
Didn't know that one. I've never set the proxy_pass_header Authorization when working with Auth and I got not issues.
steve-chavez
on 13 Jul 2020
👍1
No luck.
Here, this is as simple as I can think of...
- manually generated token on jwt.io
- postgrest config as follows (not redacted, the secret is really asdf1234 over and over again)
db-uri = "postgres://apiuser:redactredactredact@/api"
db-schema = "public"
db-anon-role = "web_anon"
jwt-secret = "asdf1234asdf1234asdf1234asdf1234asdf1234asdf1234asdf1234asdf1234"
- contents of token:
{
"username": "admin",
"type": "access_token",
"iat": 1594709240,
"expires_in": 3600,
"exp": 1594712840,
"nbf": 1594709240,
"scope": "openid",
"role": "web_admin"
}
- Sending directly to Postgrest via curl on the CLI on the host it's running on, bypassing Nginx entirely:
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.432 UTC] [0]: LOG: statement: SET client_encoding = 'UTF8';SET client_min_messages TO WARNING;
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.433 UTC] [0]: LOG: execute 0: BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.433 UTC] [0]: LOG: statement: SET LOCAL search_path = 'public', 'public';SET LOCAL "role" = 'web_anon';SET LOCAL "request.jwt.claim.role" = 'web_anon';SET LOCAL "request.method" = 'PATCH';SET LOCAL "request.path" = '/test_table';SET LOCAL "request.header.host" = '127.0.0.1:3000';SET LOCAL "request.header.user-agent" = 'curl/7.68.0';SET LOCAL "request.header.accept" = '*/*';SET LOCAL "request.header.authorization" = 'bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VybmFtZSI6ImFkbWluIiwidHlwZSI6ImFjY2Vzc190b2tlbiIsImlhdCI6MTU5NDcwOTI0MCwiZXhwaXJlc19pbiI6MzYwMCwiZXhwIjoxNTk0NzEyODQwLCJuYmYiOjE1OTQ3MDkyNDAsInNjb3BlIjoib3BlbmlkIiwicm9sZSI6IndlYl9hZG1pbiJ9.GftS4YwOZUnd3oVWIn93Vxm2OeoQ3k6RLGlbX0cdH4Q';SET LOCAL "request.header.content-type" = 'application/json';SET LOCAL "request.header.content-length" = '41';
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.436 UTC] [0]: LOG: execute 1:
WITH
pgrst_source AS (WITH pgrst_payload AS (SELECT $1::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload) UPDATE "public"."test_table" SET "id" = _."id", "name" = _."name", "phone" = _."phone" FROM (SELECT * FROM json_populate_recordset (null:: "public"."test_table" , (SELECT val FROM pgrst_body))) _ RETURNING 1)
SELECT
'' AS total_result_set,
pg_catalog.count(_postgrest_t) AS page_total,
array[]::text[] AS header,
'' AS body,
coalesce(nullif(current_setting('response.headers', true), ''), '[]') AS response_headers
FROM (SELECT * FROM pgrst_source) _postgrest_t
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.436 UTC] [0]: DETAIL: parameters: $1 = '{"id":1,"name":"neal","phone":1234567890}'
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.436 UTC] [0]: ERROR: permission denied for table test_table
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.436 UTC] [0]: STATEMENT:
WITH
pgrst_source AS (WITH pgrst_payload AS (SELECT $1::json AS json_data), pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload) UPDATE "public"."test_table" SET "id" = _."id", "name" = _."name", "phone" = _."phone" FROM (SELECT * FROM json_populate_recordset (null:: "public"."test_table" , (SELECT val FROM pgrst_body))) _ RETURNING 1)
SELECT
'' AS total_result_set,
pg_catalog.count(_postgrest_t) AS page_total,
array[]::text[] AS header,
'' AS body,
coalesce(nullif(current_setting('response.headers', true), ''), '[]') AS response_headers
FROM (SELECT * FROM pgrst_source) _postgrest_t
[36906] [5f0d553b.902a] [2020-07-14 06:48:27.437 UTC] [0]: LOG: execute 2: ABORT
api=# SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='test_table';
grantee | privilege_type
-----------+----------------
admin | INSERT
admin | SELECT
admin | UPDATE
admin | DELETE
admin | TRUNCATE
admin | REFERENCES
admin | TRIGGER
web_user | INSERT
web_user | SELECT
web_user | UPDATE
web_admin | INSERT
web_admin | SELECT
web_admin | UPDATE
web_admin | DELETE
web_admin | TRUNCATE
web_admin | REFERENCES
web_admin | TRIGGER
api=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-------------------------------
admin | Superuser, Create role, Create DB | {}
apiuser | No inheritance +| {web_anon,web_user,web_admin}
| Password valid until infinity |
authdbuser | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
web_admin | Cannot login | {}
web_anon | Cannot login | {}
web_user | Cannot login | {}
RNCTX
on 14 Jul 2020
Try your request with a capital B in Bearer in the Authorization header.
wolfgangwalther
on 14 Jul 2020
RNCTX
on 14 Jul 2020
Not sure what you want to say with the image.
Your curl request does not pass the token properly:
curl --location --request POST 'https://ngobox.robertnclayton.net/api/test_table' \
--header 'Authorization: bearer tokentokentoken-see-below-token-in-PGSQL-log' \
--header 'Content-Type: application/json' \
--data-raw '{
"id": "1",
"name": "neal",
"phone": 1234567890
}'
has to be
curl --location --request POST 'https://ngobox.robertnclayton.net/api/test_table' \
--header 'Authorization: Bearer tokentokentoken-see-below-token-in-PGSQL-log' \
--header 'Content-Type: application/json' \
--data-raw '{
"id": "1",
"name": "neal",
"phone": 1234567890
}'
Note the capital B in the 2nd request.
This is also indicated by your Postgres log:
SET LOCAL "request.jwt.claim.role" = 'web_anon';
[...]
SET LOCAL "request.header.authorization" = 'bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VybmFtZSI6ImFkbWluIiwidHlwZSI6ImFjY2Vzc190b2tlbiIsImlhdCI6MTU5NDcwOTI0MCwiZXhwaXJlc19pbiI6MzYwMCwiZXhwIjoxNTk0NzEyODQwLCJuYmYiOjE1OTQ3MDkyNDAsInNjb3BlIjoib3BlbmlkIiwicm9sZSI6IndlYl9hZG1pbiJ9.GftS4YwOZUnd3oVWIn93Vxm2OeoQ3k6RLGlbX0cdH4Q';
The header has bearer instead of Bearer. That's why PostgREST does not recognize the token at all and sets the role to the anonymous user.
wolfgangwalther
on 14 Jul 2020
Solved, but it was a combination of the above.
Nginx's built-in cache was regurgitating bad requests to me. Disabled it with
expires -1;Bearer / bearer was part of it, thanks.
Because Postgrest wants the root URL rewritten depending on what it's doing... sometimes, rewrite rules to serve it from a subdirectory named after the database it's connecting to were necessary.
# Postgrest
location ^~ /api {
rewrite ^\/api(.*) $1 break;
default_type application/json;
proxy_set_header Connection "";
proxy_http_version 1.1;
proxy_pass http://postgrest;
expires -1;
}
The above tests okay on swagger / post / patch.
https://site.url/api/ returns swagger (authenticated if the token is present)
https://site.url/api/table_name can post and patch.
Thanks for reading all of the walls of text above guys, took awhile but it's working.
FWIW I never made any progress with Keycloak's RSA tokens (and I remember trying Bearer vs bearer with it too). I switched to a simpler server using Oauth2 and a SHA256 secret encoded token.
RNCTX
on 14 Jul 2020
👍2
Was this page helpful?
0 / 5 - 0 ratings
Related issues
a-mckinley
路
3Comments
skinkie
路
4Comments
daurnimator
路
5Comments
qjhart
路
4Comments
rvernica
路
4Comments
Most helpful comment
Solved, but it was a combination of the above.
Nginx's built-in cache was regurgitating bad requests to me. Disabled it with
expires -1;Bearer / bearer was part of it, thanks.
Because Postgrest wants the root URL rewritten depending on what it's doing... sometimes, rewrite rules to serve it from a subdirectory named after the database it's connecting to were necessary.
The above tests okay on swagger / post / patch.
https://site.url/api/ returns swagger (authenticated if the token is present)
https://site.url/api/table_name can post and patch.
Thanks for reading all of the walls of text above guys, took awhile but it's working.
FWIW I never made any progress with Keycloak's RSA tokens (and I remember trying Bearer vs bearer with it too). I switched to a simpler server using Oauth2 and a SHA256 secret encoded token.