search

kartoza / osgs

A rich, integrated, and opinionated GIS Stack with a focus on configurability and ease of use, built from Open Source Components
https://kartoza.github.io/osgs/
40 stars 14 forks source link

Make functions to create database users and their permissions #127

Closed timlinux closed 2 years ago

timlinux commented 2 years ago 
-- Set up personal database for phil
CREATE DATABASE phil;
CREATE USER phil WITH PASSWORD 'fooobar';
GRANT ALL PRIVILEGES ON DATABASE "phil" to phil;
create database playground;
GRANT ALL PRIVILEGES ON DATABASE "playground" to phil;
--do this in the playground database to give all users access to all tables tha already exist
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO phil;
--these will ensure that users can access any new created tables and schemas
alter default privileges in schema public grant all on tables to phil;
-- These are permissions for the synced mergin fire hydrants database

--do this in the playground database to give all users access to the mergin project schema
GRANT USAGE ON SCHEMA mergin_project TO phil;

--do this in the playground database to give all users access to all tables tha already exist
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA mergin_project TO phil;

--these will ensure that users can access any new created tables and schemas
alter default privileges in schema mergin_project grant all on tables to phil;

Can you write this us as a workflow, explaining the steps here?

NyakudyaA commented 2 years ago 
-- Set up personal database for phil
CREATE DATABASE phil;
CREATE USER phil WITH PASSWORD 'fooobar';
GRANT ALL PRIVILEGES ON DATABASE "phil" to phil;
create database playground;
GRANT ALL PRIVILEGES ON DATABASE "playground" to phil;
--do this in the playground database to give all users access to all tables tha already exist
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO phil;
--these will ensure that users can access any new created tables and schemas
alter default privileges in schema public grant all on tables to phil;
-- These are permissions for the synced mergin fire hydrants database

--do this in the playground database to give all users access to the mergin project schema
GRANT USAGE ON SCHEMA mergin_project TO phil;

--do this in the playground database to give all users access to all tables tha already exist
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA mergin_project TO phil;

--these will ensure that users can access any new created tables and schemas
alter default privileges in schema mergin_project grant all on tables to phil;

Can you write this us as a workflow, explaining the steps here?

@timlinux You could just upgrade to PostgreSQL 14 and use the predefined roles https://www.postgresql.org/docs/14/predefined-roles.html