Is there anyone who can help me with creating a user with pgAdmin where I want to give them the privileges to read and write on their data but only read on the data added by other users?
I tried
ALTER DEFAULT PRIVILEGES FOR USER "User A"
IN SCHEMA Zoning
GRANT SELECT ON TABLES TO "User B";
and
To grant a user permission to add data to a particular schema and edit and view only the data they have added, you can follow these steps:
- Create a new user with a password using the following SQL command:
CREATE USER new_user WITH PASSWORD 'your_password';Replacenew_userwith the username you want to create andyour_passwordwith the password you want to use. - Grant the new user permission to connect to the database using the following SQL command:
GRANT CONNECT ON DATABASE your_database TO new_user;Replaceyour_databasewith the name of the database you want to grant access to. - Grant the new user permission to create tables, sequences, and other objects in the schema using the following SQL command:
GRANT USAGE, CREATE ON SCHEMA your_schema TO new_user;Replaceyour_schemawith the name of the schema you want to grant access to. - Grant the new user permission to insert data into tables in the schema using the following SQL command:
GRANT INSERT ON ALL TABLES IN SCHEMA your_schema TO new_user;Replaceyour_schemawith the name of the schema you want to grant access to. - Create a new role for the user using the following SQL command:
CREATE ROLE new_user_role; - Grant the new role permission to select, update, and delete data in tables where the user is listed as the owner using the following SQL command:
GRANT SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO new_user_role; - Assign the new role to the new user using the following SQL command:
GRANT new_user_role TO new_user;
After executing these SQL commands, the new_user should have permission to add data to the specified schema, and they will have edit and view access only to the data they have added.
When the user adds new data to a table in the schema, they will automatically become the owner of that data. As the owner of the data, the user will be able to edit and delete that data, but they will not be able to edit or delete data added by other users.
Note that granting these permissions to a user gives them a significant amount of control over the data in the database. Make sure to only grant these permissions to users who need them and who you trust to handle the data responsibly. this help from ChatGPT. But couldn't do it at all. I want to do this all by superuser.