I’ve created a PostgreSQL (model 11.5) database and I wish to create two various kinds of primary roles (
readwrite) after which assign these roles to people (
user2, and many others.).
I’ve been studying the PostgreSQL documentation and following this text for instance: https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/
Nonetheless, I’ve not been capable of get the permissions set accurately. Mainly, I need the position
readwrite to have the ability to create any schema and/or tables they need inside this database, and I need the position
readonly to have the ability to
SELECT from any of the schemas and/or tables on this database. Nonetheless, following my instance under,
user2 can create a desk, however
user1 doesn’t have entry to that desk.
I’ve created the next script that I can run because the grasp person for the database to create the database, schema, and roles.
Command to run SQL script:
psql -h <INSERT HOST HERE> -U postgres -W -f create_db.sql
Contents of create_db.sql
-- drop the present database DROP DATABASE IF EXISTS new_db; -- create the database CREATE DATABASE new_db; -- hook up with the database c new_db -- create the brand new schema CREATE SCHEMA new_schema; -- revoke privileges from 'public' position REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE ALL ON DATABASE new_db FROM PUBLIC; -- create readonly position DROP ROLE IF EXISTS readonly; CREATE ROLE readonly; GRANT CONNECT ON DATABASE new_db TO readonly; GRANT USAGE ON SCHEMA new_schema TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA new_schema TO readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA new_schema GRANT SELECT ON TABLES TO readonly; -- create learn/write position DROP ROLE IF EXISTS readwrite; CREATE ROLE readwrite; GRANT CONNECT, CREATE ON DATABASE new_db TO readwrite; GRANT USAGE, CREATE ON SCHEMA new_schema TO readwrite; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA new_schema TO readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA new_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite; GRANT USAGE ON ALL SEQUENCES IN SCHEMA new_schema TO readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA new_schema GRANT USAGE ON SEQUENCES TO readwrite; -- create customers DROP USER IF EXISTS user1; DROP USER IF EXISTS user2; CREATE USER user1 WITH PASSWORD 'password'; CREATE USER user2 WITH PASSWORD 'password'; -- grant privileges to customers GRANT readonly TO user1; GRANT readwrite TO user2;
After the script efficiently runs, I can run the next script as
user2 to create a desk within the new schema.
Command to run SQL script
psql -h <INSERT HOST HERE> -d new_db -U user2 -W -f create_.sql
Contents of create_table.sql
-- drop the present desk DROP TABLE IF EXISTS new_schema.new_table; -- create a brand new desk CREATE TABLE new_schema.new_table (title VARCHAR, age INT); -- insert information into the brand new desk INSERT INTO new_schema.new_table (title, age) VALUES ('Bob', 42);
user1 (readonly) logs into the database, I’d hope they’d be capable to
SELECT from this newly created desk, nonetheless, it seems they don’t have the right entry. They’re able to see the desk, however not question it.
psql -h <INSERT HOST HERE> -d new_db -U user1 -W
new_db=> dt new_schema.* Listing of relations Schema | Identify | Sort | Proprietor ------------+-----------+-------+------- new_schema | new_table | desk | user2 (1 row) new_db=> SELECT * FROM new_schema.new_table; ERROR: permission denied for desk new_table