PostgreSQL structure

Table of Contents General Deal with schema access rights Practical case Segregate data in schema Example

General

List:

• a physical server (machine) can host several instances of postgreSQL server program (aka "postgreSQL server")
• a postgreSQL server listen on a single network port (eg 5432)
• a postgreSQL server expose several databases
• a database contains 1 (public) or more schema
• a schema contains objects (table, views)

Rights

• a role contains permissions to database, schema, tables

User:

• each user should be created (except when OS users are used) on the database to access it
• each user has a role by default (permission to CONNECT TO DATABASE)
• each user can be granted additional roles

Deal with schema access rights

public is the default schema. Its default privileges are lax, see SO

Your confusion may arise from the fact that the public schema has a default GRANT of all rights to the role public, which every user/group is a member of. So everyone already has usage on that schema.

You'll need further knowledge if you're about to create a schema.

List:

• get user current roles
  • \du
• grant schema creation
  • GRANT CREATE ON DATABASE test TO user1;
• grant DML (SELECT, INSERT,..) in schema (if not schema owner):
  • GRANT USAGE ON SCHEMA schema1 TO user1;
  • GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema1 TO user1;
• grant DDL (CREATE TABLE..) in schema (if not schema owner):
  • GRANT CREATE ON SCHEMA schema1 TO user1;

Check what privileges you end with

SELECT 
   table_catalog, table_schema, table_name, privilege_type, grantor, grantee
FROM   
   information_schema.table_privileges 
WHERE grantee = <USERNAME>;

Practical case

CREATE DATABASE database1;
CONNECT database1;

CREATE SCHEMA schema1;
CREATE TABLE schema1.foo (id INTEGER);
INSERT INTO schema1.foo (id) VALUES (1);

CREATE SCHEMA schema2;
CREATE TABLE schema2.bar (id INTEGER);CREATE SCHEMA
INSERT INTO schema2.bar (id) VALUES (1);

CREATE USER user1;
CREATE USER user2;
GRANT CONNECT ON DATABASE database1 TO user1;
GRANT CONNECT ON DATABASE database1 TO user2;

GRANT USAGE ON SCHEMA schema1 TO user1;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema1 TO user1;

GRANT USAGE ON SCHEMA schema2 TO user1;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema2 TO user1;
GRANT USAGE ON SCHEMA schema2 TO user2;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema2 TO user2;

CONNECT database1 user1;
SELECT * FROM schema1.foo;
SELECT * FROM schema2.bar;

CONNECT database1 user2;
SELECT * FROM schema1.foo; // relation does not exists
SELECT * FROM schema2.bar; 

CONNECT database1 user1;
DROP SCHEMA schema1 CASCADE;
SELECT * FROM schema1.foo; // relation does not exists
SELECT * FROM schema2.bar; // OK

CONNECT postgres postgres;
GRANT CREATE ON DATABASE test TO user1; // this allow schema creation within an database
CONNECT database1 user1;
CREATE DATABASE database2; // permission denied to create database
CREATE SCHEMA schema1;

Segregate data in schema

You can store data in one database but different schemas, according to:

• security, then grant appropriate right to different users
• data type, to force decoupling, keeping one user for all schemas
• data lifecycle, eg. volatile vs. permanent data
• data technical type, eg. keeping BLOB in separate schema (to deal with tablespace ?)

Example

Goal:

• one user
• volatile tables (should be removed in one-liner, whatever referencial constraint exists)
• permanent tables

This can be used for:

• volatile tables being daily restored, using pg_restore
• permanent tables being partially restored, using psql --command COPY

CREATE SCHEMA permanent_tables;

CREATE TABLE referenced (id INTEGER PRIMARY KEY);
INSERT INTO referenced(id) VALUES(1);
INSERT INTO referenced(id) VALUES(2);
ALTER TABLE referenced SET SCHEMA permanent_tables;

SELECT * FROM permanent_tables.referenced;

SHOW search_path;
SET search_path TO "$user", public, permanent_tables;
SELECT * FROM referenced;
ALTER ROLE pix_db_repl_6538 SET search_path TO "$user", public, permanent_tables;

CREATE TABLE referencing (
	id INTEGER PRIMARY KEY,
	external_id INTEGER,
	CONSTRAINT fk FOREIGN KEY(external_id)
		REFERENCES referenced(id)
);

INSERT INTO referencing (id, external_id) VALUES (1,1);
INSERT INTO referencing (id, external_id) VALUES (2,2);

CREATE SCHEMA volatile_tables;
ALTER TABLE referencing SET SCHEMA volatile_tables;
SELECT * FROM volatile_tables.referencing;

SET search_path TO "$user", public, permanent_tables, volatile_tables;
SELECT * FROM referencing;

INSERT INTO referencing (id, external_id) VALUES (3,2);

DROP SCHEMA volatile_tables CASCADE;
SHOW search_path;

SELECT * FROM referencing; // relation does not exists

CREATE SCHEMA volatile_tables;

CREATE TABLE volatile_tables.referencing (
	id INTEGER PRIMARY KEY,
	external_id INTEGER,
	CONSTRAINT fk FOREIGN KEY(external_id)
		REFERENCES referenced(id)
);

INSERT INTO referencing (id, external_id) VALUES (1,1);
INSERT INTO referencing (id, external_id) VALUES (2,2);