How to Implement Role Based Access Control (RBAC) and use with Row Level Security (RLS)

[MightyPhoenix]

Follow these steps -

1. Create a role by going to Database > Roles and click on + Add role

2. Give your role a name, for example - dash_admin and click Create Role

3. Now go to SQL Editor and click on + New query and run these queries

grant dash_admin to authenticator;

grant usage on schema public to dash_admin;

grant all privileges on all tables in schema public to dash_admin;

4. Now assign this role to your user in the user table using this query with uid -

UPDATE auth.users SET role = 'dash_admin' WHERE id = '8x7587xx-88x5-4xxx-x349-9x82xxxxxx41';

5. Finally setup RLS for any table in public schema for any CRUD Operation like this 👇

6. To test it out, log out and then log in again to refresh JWT and see if it works.

• Took Inspiration from here

Github Discusion Answer
Stackoverflow SQL queries

• Feel free to give suggestions or correct me if I went wrong somewhere. Thanks, I hope this helps.

[Tenuka22]

Ive enable my user the storage access but this role dev indicates a error

`Log ID
dc53353c-f642-4fae-85eb-ba187b8ec7a5

Log Timestamp (UTC)
2024-05-08T15:51:38.363Z

Log Event Message
pdbdevrjuzrdprjxtdux | POST | 500 | 10.103.62.131 | 880a9cba53f43632-FRA | /object/products/full-moon-forest-night-dark-starry-sky-5k-8k-7952x5304-1684.jpg | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36 OPR/109.0.0.0

Log Metadata
[
{
"context": [
{
"host": "ip-10-103-37-78.ap-southeast-1.compute.internal",
"pid": 1,
"type": "request"
}
],
"err": [],
"error": [
{
"message": "\n SELECT\n set_config('role', $1, true),\n set_config('request.jwt.claim.role', $2, true),\n set_config('request.jwt', $3, true),\n set_config('request.jwt.claim.sub', $4, true),\n set_config('request.jwt.claims', $5, true),\n set_config('request.headers', $6, true),\n set_config('request.method', $7, true),\n set_config('request.path', $8, true);\n - permission denied to set role "dev"",
"name": "error",
"raw": "{"length":101,"name":"error","severity":"ERROR","code":"42501","file":"guc.c","line":"11978","routine":"call_string_check_hook"}",
"stack": "error: \n SELECT\n set_config('role', $1, true),\n set_config('request.jwt.claim.role', $2, true),\n set_config('request.jwt', $3, true),\n set_config('request.jwt.claim.sub', $4, true),\n set_config('request.jwt.claims', $5, true),\n set_config('request.headers', $6, true),\n set_config('request.method', $7, true),\n set_config('request.path', $8, true);\n - permission denied to set role "dev"\n at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:287:98)\n at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:126:29)\n at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:39:38)\n at TLSSocket. (/app/node_modules/pg-protocol/dist/index.js:11:42)\n at TLSSocket.emit (node:events:518:28)\n at addChunk (node:internal/streams/readable:559:12)\n at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)\n at Readable.push (node:internal/streams/readable:390:5)\n at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)\n at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17)"
}
],
"event": null,
"job": null,
"jodId": null,
"level": "error",
"metadata": null,
"objectPath": null,
"owner": "d9f531af-8bb2-430c-b5a9-a84e5c5817e5",
"payload": null,
"project": "pdbdevrjuzrdprjxtdux",
"rawError": [],
"req": [
{
"headers": [
{
"accept": "/",
"cache_control": null,
"cf_connecting_ip": "51.75.77.237",
"cf_ipcountry": null,
"cf_ray": "880a9cba53f43632-FRA",
"content_disposition": null,
"content_length": "9002486",
"content_range": null,
"content_type": "multipart/form-data; boundary=----WebKitFormBoundary2MBIhA7tzEz8xT5s",
"date": null,
"expires": null,
"host": "storage-api-lb-ap-southeast-1-ext.storage.supabase.com",
"if_modified_since": null,
"if_none_match": null,
"location": null,
"range": null,
"referer": "http://127.0.0.1:3000/",
"transfer_encoding": null,
"tus_resumable": null,
"upload_length": null,
"upload_metadata": null,
"upload_offset": null,
"user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36 OPR/109.0.0.0",
"x_client_info": "supabase-ssr/0.3.0",
"x_client_trace_id": null,
"x_forwarded_host": "pdbdevrjuzrdprjxtdux.supabase.co",
"x_forwarded_port": "443",
"x_forwarded_proto": "https",
"x_real_ip": null,
"x_upsert": "false"
}
],
"hostname": "storage-api-lb-ap-southeast-1-ext.storage.supabase.com",
"method": "POST",
"region": "ap-southeast-1",
"remoteAddress": "10.103.62.131",
"remotePort": 12088,
"traceId": "880a9cba53f43632-FRA",
"url": "/object/products/full-moon-forest-night-dark-starry-sky-5k-8k-7952x5304-1684.jpg"
}
],
"reqId": "880a9cba53f43632-FRA",
"res": [
{
"headers": [
{
"cache_control": null,
"content_disposition": null,
"content_length": "73",
"content_range": null,
"content_type": "application/json; charset=utf-8",
"etag": null,
"expires": null,
"last_modified": null,
"location": null,
"tus_resumable": null,
"upload_length": null,
"upload_metadata": null,
"upload_offset": null,
"x_transformations": null
}
],
"statusCode": 500
}
],
"responseTime": 292.6079760193825,
"tenantId": "pdbdevrjuzrdprjxtdux"
}
]
`