postgresql - Automatically allow access to tables in postgres from a user -

postgresql - Automatically allow access to tables in postgres from a user -

i have postgresql database web application. database owned particular user on system, let's foouser. owner, user has total permissions on database.

the server has user, let's webappuser, user under application server runs. instead of specifying username , password in web application's config file, want utilize "peer" authentication. have gotten authentication work properly, ran next issue.

when created webappuser role in postgresql, granted login permission grant on database foo webappuser; , within database grant on schema public webappuser;.

the issue having table permissions. unlike mysql allows access default tables if have access database (a reasonable assumption in opinion), postgresql denies access of tables though permission has been given on schema , database. in order around this, have explicitly grant permissions on new tables, views, procedures, etc. create using grant on table table_name webappuser; (and views, etc.).

it ends time run database migration, have add together permissions database new tables created. problem can't add together permission info migrations because developer machines don't have additional user. in case, looks wrong way of doing things.

how can allow access database tables additional user without needing manual intervention every time table, view, procedure, etc. created?

bonus points: there way restrict user's permission crud operations instead of total permissions , still whole thing automatically?

without experience specifics of laravel migrations: when migrations on same server there should no problem, long permissions migrated, because webappuser available cluster-wide.

when migrating different server need create user on new server , set permissions migrated objects. have 2 ways that.

the first set default privileges on tables in schema before migrate or grant select, insert, update, delete on tables in schema sch_name webappuser after migration. default privileges set with:

alter default privileges in schema sch_name grant select, insert, update, delete on tables webappuser;

both commands sql-standard compliant should have no problems across compliant architectures.

keep in mind other tables created in same schema have privileges set webappuser. setting privileges way "untrusted" user (the person using web application) not recommended in production environment because of potential privilege leaks; in development environment may acceptable.

the sec - favour - write stored procedure sets appropriate permissions. migration, run stored procedure 1 time , should up-and-running. gives more command on permission granting. procedure like:

create function grant_webapp_privileges() returns void $$ -- create webappuser, if necessary create role webappuser login; -- grant privileges on required objects grant select, insert, update, delete on table table1 webappuser; ... $$ language sql;

on master database need maintain stored procedure up-to-date when create or drop new relations. if laravel supports insertion of code blocks not in schema migrating, can create above procedure anonymous code block gets executed after migration.

(as aside, never give webappuser-like roles crud access. instead provide access through views hide of underlying info model specifics, such person having address, contact_information , other details; view serves in 1 big row. way can alter underlying relations , update view, rather having tweak web application. same principle oop , easier manage privileges.)

postgresql permissions database-migration