I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go.
The application currently consists of a command to update the data; and one to query it. Naturally, I'll also need to maintain the database in other ways (create new tables, views, triggers, etc).
While this application will be the only one hosted on the server at first, I'd prefer to bake in the assumption that it might be hosted on a server with other databases in the future, rather than having to scramble later if that becomes necessary in the future.
I would think that these would be a fairly common set of requirements, but I'm having trouble finding a simple tutorial explaining how to set up a new database in PostgreSQL, with this sort of user/privilege separation. The references go on at length about groups, users, roles, databases, schemas and domain; but I find them confusing.
Here's what I've tried so far (from within
psql as 'postgres'):
CREATE DATABASE hostdb; REVOKE ALL ON DATABASE hostdb FROM public; \connect hostdb CREATE SCHEMA hostdb; CREATE USER hostdb_admin WITH PASSWORD 'youwish'; CREATE USER hostdb_mgr WITH PASSWORD 'youwish2'; CREATE USER hostdb_usr WITH PASSWORD 'youwish3'; GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin; GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr; ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr; ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;
But I'm not getting the intended semantics. I want to have it configured so only the
hostdb_admin can create (and drop and alter) tables; the
hostdb_mgr can read, insert, update and delete on all tables by default; and the
hostdb_usr can only read all tables (and views).
When I tried this I found that I was able to create tables in
hostdb as any of these users; but, for each user, I could only read or modify tables created by that user - unless I use an explicit
I'm guessing that there's something missing between
CREATE DATABASE and
CREATE SCHEMA, something to apply the
SCHEMA to the
(As things get more advanced I'll also have questions to apply similar restrictions on
TRIGGERS, stored procedures,
VIEWS and perhaps other objects).
Where can I find a decent guide, tutorial or video series on this?