php - How to grant limited permissions to PostgreSQL tables? -

i considering switching mysql postgresql because of limitations of mysql's fulltext features in shared hosting environment (the minimum word length cannot changed). have no administrative experience of postgresql , struggling make sense of granting permissions users/roles.

my app has 2 users, 1 building , 1 runtime. want runtime user have these perms:

select, insert, update, delete 

i therefore log onto postgresql thus:

sudo -u postgres psql postgres 

having consulted manuals , various internet sources, created demo, intention of having limited permissions on future tables. fresh database:

create user job_frontend password 'password'; drop database if exists job_frontend; create database job_frontend encoding 'utf8'; create role job_runtime; alter default privileges in schema public     grant select, insert, update, delete on tables job_runtime; grant job_runtime job_frontend; 

my strategy, going wrong: create user, set grants on role, assign role grants user. need remove default permissions database? in mysql, users have no permissions default, , in mindset stuck.

i use pdo/php try various operations (of course modify prepare() run item want:

<?php  $dsn  = 'pgsql:dbname=job_frontend;host=localhost;user=job_frontend;password=password'; $db = new pdo($dsn);  // these should succeed $sqlread = "select * x"; $sqlwrite = "insert x (id) values (1)";  // should fail in fact succeeds $sqlcreate = "create table x (id integer)";  $stmt = $db->prepare($sqlread); if ($stmt === false) {     print_r($db->errorinfo()); }  $stmt->execute(); echo "count: " . $stmt->rowcount() . "\n";  if ($rows = $stmt->fetchall(pdo::fetch_assoc)) {     print_r($rows); } 

as per comment, create table succeeds, don't want to. missing?

i believe shared postgresql environment 8.3, later 1 may become available in future (i note see there various grant options not available until 9.x). running 9.3 locally test things, keep compatible 8.3 (until later version comes online).

anybody can in schema "public" default.

postgres=# create role tom login; -- user without rights create role postgres=# set role tom ; set postgres=> create table bubu(a int); create table 

you have remove create right public on public schema default.

postgres=# revoke create on schema public public; revoke postgres=# set role tom; set postgres=> create table foo(a int); error:  permission denied schema public line 1: create table foo(a int);                      ^