Tutorial by Examples

Generally you should avoid using the default database role (often postgres) in your application. You should instead create a user with lower levels of privileges. Here we make one called niceusername and give it a password very-strong-password CREATE ROLE niceusername with PASSWORD 'very-strong-pas...

To support a given application, you often create a new role and database to match. The shell commands to run would be these: $ createuser -P blogger Enter password for the new role: ******** Enter it again: ******** $ createdb -O blogger blogger This assumes that pg_hba.conf has been prope...

Suppose, that we have three users : The Administrator of the database > admin The application with a full access for her data > read_write The read only access > read_only --ACCESS DB REVOKE CONNECT ON DATABASE nova FROM PUBLIC; GRANT CONNECT ON DATABASE nova TO user; With th...

With the below commands, user's default search_path can be set. Check search path before set default schema. postgres=# \c postgres user1 You are now connected to database "postgres" as user "user1". postgres=> show search_path; search_path ---------------- &q...

Suppose, that we have three users : The Administrator of the database > admin The application with a full access for her data > read_write The read only access > read_only With below queries, you can set access privileges on objects created in the future in specified schema. ALTER ...

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword'; GRANT CONNECT ON DATABASE <database_name> to readonly; GRANT USAGE ON SCHEMA public to readonly; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;