PostgreSQL - Set up databases & permissions

postgresql-set_up_database-example.sql

\c postgres

\set search_path public

/**

  Create roles. This is a group to make things easier.

*/
create user researcher with nosuperuser nocreatedb nocreaterole nologin noreplication;

/**

  Create users.

*/
create user user_a with
password 'pass_a'
login in role researcher;

create user user_b with
password 'pass_b'
login in role researcher;

/**

  Create database.

*/
create database data owner postgres;

\c data

create extension postgis;

/**

  Tune permissions.

*/
-- Drop default privileges for public

revoke all privileges on database data
from public;

revoke all privileges on schema public
from public;

-- Drop default privileges for users

revoke all privileges on database data
from researcher;

revoke all privileges on schema public
from researcher;

-- Grant connect to database data to users

grant connect on database data
to researcher;

-- Permissions for public schema

grant all privileges on schema public
to researcher;

grant usage on schema public
to researcher;

grant select on all tables in schema public
to researcher;

-- Grant creation

grant create on database data to researcher;

postgresql-set_up_database_different_user_profiles.sql

/**

  Create a database with a owner with full privileges and a 
  read-only additional user.
  
  This is to be run as the "postgres" superuser.
  
  With this permissions, dbowneruser is able to create new schemas
  with full ownership and create functions and types at public.
  From this point, dbowneruser should be able to connect to the DB
  and execute any psql script defining any kind of DB object.
  
  Permissions to dbreadonlyuser, however, must be detailed object 
  by object (see last section on how to add permissions to schemas
  and all tables in them).
  
*/

-- Set the name of the database and the owner and read-only user
\set dbname thedbname
\set dbowneruser thedbowneruser
\set dbreadonlyuser thedbreadonlyuser

\set search_path public

\c postgres postgres

begin;

create user :dbowneruser with
password 'thepassword'
NOSUPERUSER LOGIN NOCREATEDB NOCREATEROLE NOREPLICATION;

create user :dbreadonlyuser with
password 'thepassword'
NOSUPERUSER LOGIN NOCREATEDB NOCREATEROLE NOREPLICATION;

commit;

-- Create database cannot be run in a transaction
create database :dbname
owner :dbowneruser;

\c :dbname

-- Create extensions here
create extension postgis;

-- Drop default privileges for public
revoke all privileges on database :dbname
from public;

revoke all privileges on schema public
from public;

-- Drop default privileges for the read-only user
revoke all privileges on database :dbname
from :dbreadonlyuser;

revoke all privileges on schema public
from :dbreadonlyuser;

grant connect on database :dbname
to :dbreadonlyuser;

-- Permissions for public schema
grant all privileges on schema public
to :dbowneruser;

grant usage on schema public
to :dbreadonlyuser;

grant select on all tables in schema public
to :dbreadonlyuser;

-- This allow the user to create schemas in the database with his/her ownership
grant create on database :dbname to :dbreadonlyuser;