How To Give Permission To a User On PostgreSQL

In this article we will examine how to give permission to a user in PostgreSQL.

You should click on the Postgres menu item for other PostgreSQL articles.

You can give permission to a user on an object with the command below.

GRANT permissiontype ON objectname TO username;

1	GRANT permissiontype ON objectname TO username;

Give permission on database:

GRANT SELECT ON DATABASE databasename  TO username;

1	GRANT SELECT ON DATABASE databasename TO username;

Below you will find the kinds of permissions you can give on the database.

SELECT,
INSERT,
UPDATE,
DELETE,
TRUNCATE,
REFERENCES,
TRIGGER,
CREATE,
TEMPORARY or TEMP,
EXECUTE,
USAGE,
ALL PRIVILEGES

SELECT,

INSERT,

UPDATE,

DELETE,

TRUNCATE,

REFERENCES,

TRIGGER,

CREATE,

TEMPORARY or TEMP,

EXECUTE,

USAGE,

ALL PRIVILEGES

Give permission on the object:

GRANT SELECT ON tablename TO username;

1	GRANT SELECT ON tablename TO username;

Give all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA schemaname TO username;

1	GRANT SELECT ON ALL TABLES IN SCHEMA schemaname TO username;

Give all permissions on database:

GRANT ALL PRIVILEGES ON DATABASE "databasename"  TO username;

1	GRANT ALL PRIVILEGES ON DATABASE "databasename" TO username;

If you put the following command at the end of the above scripts, the relevant user can also give permissions to others.

GRANT SELECT ON tablename TO username WITH GRANT OPTION;

1	GRANT SELECT ON tablename TO username WITH GRANT OPTION;

You may want to look at the link below for more detailed authorization types.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux