1、使用postgres用户登录pgsql
sudo -u postgres psql postgres -p 5432
2、添加名为myuser的用户,并设置密码
create user myuser with password '123456';
3、给myuser用户,创建数据库叫work_base
create database work_base owner myuser;
4、授予myuser当前work_base的全部权限
4.1、 先退出postgre数据库
exit
4.2、登录work_base数据库
psql -U postgres -d work_base -p 5432
4.3、将work_base的所有权限赋予myuser
grant all privileges on database work_base to myuser;
grant usage on schema public to myuser;
grant all privileges on all tables in schema public to myuser;
grant all privileges on all sequences in schema public to myuser;
grant select,insert,update,delete on all tables in schema public to myuser;
5、创建用户 myuser2
create user myuser2 with password '123456';
mydb=> \c mydb myuser2
您现在已经连接到数据库 "mydb",用户 "myuser2".
mydb=> insert into mydbtable (name,year) values ('xiaoming3',33);
错误: 对表 mydbtable 权限不够
Postgresql 创建数据库、表、插入表数据等sql
postgres=# create database mydb;
CREATE DATABASE
postgres=# alter database mydb owner myuser;
ALTER DATABASE
postgres=# create table mydbtable(name varchar(80),year int);
CREATE TABLE
mydb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+-----------+--------+----------
public | mydbtable | 数据表 | postgres
(1 行记录)
postgres=# insert into mydbtable (name,year) values ('xiaoming',23);
INSERT 0 1
postgres=# table mydbtable;
name | year
----------+------
xiaoming | 23
(1 row)
postgres=# select * from mydbtable;
name | year
----------+------
xiaoming | 23
(1 row)
postgres=# select name from mydbtable;
name
----------
xiaoming
(1 row)
postgres=# insert into mydbtable (name,year) values ('xiaohong',23);
INSERT 0 1
postgres=# select name from mydbtable;
name
----------
xiaoming
xiaohong
(2 rows)
postgres=# update mydbtable set name = 'xiaohei' where name='xiaohong';
UPDATE 1
postgres=# select name from mydbtable;
name
----------
xiaoming
xiaohei
(2 rows)
postgres=#
postgres=# delete from mydbtable where name='xiaohei';
DELETE 1
postgres=# select name from mydbtable;
name
----------
xiaoming
(1 row)
postgres=# delete from mydbtable;
DELETE 1
postgres=# select * from mydbtable;
name | year
------+------
(0 rows)
postgres=#
postgres=# create database testdb;
CREATE DATABASE
postgres=# alter database testdb;
ALTER DATABASE
常见运维sql
#获取连接的状态信息sql
SELECT
pg_stat_activity.pid,
pg_stat_activity.usename,
pg_stat_activity.query,
pg_stat_activity.state
FROM
pg_stat_activity
WHERE
pg_stat_activity.state <> 'idle'
AND pg_stat_activity.query <> '<IDLE>'
ORDER BY
pg_stat_activity.query_start;