Limit postgres User To A Schema

I am getting ready to launch a web site for public consumption. As a result, I want to restrict the database user my application will be using. Up to this point, I was testing with an admin user.

The goal is to restrict access to just one schema and a few objects.

postgres=# CREATE ROLE someuser WITH LOGIN PASSWORD 'password goes here';
CREATE USER
postgres=# GRANT CONNECT ON DATABASE somedatabase TO someuser;
GRANT CONNECT
postgres=# GRANT USAGE ON someschema TO someuser;
GRANT USAGE
postgres=# ALTER ROLE someuser SET search_path TO someschema;
ALTER ROLE
postgres=# GRANT SELECT ON sometable TO someuser;
GRANT SELECT

First, the role (user) is created with the ability to login. Next, the user is given permission to connect to a database and then use a specific schema. The user's search_path is then restricted to just the desired schema.

Finally, access to objects are granted. Remember to include sequences!

New PostgreSQL Databases And The Users That Own Them

A quick post on how to create new databases with owners other than 'postgres'.

$ psql postgres
postgres=# CREATE USER someuser WITH PASSWORD 'password goes here';
CREATE USER
postgres=# CREATE DATABASE newDatabase WITH OWNER = someuser;
CREATE DATABASE

A quick, random password can be created using /dev/random:

$ head -c 10 /dev/random | uuencode -m -