0

I'm trying to set up PostgreSQL for local use on my desktop and I ran into a weird problem. I can't seem to find a simple way to make the database cluster (which is just a directory) only accessible to the postgres user and completely accessible to the same. In other words, I want postgres (the user) to have full access to the directory, but everyone else to just be able to read what's in it (so that files can be copied).

I've created the user 'postgres', removed them from all groups and denied write permission to the 'Users' group. However, when I try to initialize the database, the postgres user seems to be unable to write to the directory - once I remove write denial from 'Users', it works fine.

I know I could do something like deny write permissions to my default user (myself essentially), but this user belongs to 'Administrators' and not 'Users'. Since apparently all users belong to 'Users' implicitly and denial takes precedence, I'm afraid I'd be denying myself, the only administrator, capability to modify this directory at any point in the future.

I've seen some other instructions that involve Group Policy etc. but they don't seem to be specific enough and the process feels way too complicated, which makes me think I'm misunderstanding NTFS/Windows permissions entirely. This feeling is further compounded by the fact that I can think of how to do this on Linux in 5 minutes and simply. I'm not even sure if it's possible at this point.

How would I give a specific user sole access on a directory and nobody else?

edit: I think I can boil down this question to a simpler one: Is there any way of disallowing anything to a user that belongs to the Administrator group? And if you did that, would you be able to restore permissions to what they used to be?

  • The short answer to both questions in bold is "yes". Concerning the file system, Administrators will always have access to drive root folders (unless restricted by group policies), so resetting all permissions is always an option. – Dmitry Grigoryev Jul 9 '15 at 20:18
1

You don't need to deny permissions for this, nor to remove postgres from Users group. This group should have read access to the directory, but no write access. Note that it's not the same as denial, you just need to make sure your cluster directory doesn't inherit any permissions from its parent, then manually assign it read permissions for Users group. Then assign it write permissions for postgres user only, and you're all set.

Edit: if the above explanation is not clear enough, here's a screenshot and an article.

  • I understood what you meant and I changed the permissions, but my user is still capable of performing all actions because it belongs to the Administrator group I think. I tried removing permissions from the Authenticated Users group, but the effect is the same, it seems Allow permissions also take precedence over a lack of permissions. edit: Also tried removing permissions from my own user specifically, same effect - didn't expect much anyway. – mechalynx Jul 9 '15 at 19:25
  • How about not using Administrator's account as your regular login? Administrators are meant to have access to everything in the system. You can still perform administrative tasks by entering Administrator password when necessary. ------- Anyway, you should be able to remove permissions even from an elevated user. You should post screenshots of your permission dialogs, otherwise your problem is hard to diagnose. – Dmitry Grigoryev Jul 9 '15 at 20:13
  • I would gladly not use an administrator account as my regular login and that's what I thought would happen when I first installed windows on this machine, but now it's far too late. Here's a screenshot: imgur.com/vmiTBX9 - I did the same for 'Users' and both have no effect besides explorer complaining that I can't modify permissions afterwards, which it still changes anyway. It seems that specificity isn't taking effect. Thanks for the effort, but I think I'll just give up on this and stick to regular backups to maintain integrity. – mechalynx Jul 9 '15 at 21:56
  • From what I see on the screenshot, both Administrators and Users have "Full Control" over the folder and Authenticated users have "Modify" permissions. Have you tried to remove these permissions (and perhaps to add a "Read" permission instead)? – Dmitry Grigoryev Jul 9 '15 at 22:29
  • I've tried reducing permissions to 'Read' on both 'Users' and 'Authenticated Users', but not on 'Administrators' and I haven't reduced the permissions simultaneously on the former groups. I avoided changing 'Administrator' permissions so I don't accidentally lock myself out and I didn't change permissions simultaneously because I'm looking for a simple solution if possible and because I was expecting specificity to take precedence, hence I expected either one of these would only be required or nothing would work. – mechalynx Jul 9 '15 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.