Query hierarchical file permissions

I have a table representing some files with their paths and another table with file sharings by user and files. Below is Table **files**, simplified. Example: - I have a directory A (path /A) containing some files. - Directory B is inside A and its path is /A/B and it contains some files. - Files C (path /A/B/C) and D (path /A/B/D) are inside B. id | path -|- 1 | /A 2 | /A/B 3 | /A/B/C 4 | /A/B/D Below is table **file_sharings**, simplified. If a user has a file sharing with path='/A' and readable=true, it means that he can read every files with a path starting with '/A' Now I have that in this file (it's only an example, it can be more complex): id|user_id|file_id|path|readable -|-|-|-|- 1 | 1 | 1 | '/A' | true 2 | 1 | 2 | '/A/B' | false 3 | 1 | 4 | '/A/B/D' | true So, it means that user 1 can see everything in the directory A, but he can't see directory B and file C but he can see file D (the same way we can have with google drive for instance) What I want is a query to have all files/directories that user 1 can see. For the moment I have something like this:

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=true)
AND (path NOT LIKE ANY (SELECT fs.path || '%'
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=false)
);

but it returns only: /A If I do:

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=true)
AND (path NOT IN (SELECT fs.path
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=false)
);

... it returns:

/A
/A/B/C
/A/B/D

I don't know how to have all files that user 1 can read except the ones he can't read. Here I must have /A and /A/B/D, but in reality the tree files can be more complex but the tenet is the same that in this simple example. It's easy in the real life but I'm stuck to create the good request :D Here a [dbfiddle](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=e37c9d15a577e9e38816af01b9950a89) When I have no row in the DB, it means the user has no readable rights. When I have a row with readable=true, it means the user has a readable right on the file and the children (the files in the repository). When I have a row with readable=false, it means that the file and its children are specifically forbidden to the user. I do this way because I don't want to create billion rows when I share a root directory to another user. file_id is a foreign key used only to recalculate the file_sharings path when I move a file/repository into another one. So I can have a readable right on a file whereas it is not explicitly in the DB Here a picture of the fiddle example for user1: With this request:

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=true)
AND (path NOT LIKE ANY (SELECT fs.path || '%'
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=false)
);

It returns A1, B2 and C3. I should also have C2. With the second request:

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=true)
AND (path NOT IN (SELECT fs.path
    FROM file_sharings fs
    WHERE fs.user_id='1'
    AND fs.readable=false)
);

It returns A1, B2, C3, C2 and C1. I should not have C1.