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 GRANT
.
I'm guessing that there's something missing between CREATE DATABASE
and CREATE SCHEMA
, something to apply the SCHEMA
to the DATABASE
?
(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?
Best Answer
You'll find everything in the manual. Links below.
Granted, the matter is not trivial and sometimes confusing. Here is a recipe for the use case:
Recipe
As superuser
postgres
:If you want a more powerful admin that can also manage databases and roles, add the role attributes
CREATEDB
andCREATEROLE
above.Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):
I am naming the schema
schma
(nothostdb
which would be confusing). Pick any name. Optionally makeschma_admin
the owner of the schema:For
and drop and alter
see notes below.Views are special. For one:
And for Updatable Views:
Triggers are special, too. You need the
TRIGGER
privilege on the table, and:But we are already over-expanding the scope of this question ...
Important Notes
Ownership
If you want to allow
schma_admin
(alone) to drop and alter tables, make the role own all objects. The documentation:Or create all objects with the role
schma_admin
to begin with, then you need not set the owner explicitly. It also simplifies default privileges, which you then only have to set for the one role:Pre-existing objects
Default privileges only apply for newly created objects and only for the particular role they are created with. You'll want to adapt permissions for existing objects, too:
The same applies if you create objects with a role that does not have
DEFAULT PRIVILEGES
set, like the superuserpostgres
. Reassign ownership toschma_admin
and set privileges manually - or setDEFAULT PRIVILEGES
forpostgres
as well (while connected to the right DB!):Default privileges
You were missing an important aspect of the
ALTER DEFAULT PRIVILEGES
command. It applies to the current role unless specified otherwise:Default privileges only apply to the current database. So you don't mess with other databases in the DB cluster. The documentation:
You may also want to set default privileges for
FUNCTIONS
andTYPES
(not justTABLES
andSEQUENCES
), but those might not be needed.Default privileges for
PUBLIC
Default privileges granted to
PUBLIC
are rudimentary and overestimated by some. The documentation:Bold emphasis mine. typically the one command above is enough to cover everything:
In particular, no default privileges are granted to
PUBLIC
for new schemas. It may be confusing that the default schema named "public" starts withALL
privileges forPUBLIC
. That's just a convenience feature to ease the start with newly created databases. It does not affect other schemas in any way. You can revoke these privileges in the template databasetemplate1
, then all newly created databases in this cluster start without them:The privilege
TEMP
Since we revoked all privileges on
hostdb
fromPUBLIC
, regular users cannot create temporary tables unless we explicitly allow it. You may or may not want to add this:search_path
Don't forget to set the
search_path
. If you only got the one database in the cluster you can just set the global default inpostgresql.conf
. Else (more likely) set it as property of the database, or just for involved roles or even the combination of both. Details:You may want to set it to
schma, public
if you use the public schema as well, or even (less likely)$user, schma, public
...An alternative would be to use the default schema "public" which should work with default settings for
search_path
unless you changed that. Remember to revoke privileges forPUBLIC
in this case.Related