Difference between revisions of "GRANT"
Jump to navigation
Jump to search
↑ https://stackoverflow.com/a/10353730
↑ https://dba.stackexchange.com/a/184339
| (44 intermediate revisions by 5 users not shown) | |||
| Line 1: | Line 1: | ||
* PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-grant.html | * PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-grant.html | ||
| − | + | == 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 | + | * <code>GRANT [[pg_read_all_data]] TO xxx;</code> |
| + | * <code>GRANT pg_read_all_data, [[pg_monitor]] TO xxx;</code> | ||
| − | |||
| + | * <code>[[SELECT]], INSERT, UPDATE, [[DELETE]], TRUNCATE, REFERENCES, TRIGGER, CREATE, [[CONNECT]], TEMPORARY, EXECUTE, [[USAGE]]</code> | ||
| − | GRANT | + | * [[GRANT SELECT]]: <code>[[GRANT SELECT ON mytable TO xxx]]</code> |
| − | + | ||
| + | [[GRANT USAGE]] | ||
| + | [[GRANT USAGE ON]] [[ALL SEQUENCES IN SCHEMA]] your_schema TO mygrp; </code><ref>https://stackoverflow.com/a/10353730</ref> | ||
| + | |||
| + | Command: <code> GRANT "[[your-defined_role]]" TO "your_username";</code> | ||
| + | :Output: <code>[[GRANT ROLE]]</code> | ||
SELECT * FROM your_table_name; | SELECT * FROM your_table_name; | ||
[[ERROR: permission denied for table]] your_table_name | [[ERROR: permission denied for table]] your_table_name | ||
| − | [[GRANT | + | * List roles: <code>[[\du]]</code> or <code>[[SELECT * FROM pg_roles;]]</code> |
| + | |||
| + | [[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 | + | == [[Amazon Redshift]] == |
| + | [[Amazon Redshift]] system information functions: | ||
* <code>[[HAS_TABLE_PRIVILEGE]]</code> | * <code>[[HAS_TABLE_PRIVILEGE]]</code> | ||
* <code>[[HAS_SCHEMA_PRIVILEGE]]</code> | * <code>[[HAS_SCHEMA_PRIVILEGE]]</code> | ||
| Line 25: | Line 41: | ||
* <code>[[HAS_DATABASE_PRIVILEGE]]</code> | * <code>[[HAS_DATABASE_PRIVILEGE]]</code> | ||
| + | == AWS RDS == | ||
| + | * <code>[[GRANT rds_iam]] TO your-user;</code> | ||
| + | * <code>GRANT [[rds_superuser]] TO "your username";</code> | ||
| + | == Activities == | ||
* [[View GRANTs on Redshift]]: (<code>[[HAS_TABLE_PRIVILEGE]]</code>) | * [[View GRANTs on Redshift]]: (<code>[[HAS_TABLE_PRIVILEGE]]</code>) | ||
* [[Amazon Redshift: show permissions]]: (<code>[[has_schema_privilege]]</code>) | * [[Amazon Redshift: show permissions]]: (<code>[[has_schema_privilege]]</code>) | ||
| + | * [[Amazon Redshift: grant read only privileges to user]] | ||
| + | |||
| + | == Errors == | ||
| + | * <code>[[Must be owner of table]]</code> | ||
== Related terms == | == Related terms == | ||
| + | * [[ERROR: permission denied for]] | ||
* [[Amazon Redshift: GRANT]] | * [[Amazon Redshift: GRANT]] | ||
| + | * <code>[[\dp]]</code> [[list tables]] and privileges | ||
| + | * <code>[[\dn+]]</code> | ||
* <code>[[\ddp]]</code> <ref>https://dba.stackexchange.com/a/184339</ref> | * <code>[[\ddp]]</code> <ref>https://dba.stackexchange.com/a/184339</ref> | ||
| + | [[\dt *.*]] | ||
* <code>[[CREATE ROLE]]</code> | * <code>[[CREATE ROLE]]</code> | ||
| − | * | + | * [[DELETE PostgreSQL]] |
| + | * [[PostgreSQL Privileges]] | ||
== See also == | == See also == | ||
| + | * {{ALTER}} | ||
* {{GRANT}} | * {{GRANT}} | ||
| − | |||
| − | |||
[[Category:PostgreSQL]] | [[Category:PostgreSQL]] | ||
Latest revision as of 10:03, 4 November 2024
- 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;GRANT pg_read_all_data, pg_monitor 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
Errors[edit]
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 TABLE, ALTER DEFAULT PRIVILEGES, ALTER MATERIALIZED VIEWGRANT,\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:
