I looked at postgreSQL 11 (but it should be the same for older versions ?)
Postgres manages database access permissions using roles.
A role can be a database user or a group of database users, depending on how you setup the role. To create a role:
CREATE ROLE role_name;
A user is a role with login privilege. To create a user:
CREATE USER user_name PASSWORD 'password'; -- or CREATE ROLE user_name LOGIN PASSWORD 'password'
A role has attributes that define its privileges and interact with the client authentication system:
LOGIN: allows a role to log in a database
SUPERUSER: superuser status, bypasses all permission checks, except the right to log in
CREATEDB: allows a role to create databases
CREATEROLE: allows a role to create other roles
REPLICATION LOGIN: initial streaming replication
PASSWORD: defines a password for a role
Note that role-specific defaults attached to roles without LOGIN privilege are fairly useless, since they will never be invoked.
CREATE ROLE name LOGIN; -- equivalent to CREATE USER name; CREATE ROLE name SUPERUSER; CREATE ROLE name CREATEDB; CREATE ROLE name CREATEROLE; CREATE ROLE name REPLICATION LOGIN; CREATE ROLE name PASSWORD 'string';
When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement.
An object can be assigned to a new owner with an
The initial state is that only the owner (or a superuser) can do anything with
the object. To allow other roles to use it, privileges must be granted. The
owner has some special privileges that only him and superusers have (
REVOKE, etc.), these privileges cannot be granted or revoked.
The privileges are:
SELECT: allows select
INSERT: allows insert
UPDATE: allows update
DELETE: allows delete
TRUNCATE: allows truncate
REFERENCES: allows creation of a foreign key constraint
TRIGGER: allows the creation of a trigger
CREATE: depends on the object (go check the doc)
CONNECT: allows the user to connect to the specified database
TEMPORARY: allows temporary tables to be created
EXECUTE: allows the use of the specified function or procedure, or any operators on top of the function
USAGE: depends on the object, but on schemas, allows access to objects contained in the specified schema
ALL PRIVILEGES: Grant all of the available privileges at once
The right to modify or destroy an object is always the privilege of the owner only.
GRANT PERMISION ON object TO userex of granting privileges
REVOKE ALL ON accounts FROM publicex of revoking privileges
To be able to properly set privileges you first need to know about postgreSQL “internal organization”.
PostgreSQL is made of (a) cluster(s). A cluster contains one or more databases. A database contains one or more schemas. A schema contains tables (and views, indexes, sequences, data types, functions, operators).
+----------------------+ | cluster | | +------------------+ | | | databases | | | | +--------------+ | | | | | schemas | | | | | | +----------+ | | | | | | | tables | | | | | | | +----------+ | | | | | +--------------+ | | | +------------------+ | +----------------------+
By default there is a schema named “PUBLIC” present in each database, everyone
USAGE privileges on it.
All objects created without mentioning the schema name is added to “PUBLIC” schema.
By default, users cannot access any objects in schemas they do not own. To allow
that, the owner of the schema must grant the
USAGE privilege on the schema.
To be able to access any object, a user first need to have access to its containers.
For example, if we want to create the user “Alice” and give her the right to
select, insert and update data in the table
users in schema
my_schema in the
foo. Alice needs to be able to:
- log in
- connect to the database
- access the element in the schema
- select, insert and update the table
First we (the owner) need to create a role with the
LOGIN attribute or a user:
CREATE ROLE alice LOGIN PASSWORD 'password' -- or CREATE USER alice PASSWORD 'password';
Then Alice needs to be able to connect to the database:
GRANT CONNECT ON DATABASE foo TO alice;
Then we (the owner) need to give alice the right to access the obects of the
GRANT USAGE ON SCHEMA my_schema TO alice;
Finally, we can give alice the permission to select, insert and update the table
GRANT SELECT, INSERT, UPDATE ON users TO alice;
And voilà !
You can now connect to
psql -U alice -h localhost foo
I hope it makes more sense now. Bye. Take care of your dogs.