I would like to have user who can read and insert data to all tables within one schema. I've executed following SQl statements:
CREATE SCHEMA ABC;
CREATE USER MyUser with PASSWORD '12345678';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ABC TO MyUser;
When I try to login with this user I am getting exception: Role 'MyUser' does not exists.... What is not correct here?
How to Define Privileges Upon Role Creation Now, we are ready to recreate the "demo_role" role with altered permissions. We can do this by specifying the permissions we want after the main create clause:
CREATE ROLE role_name WITH optional_permissions;
You can see a full list of the options by typing:
\h CREATE ROLE
We want to give this user the ability to log in, so we will type:
CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE
If we want to get to this state without specifying the "login" attribute with every role creation, we can actually use the following command instead of the "CREATE ROLE" command:
CREATE USER role_name;
The only difference between the two commands is that "CREATE USER" automatically gives the role login privileges.
here or here about PostgreSQL User Administration. Thanks
UPDATE
We want to give this user the ability to log in, so we will type:
CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE
If we check the attributes \du
demo_role | | {}
my user name was case sensitive. using small letters solves problem.
