CODEWITHSUNDEEP
postgresqldockerdocker-composeflyway
Dattebayo
Dattebayo

Reputation: 13

How to enable multiple user access to docker postgres container?

I am new to docker and flyway. I am having three tables i.e products, inventory_history and purchase_history for my db prod_mgmt and their schema is defined in a sql file. I am using flyway to create these tables when docker container for postgres spins up.

Now in the docker compose file I have a user called 'postgres' and a password for the db. There is service section where flyway is defined in compose file, which connects to the db using the above username and password and calls migrate.

In the postgres section of the compose file I have the image_name, container_name and environment: - POSTGRES_HOST=postgres - POSTGRES_PORT=**** - POSTGRES_DB=prod_mgmt - POSTGRES_USER=postgres - POSTGRES_PASSWORD

My question is I want to have 3 users i.e product_users with access to only product table,inventory_planners access to product and inventory_history table, sales_person access to purchase_history and product. These users will have only insert,update and select access only.

How should I grant permissions to them in postgres? do I need to also add them as POSTGRES_USER in postgres section of the docker compose file? I believe user 'postgres' will act as a superuser which will have permission to all three tables (create,insert,select,update and drop)

What I am trying to add in sql file:

create user product_users;
GRANT SELECT,INSERT,UPDATE on product,inventory_planners to product_users;

Not sure how to add multiple users in the docker compose file.

Upvotes: 1

Views: 2025

Answers (1)

ahmed_131313
ahmed_131313

Reputation: 146

I think you can create the users and grant them permissions after the PostgreSQL container starts using Flyway migrations, this way you will give the users the correct permissions. The sql commands required will be something like:

CREATE USER product_users;
GRANT SELECT, INSERT, UPDATE ON products TO product_users;

CREATE USER inventory_planners;
GRANT SELECT, INSERT, UPDATE ON products, inventory_history TO inventory_planners;

CREATE USER sales_person;
GRANT SELECT, INSERT, UPDATE ON products, purchase_history TO sales_person;

I hope this answers you question, let me know if I missed something.

Upvotes: 1

Related Questions