postgreSQL Role Management
Syntax
• CREATE ROLE name [ [ WITH ] option [ ... ] ]
• CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE |
NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN |
CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL
'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [,
...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid
Examples
Create a user with a password
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-password' LOGIN;The problem with that is that queries typed into the psql console get saved in a history file .psql_history in the user's home directory and may as well be logged to the PostgreSQL database server log, thus exposing the password.
To avoid this, use the \password command to set the user password. If the user issuing the command is a superuser, the current password will not be asked. (Must be superuser to alter passwords of superusers)
CREATE ROLE niceusername with LOGIN;
\password niceusernameCreate Role and matching database
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 bloggerThis assumes that pg_hba.conf has been properly configured, which probably looks like this:
# TYPE DATABASE USER ADDRESS METHOD
host sameuser all localhost md5
local sameuser all md5Grant and Revoke Privileges.
Suppose, that we have three users :
1. The Administrator of the database > admin
2. The application with a full access for her data > read_write
3. The read only access > read_only
--ACCESS DB
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT CONNECT ON DATABASE nova TO user;With the above queries, untrusted users can no longer connect to the database.
--ACCESS SCHEMA
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO user;The next set of queries revoke all privileges from unauthenticated users and provide limited set of privileges for the read_write user.
--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin ;
--ACCESS SEQUENCES
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only; -- allows the use of CURRVAL
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of NEXTVAL and
SETVAL
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of CURRVAL and
NEXTVAL
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin;