GRANT

• PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-grant.html

Examples

• GRANT ALL PRIVILEGES ON DATABASE YOUR_DB_NAME TO YOUR_USERNAME;

SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE

GRANT SELECT ON mytable TO xxx

GRANT USAGE
GRANT USAGE ON ALL SEQUENCES IN SCHEMA your_schema TO mygrp; 

Command: GRANT "your-defined_role" TO "your_username";

Output: GRANT ROLE

SELECT * FROM your_table_name;
ERROR: permission denied for table your_table_name

• List roles: \du or SELECT * FROM pg_roles;

psql -h you_dp_ip -U postgres -p 5432  << EOF
CREATE DATABASE "yourdbname";
CREATE USER yourusername WITH PASSWORD yourpassword;
GRANT ALL PRIVILEGES ON DATABASE "yourdbname" to yourusername;
GRANT ALL PRIVILEGES ON SCHEMA public to yourusername;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO yourdbname;
EOF

Amazon Redshift

Amazon Redshift system information functions:

• HAS_TABLE_PRIVILEGE
• HAS_SCHEMA_PRIVILEGE
• HAS_ASSUMEROLE_PRIVILEGE
• HAS_DATABASE_PRIVILEGE

AWS RDS

• GRANT rds_iam TO your-user;

Activities

• View GRANTs on Redshift: (HAS_TABLE_PRIVILEGE)
• Amazon Redshift: show permissions: (has_schema_privilege)
• Amazon Redshift: grant read only privileges to user

Related terms

• ERROR: permission denied for
• Amazon Redshift: GRANT
• \dn+
• \ddp

\dt *.*

• CREATE ROLE
• Connect to a database
• Show databases
• Create database
• Delete database
• Create user
• List users
• List databases
• List tables: show tables
• GRANT
• DELETE PostgreSQL

See also

• ALTER, ALTER DATABASE, ALTER TABLE, ALTER DEFAULT PRIVILEGES, ALTER MATERIALIZED VIEW
• GRANT, \ddp, GRANT USAGE, GRANT ALL PRIVILEGES, GRANT SELECT, View GRANTs on Redshift, has_table_privilege, has_schema_privilege, HAS_DATABASE_PRIVILEGE, SCHEMA, Privileges, GRANT EXECUTE

https://stackoverflow.com/a/10353730

https://dba.stackexchange.com/a/184339