2

I have created a new database and I have some existing users who I want to grant full permission to access the database.

So to keep it simple I simply want to be able to connect to the database using a user name so I run the following:

GRANT CONNECT ON DATABASE my_database TO my_user;

This doesn't work I get a failed to create a connection when trying to connect, I have been into the database in pgadmin, i've given full access to everything and even made the user name a super user and still it doesn't work.

I am using a reporting tool to test the connection for the user account and all it is doing is trying to connect and it fails everytime with my users. I have created databases for them before and it's been fine but this time it's not working. I can access it through the main account and existing super users can access it but for some reason the none superusers can't access even when you grant them permissions.

GRANT CONNECT ON DATABASE my_database TO my_user;
GRANT USAGE ON SCHEMA my_schema TO my_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_schema  TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA my_schema TO my_user;

It looks like there is something needed for the user account, I have the users I want setup so they have "can login" privileges.

What am I doing wrong.

C-Sharpie
  • 195
  • 10
  • have you configured the `pg_hba.conf` file for the new user? https://stackoverflow.com/a/49349393/2275388 – Jim Jones Feb 08 '21 at 11:56
  • I have setup this file before for the listening but not specifically for the users. Is that what I need to do in order to get the GRANT and permissions working. Why do some accounts already work the desired way? – C-Sharpie Feb 08 '21 at 12:17
  • 1
    The pg_hba is sort of a 'firewall' for the database, which means that it works before the user ever connecting to the postgres instance and therefore before all grants you have configured. If there is a certain ip range that is accepted for `all all` (all users and all databases) it might explain why some users do have access do have access by default. Does the answer I posted in my last comment helps you in any way? – Jim Jones Feb 08 '21 at 12:24
  • If possible, you can add the permissions you configured in your pg_hba to the question and we can take a look :) – Jim Jones Feb 08 '21 at 12:31
  • 1
    Cheers, I have to unfortunate issue of not having direct access to the server at this time but I will test the theory on my machine tonight. As a silly work around for now I just created the user a new account and they had access. Not sure why I couldn't give an existing user access, but creating a new user gives access. – C-Sharpie Feb 08 '21 at 13:46
  • 1
    That's indeed a mystery :) If new users directly have access to the database and there are no previously rules to deny access to a certain user, I just cannot explain this behaviour. It is always a combination of 1) ip address 2) database and 3) user. These 3 things need to be configured in the pg_hba.conf. In case this user needs to execute functions, make sure it has `GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA your_schema TO your_user;` - or constrain it to certain functions. – Jim Jones Feb 08 '21 at 13:56
  • "failed to create a connection" does not seem to be PostgreSQL error message. Please quote the precise error message. – jjanes Feb 08 '21 at 17:08
  • The error message "failed to create a connection" was my external test in an application to establish a connection to the database. The error the user was getting was ERROR: permission denied for table table_name SQL state: 42501. – C-Sharpie Feb 09 '21 at 14:16
  • I have created them a new account and granted them permissions which seems to be working fine. I'm just perplexed by how an existing user who had access to several other databases couldn't access this new one. – C-Sharpie Feb 09 '21 at 14:18

0 Answers0