postgresql 赋权语句 grant的正确使用说明

grant select on all tables in schema public to username;

t1=# \c

You are now connected to database [t1] as user [postgres]

t1=# \dns

List of schemas

Name | Owner

--------±---------

public | postgres

s1 | postgres

s2 | postgres

(3 rows )

t1=# create user u1 password ‘123456 ';

CREATE ROLE

t1=# create user u2 password ‘123456' ;

CREATE ROLE

t1=# grant all privileges on schema s1 to u1;

GRANT

t1=# grant all privileges on schema s2 to u1;

GRANT

t1=# \c - u1

You are now connected to database [t1] as user [u1].

t1=> create table s1.table1(hid int );

CREATE TABLE

t1=> create view s2.view1 as select * from s1.table1;

CREATE VIEW

t1=> \c - u2

You are now connected to database [t1] as user [u2].

t1=> select * from s2.view1;

ERROR: permission denied for schema s2

LINE 1: select * from s2.view1;

t1=> \c - postgres

You are now connected to database [t1] as user [postgres].

t1=# grant usage on schema s2 to u2;

GRANT

t1=# \c - u2

You are now connected to database [t1] as user [u2].

t1=> select * from s2.view1;

ERROR: permission denied for view view1

t1=> \c - postgres

You are now connected to database [t1] as user [postgres].

t1=# grant select on s2.view1 to u2;

t1=# \c - u2

You are now connected to database [t1] as user [u2].

t1=> select * from s2.view1;

hid

(0 rows )

create user lukes password ‘X123_x123'

grant usage on schema cl_crm to lukes

select * from cl_crm.v_account limit 1

Amazon Invalid operation: permission denied for schema raw_wam;

1 statement failed.

grant usage on schema raw_wam to lukes

grant usage on schema raw_c3 to lukes

Amazon Invalid operation: permission denied for relation v_account;

grant select on table cl_crm.v_account to lukes

CREATE or replace view cl_crm.v_account as with userids as (

select distinct su.ibsu_userid from raw_wam.ibdwsurvey su

left join raw_c3.ibdwsurveyinfo si …)