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
Alter default search_path of user
With the below commands, user's default search_path can be set.
- Check search path before set default schema.
alter usercommand to append a new schema
- Check result after execution.
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
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 Read Only User
Create 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:
This assumes that
pg_hba.conf has been properly configured, which probably looks like this:
Grant access privileges on objects created in the future.
Suppose, that we have
three users :
- The Administrator of the database >
- The application with a full access for her data >
- The read only access >
With below queries, you can set access privileges on objects created in the future in specified schema.
Or, you can set access privileges on objects created in the future by specified user.
Grant and Revoke Privileges.
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 the above queries, untrusted users can no longer connect to the database.
The next set of queries revoke all privileges from unauthenticated users and provide limited set of privileges for the