PostgreSQL, How to keep only one schema

permissionspostgresqlpostgresql-9.1

I don't want some users be available to create schemas, and only use the public schema.

I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 documentation. How can I do this?

Best Answer

The documentation of GRANT says the following:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an 
existing object, you must own the object and have this privilege for the containing schema.

So let's simply try this. I created a user for this purpose, and if you look at it, it has the CREATEDB attribute, just for clarifying which CREATE privilege refers to what.

CREATE USER schematest WITH LOGIN CREATEDB PASSWORD 'secure';

REVOKE CREATE ON DATABASE access_test FROM schematest;

GRANT CREATE ON SCHEMA public TO schematest;

These result in the following behaviour:

CREATE SCHEMA other_schema;

ERROR: permission denied for database access_test
SQL state: 42501

So far so good: the user cannot create a new schema. Let's see if it can create objects in the public schema:

CREATE public.simple_table(id integer);

Query returned successfully with no result in 181 ms.

Hooray!

Related Solutions

Postgresql – How to list all tables in all schemas owned by the current user in Postgresql

This will list all tables the current user has access to, not only those that are owned by the current user:

select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'

(I'm not entirely sure the not like 'pg_toast%' is actually needed though.)

I you really need the owner information, you probably need to use pg_class and related tables.

Edit: this is the query that includes the owner information:

select nsp.nspname as object_schema,
       cls.relname as object_name, 
       rol.rolname as owner, 
       case cls.relkind
         when 'r' then 'TABLE'
         when 'm' then 'MATERIALIZED_VIEW'
         when 'i' then 'INDEX'
         when 'S' then 'SEQUENCE'
         when 'v' then 'VIEW'
         when 'c' then 'TYPE'
         else cls.relkind::text
       end as object_type
from pg_class cls
  join pg_roles rol on rol.oid = cls.relowner
  join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
  and nsp.nspname not like 'pg_toast%'
  and rol.rolname = current_user  --- remove this if you want to see all objects
order by nsp.nspname, cls.relname;

PostgreSQL: How to backup only One Schema from a database and restore it on another server

You can select which schemas to dump with the -n option of pg_dump. Create a dump of schema B:

pg_dump ...other...options... -Fc -n B >dump.dmp

Restore the dump file:

pg_restore -d somedb dump.dmp

The target database does not have to have the same name as the original one.

Note that you will have problems if schema B has dependencies on schema C. Then you won't be able to restore it separately.

Best Answer

Related Solutions

Postgresql – How to list all tables in all schemas owned by the current user in Postgresql

PostgreSQL: How to backup only One Schema from a database and restore it on another server

Related Question