Difference between revisions of "GRANT"
Jump to navigation
Jump to search
↑ https://stackoverflow.com/a/10353730
↑ https://dba.stackexchange.com/a/184339
| (3 intermediate revisions by 2 users not shown) | |||
| Line 3: | Line 3: | ||
== Examples == | == Examples == | ||
* <code>[[GRANT ALL PRIVILEGES]] ON DATABASE YOUR_DB_NAME TO YOUR_USERNAME;</code> | * <code>[[GRANT ALL PRIVILEGES]] ON DATABASE YOUR_DB_NAME TO YOUR_USERNAME;</code> | ||
| + | |||
| + | * <code>GRANT [[pg_read_all_data]] TO xxx;</code> | ||
| + | |||
* <code>[[SELECT]], INSERT, UPDATE, [[DELETE]], TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, [[USAGE]]</code> | * <code>[[SELECT]], INSERT, UPDATE, [[DELETE]], TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, [[USAGE]]</code> | ||
| Line 21: | Line 24: | ||
[[psql -h]] you_dp_ip [[-U postgres]] [[-p 5432]] << EOF | [[psql -h]] you_dp_ip [[-U postgres]] [[-p 5432]] << EOF | ||
[[CREATE DATABASE]] "yourdbname"; | [[CREATE DATABASE]] "yourdbname"; | ||
| − | [[CREATE USER]] yourusername WITH PASSWORD yourpassword; | + | [[CREATE USER]] "yourusername" WITH PASSWORD yourpassword; |
| − | [[GRANT ALL PRIVILEGES ON DATABASE]] "yourdbname" to yourusername; | + | [[GRANT ALL PRIVILEGES ON DATABASE]] "yourdbname" to "yourusername"; |
| − | [[GRANT ALL PRIVILEGES ON SCHEMA]] public to yourusername; | + | [[GRANT ALL PRIVILEGES ON SCHEMA]] public to "yourusername"; |
| − | [[GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA]] [[public]] TO yourdbname; | + | [[GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA]] [[public]] TO "yourdbname"; |
EOF | EOF | ||
| Line 55: | Line 58: | ||
* <code>[[CREATE ROLE]]</code> | * <code>[[CREATE ROLE]]</code> | ||
* [[DELETE PostgreSQL]] | * [[DELETE PostgreSQL]] | ||
| − | * [[Privileges]] | + | * [[PostgreSQL Privileges]] |
== See also == | == See also == | ||
Latest revision as of 15:39, 5 July 2023
- PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-grant.html
Examples[edit]
GRANT ALL PRIVILEGES ON DATABASE YOUR_DB_NAME TO YOUR_USERNAME;
GRANT pg_read_all_data TO xxx;
SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE
GRANT USAGE GRANT USAGE ON ALL SEQUENCES IN SCHEMA your_schema TO mygrp; [1]
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:
\duorSELECT * 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
GRANT CONNECT
Amazon Redshift[edit]
Amazon Redshift system information functions:
AWS RDS[edit]
GRANT rds_iam TO your-user;GRANT rds_superuser TO "your username";
Activities[edit]
- View GRANTs on Redshift: (
HAS_TABLE_PRIVILEGE) - Amazon Redshift: show permissions: (
has_schema_privilege) - Amazon Redshift: grant read only privileges to user
Related terms[edit]
- ERROR: permission denied for
- Amazon Redshift: GRANT
\dplist tables and privileges\dn+\ddp[2]
\dt *.*
See also[edit]
ALTER, ALTER DATABASE, ALTER DEFAULT PRIVILEGESGRANT,\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
Advertising:
